Sorting a HasManyOfDescendants relationship based on the CTE

[jordyvanderhaegen]

Hi,

Thank you for this awesome package!

I have a question about best practices for sorting a hasManyOfDescendantsAndSelf relationship based on the CTE.
I've come up with a solution, but I feel like there's probably a better way to solve this.

I have a simple project with a Product and Category model.
On the Category model I have defined a recursive products relationship:

public function recursiveProducts(): HasManyOfDescendants
{
    return $this->hasManyOfDescendantsAndSelf(Product::class, 'category_code', 'code');
}

Out of the box, this produces the following query:

with recursive `laravel_cte` as ((select *, 0 as `depth` from `categories` where `code` = 'MY_CATEGORY' and `code` is not null) union all (select `categories`.*, `depth` + 1 as `depth` from `categories` inner join `laravel_cte` on `laravel_cte`.`code` = `categories`.`parent_code`)) select * from `products` where `products`.`category_code` in (select `code` from `laravel_cte` order by `laravel_cte`.`sort` asc)

Using this relationship, I want to order the products depending on both the depth and sort from the underlying categories.
To do so, I have extended the HasManyOfDescendants relationship and overwritten the getInitialQuery and getRecursiveQuery methods:

class HasManyOfDescendants extends BaseHasManyOfDescendants
{
    protected function getInitialQuery(ExpressionGrammar $grammar, callable $constraint, $alias, $selectPath)
    {
        $query = parent::getInitialQuery(...func_get_args());
        $query->selectRaw("cast(CONCAT(0, categories.sort) as char(65535)) as `sort_path`");

        return $query;
    }

    protected function getRecursiveQuery(ExpressionGrammar $grammar, $selectPath)
    {
        $query = parent::getRecursiveQuery(...func_get_args());
        $query->selectRaw("concat(`sort_path`, `depth` + 1, categories.sort) as `sort_path`");

        return $query;
    }
}

This results in the following query:

with recursive `laravel_cte` as ((select *, 0 as `depth`, cast(CONCAT(0, categories.sort) as char(65535)) as `sort_path` from `categories` where `code` = 'MY_CATEGORY' and `code` is not null) union all (select `categories`.*, `depth` + 1 as `depth`, concat(`sort_path`, `depth` + 1, categories.sort) as `sort_path` from `categories` inner join `laravel_cte` on `laravel_cte`.`code` = `categories`.`parent_code`)) select * from `products` where `products`.`category_code` in (select `code` from `laravel_cte` order by `laravel_cte`.`sort` asc)

This is great, but does not sort the products yet. I'm able to do so after joining the CTE on the products:

$products = $category
    ->recursiveProducts()
    ->select('products.*')
    ->join('laravel_cte', 'laravel_cte.code', 'products.category_code')
    ->orderBy('laravel_cte.sort_path')
    ->get()

with recursive `laravel_cte` as ((select *, 0 as `depth`, cast(CONCAT(0, categories.sort) as char(65535)) as `sort_path` from `categories` where `code` = 'MY_CATEGORY' and `code` is not null) union all (select `categories`.*, `depth` + 1 as `depth`, concat(`sort_path`, `depth` + 1, categories.sort) as `sort_path` from `categories` inner join `laravel_cte` on `laravel_cte`.`code` = `categories`.`parent_code`)) select `products`.* from `products` inner join `laravel_cte` on `laravel_cte`.`code` = `products`.`category_code` where `products`.`category_code` in (select `code` from `laravel_cte` order by `laravel_cte`.`sort` asc) order by `laravel_cte`.`sort_path`

This works great, but feels a bit hacky to me. Especially the part where I had to join the CTE table, which seems to be an internal name implemented by the package that might change in the future.
I have considered the following option, but without success:

• Using the getCustomPaths method
  Does not apply to relationship queries and does currently now allow to specify different statements for the initial and recursive query.

Any advice on how this might be achieved in a better way?

[staudenmeir]

Hi @jordyvanderhaegen,
There's a newer and better way to define relationships like recursiveProducts() that I would recommend for your case:
https://github.com/staudenmeir/laravel-adjacency-list#concatenation

use Staudenmeir\EloquentHasManyDeep\HasRelationships;
use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

class Category extends Model
{
    use HasRelationships; // Requires staudenmeir/eloquent-has-many-deep
    use HasRecursiveRelationships;

    public function recursiveProducts()
    {
        return $this->hasManyDeepFromRelations($this->descendantsAndSelf(), (new static)->products());
    }

    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

You get the same result, but the query is a bit different. The key difference is that it already includes the join with laravel_cte that you had to add manually:

with recursive `laravel_cte` as ((select *, 0 as `depth`, cast(`id` as char(65535)) as `path`
                                  from `categories`
                                  where `categories`.`id` = ?)
                                 union all
                                 (select `categories`.*, `depth` + 1 as `depth`, concat(`path`, ?, `categories`.`id`)
                                  from `categories`
                                           inner join `laravel_cte` on `laravel_cte`.`id` = `categories`.`parent_id`))
select `products`.*, `laravel_cte`.`path` as `laravel_through_key`
from `products`
         inner join `laravel_cte` on `laravel_cte`.`id` = `products`.`category_id`

The query also includes custom paths so it's too bad that they don't work for sort_path.

You can use withRecursiveQueryConstraint() instead of overwriting getRecursiveQuery():
https://github.com/staudenmeir/laravel-adjacency-list#recursive-query-constraints

There is no equivalent withInitialQueryConstraint() yet, but it's on my TODO list.

[jordyvanderhaegen]

That is much cleaner!
Opted to go for overwriting getInitialQuery and getRecursiveQuery methods on the Category model for now.
Feels a bit cleaner than to wrap everything in the static withRecursiveQueryConstraint callback.

Thank you for the quick response :-)