Order by relation found in field

Hello community. I have issue with query ordering. At first sight it is very simple, but I have no idea how can I solve it.

I have eloquent query like this:

Product::query()
	->published()
	->whereHas('translations', function($query) {
		$query
			->whereIn('key', ['short_name', 'short_description', 'category_description'])
			->where('content', 'like', '%some_search_request%')
	})
	->whereHas('page_info', function($query) {
		$query
			->where('title', 'like', '%some_search_request%')
			->orWhere('h1', 'like', '%some_search_request%')
			->orWhere('description', 'like', '%some_search_request%')
	})
    ->paginate(20);

This query is used for search and live search on website. That is why we need to show most relevant items firstly.

Task, we need to order in such way:

Firstly show items if we found match in relation 'translation' and keys 'short_name' or 'short_description'
Secondarily if we have match in relation 'translation' and key 'category_description'
Thirdly if we have match in relation 'page_info' in any column.

Does anyone have any idea?

Snapey

2 years ago

Level 122

Use a join

jordan-dev

2 years ago

Level 1

@Snapey Can you provide some example please. If I can just use join in this case I did not write this question here... Please provide same example for my case and what kind of order should I use.

tangtang

2 years ago

Best Answer

Level 6

@jordan-dev let's do it with a little db raw (only if you want it) 🤣🤣

try this

Product::query()
    ->published()
    ->select(['products.*', DB::raw('
        CASE
            WHEN translations.key IN ("short_name", "short_description") AND translations.content LIKE "%some_search_request%" THEN 1
            WHEN translations.key = "category_description" AND translations.content LIKE "%some_search_request%" THEN 2
            WHEN page_info.title LIKE "%some_search_request%" OR page_info.h1 LIKE "%some_search_request%" OR page_info.description LIKE "%some_search_request%" THEN 3
            ELSE 4
        END AS priority
    ')])
    ->join('translations', 'products.id', '=', 'translations.product_id')
    ->leftJoin('page_info', 'products.id', '=', 'page_info.product_id')
    ->where(function($query) {
        $query->whereIn('translations.key', ['short_name', 'short_description', 'category_description'])
              ->where('translations.content', 'like', '%some_search_request%')
              ->orWhere('page_info.title', 'like', '%some_search_request%')
              ->orWhere('page_info.h1', 'like', '%some_search_request%')
              ->orWhere('page_info.description', 'like', '%some_search_request%');
    })
    ->orderBy('priority')
    ->paginate(20);

jordan-dev

2 years ago

Level 1

@tangtang Thank you very much, it almost done... I do some changes to code according my db structure.

Product::query()            
            ->join('translations', function($join) {
                /**
                 * @var $join JoinClause
                 */
                $join->on('products.id', '=', 'translations.translatable_id')
                    ->where('translations.translatable_type', Product::class)
                    ->where('translations.locale', app()->getLocale())
                    ->where(function($query) {
                        $query
                            ->whereIn('translations.key', ['short_name', 'short_description'])
                            ->orWhere('translations.key', 'like', 'add_cat_desc_%');
                    });
            })
            ->leftJoin('page_infos', function($join) {
                /**
                 * @var $join JoinClause
                 */
                $join->on('products.id', '=', 'page_infos.pageable_id')
                    ->where('page_infos.pageable_type', Product::class)
                    ->where('page_infos.locale', app()->getLocale());
            })
            ->leftJoin('tags', function($join) {
                /**
                 * @var $join JoinClause
                 */
                $join
                    ->on('products.id', '=', 'tags.owner_id')
                    ->where('tags.owner_type', Product::class);
            })
            ->where(function($query) {
                $query
                    ->where('translations.content', 'ilike', $this->wrap_s)
                    ->orWhere('page_infos.h1_title', 'ilike', $this->wrap_s)
                    ->orWhere('page_infos.title', 'ilike', $this->wrap_s)
                    ->orWhere('page_infos.desc', 'ilike', $this->wrap_s)
                    ->orWhere('page_infos.keywords', 'ilike', $this->wrap_s)
                    ->orWhere('tags.content', 'ilike', $this->wrap_s);
            })
            ->orderByRaw(
                \DB::raw('
                    CASE
                        WHEN translations.key IN ("short_name", "short_description") AND translations.content ILIKE "' . $this->wrap_s . '" THEN 1
                        WHEN translations.key ILIKE "add_cat_desc_%" AND translations.content ILIKE "' . $this->wrap_s . '" THEN 2
                        ELSE 3
                    END DESC
                    ')
            )
            ->select(['products.*'])
            ->groupBy(['products.id'])
//            ->toSql($limit);
            ->paginate($limit);

Query works, without orderByRaw. All joins and search functions works properly. But when I add this

->orderByRaw(
                \DB::raw('
                    CASE
                        WHEN translations.key IN ("short_name", "short_description") AND translations.content ILIKE "' . $this->wrap_s . '" THEN 1
                        WHEN translations.key ILIKE "add_cat_desc_%" AND translations.content ILIKE "' . $this->wrap_s . '" THEN 2
                        ELSE 3
                    END DESC
                    ')
            )

Every time I have this error and can do nothing with it

SQLSTATE[42703]: Undefined column: 7 ERROR: column "short_name" does not exist LINE 3: WHEN translations.key IN ("short_nam... ^ (SQL: select "products".* from "products" inner join "translations" on "products"."id" = "translations"."translatable_id" and "translations"."translatable_type" = App\Models\Product and "translations"."locale" = en and ("translations"."key" in (short_name, short_description) or "translations"."key"::text like add_cat_desc_%) left join "page_infos" on "products"."id" = "page_infos"."pageable_id" and "page_infos"."pageable_type" = App\Models\Product and "page_infos"."locale" = en left join "tags" on "products"."id" = "tags"."owner_id" and "tags"."owner_type" = App\Models\Product where ("translations"."content"::text ilike %lab% or "page_infos"."h1_title"::text ilike %lab% or "page_infos"."title"::text ilike %lab% or "page_infos"."desc"::text ilike %lab% or "page_infos"."keywords"::text ilike %lab% or "tags"."content"::text ilike %lab%) group by "products"."id" order by CASE WHEN translations.key IN ("short_name", "short_description") AND translations.content ILIKE "%lab%" THEN 1 WHEN translations.key ILIKE "add_cat_desc_%" AND translations.content ILIKE "%lab%" THEN 2 ELSE 3 END DESC limit 36 offset 0)

Does not matter I insert this code to ->orderByRaw() Method, or in ->select() as you provide, in all cases this I have this error. Do you have any idea ?

jordan-dev

2 years ago

Level 1

I change double quotes to quotes, and it works, BUT )), now new error

Grouping error: 7 ERROR: column "translations.key" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: WHEN translations.key IN

I add that field in groupBy section, and it works but not properly. It produced many doubles of items

Please or to participate in this conversation.