withRecursiveExpression not appearing on GET method

[chammeow]

Please help. I'm doing an eloquent selection for the list of categories with its Hierarchical Data and need to get the path names of categories to subcategories (eg. Electronics > Mobile Phones > Android Phones) but these path names not appearing on my GET method. Here is my code.

CONTROLLER

$query = "SELECT name as path
                        FROM merchant_branch_categories
                        WHERE parent_id IS NULL
                    UNION ALL
                    SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
                    FROM category_path AS cp JOIN merchant_branch_categories AS c
                    ON cp.id = c.parent_id";

            $merchantBranchCategoryListQuery = Merchant_branch_category::when(
                $request->name,
                function ($query) use ($request) {
                    $query->where('name', 'LIKE', '%' . $request->name . '%');
            })
            ->when($request->merchant_branch_id,
                function ($query) use ($request) {
                    $query->where('merchant_branch_id', intVal($request->merchant_branch_id));
            })
            ->when($request->parent_id,
                function ($query) use ($request) {
                    $query->where('parent_id', intVal($request->parent_id));
            })
            ->when($request->description,
                function ($query) use ($request) {
                    $query->where('description', 'LIKE', '%' . $request->description . '%');
            })
            ->when($request->tags,
                function ($query) use ($request) {
                    $query->where('tags', 'LIKE', '%' . $request->tags . '%');
            })
            ->when($request->is_active,
                function ($query) use ($request) {
                    $query->where('is_active', $request->is_active);
            })
            ->withRecursiveExpression('category_path', $query, ['id', 'title', 'path']);

            if ($request->per_page && $request->per_page != 'all') {
                $merchantBranchCategoryList = $merchantBranchCategoryListQuery->with('categories')->paginate($request->per_page);
                $merchantBranchCategoryList->appends($request->all());
            } else {
                $merchantBranchCategoryList = $merchantBranchCategoryListQuery->with('categories')->get();
            }

            return $this->sendResponse($merchantBranchCategoryList, 'Merchant Branch Category List Retrieved.');

I've tried to check the SQL string for $merchantBranchCategoryList (eg. $merchantBranchCategoryListQuery->toSql()) but the ACTUAL return is

with recursive category_path (id, title, path) as (SELECT name as path FROM merchant_branch_categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name) FROM category_path AS cp JOIN merchant_branch_categories AS c ON cp.id = c.parent_id) select * from merchant_branch_categories

I must be expecting

with recursive category_path (id, title, path) as (SELECT name as path FROM merchant_branch_categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name) FROM category_path AS cp JOIN merchant_branch_categories AS c ON cp.id = c.parent_id) select * from category_path

PLEASE HELP ME TO UNDERSTAND.

[staudenmeir]

The query doesn't select from the expression by default. Add this to your method:

$merchantBranchCategoryListQuery->from('category_path');

[chammeow]

The query doesn't select from the expression by default. Add this to your method:

$merchantBranchCategoryListQuery->from('category_path');

Thank you very much. I changed my approach using the adjacent list which is more convenient. I used the custom path added to my Model and add the tree() and toTree() function.

THANK YOU VERY MUCH.