Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

calin.ionut's avatar

advanced join based on column values

I have these tables:

users 
locations
structures
structure_values

The locations table

id, name

the structures table:

id, name

and the structure_values table

id, structure_id, parent, entity, entity_id

I need to create custom hierarchical structure that can contain user or location.

Structure example:

id | structure_id | entity | entity_id | parent
1 | 1 | location | 1 | null
2 | 1 | user | 1 | 1
3 | 1 | user | 2 | 2
4 | 1 | location | 2 | 2
5 | 1 | user | 3 | 4

In the model for the structure_values table I created the 2 relations:

public function directChildren() {
	return $this->hasMany(StructureValue::class, 'parent');
}

public function children() {
	return $this->directChildren()->with('children');
}

The root element (with parent null) return the correct structure

	$root->children->toArray();

The problem is I want to get the info for the user or location....

I have updated the directChildren relation with join....

public function directChildren() {
	return $this->hasMany(StructureValue::class, 'parent')
		->join('users', function($join) {
			$join->on('users.id', '=', 'structure_values.entity_id')
				->where('structure_values.entity', 'user');
		})
      ->join('locations', function($join) {
			$join->on('locations.id', '=', 'structure_values.entity_id')
				->where('structure_values.entity', 'location');
		});
}

the result is an empty array.

How can I join data to get the info for those 2 entities (user, location) ???

0 likes
3 replies
aosdev's avatar

You don't need reinvent the wheel, just use polymorphic relations.

Please or to participate in this conversation.