Define a Laravel relationship not using equal but like operator

43 Views Asked by At

I have a domaines table representing nodes of a tree structure of technical domains. Each node has a parent_id pointing to the id of his parent. Root node has parent_id = null. To avoid the use of recursive process that can slow reading process, I added a path property that represents the concatenation of all the parent ids used to reach the node. For a node (id:123), child of another node (id:45), who is also child of a node (a root node this time) (id:6), our node 123 will have his property path set on '6/45/123'.

I already declare the first relation between parent and direct children

    /**
     * Get direct children 
     * 
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function children()
    {
        return $this->hasMany(Domaine::class, 'parent_id') );
    }

How can i define a allChildren relationship for my model Domaine using not the parent_id but the path and the like operator, to achieve the same behavior as if I declared a "classic" relationship. The resulting select of all the child of the root node 6 should be like :

SELECT ALL_CHILDREN.*
FROM domaines AS PARENT
INNER JOIN domaines AS ALL_CHILDREN ON CHILDREN.path LIKE CONCAT(PARENT.path, '/%')
WHERE PARENT.ID = 6

The hasMany relationship declaration do not have a configuration for the operator. In addition i need to use CONCAT() function and not directly look like PARENT.path due to the fact that an similar path but not a child could be included.

I don't know how to proceed using clean solution

0

There are 0 best solutions below