Sorting by relevance with Laravel Collections whereHas()-CodePudding

Take this working code. It firstly searches the users table for a keyword and then it will use the whereHas() method to search the pivot table category_user for any users assigned to a given category based on a search. This works nicely, all entries are retrieved.

So to be absolutely clear 3 tables are in use, users, categories and category_user.

I am using Laravel collections sortByDesc() to order the entries based on relevance. So if category 1, category 2 and a keyword of my example search term were all stored against user 1 and only category 1 was stored against user 2, user 1 would display first in the results.

The below code does exactly this but ONLY on the users table. I Have had a good scan around and cannot seem a way to work this solution into this type of query, due to the uniqueness of the way I need to order. Can anyone advise any methods?

//props for sortByDesc
$props = ['name', 'slug', 'location', 'company_name', 'biography', 'usually_active'];

//Search Query
//1. Search users table for keywords
//2. use whereHas() to search pivot table of categories for keyword and filters selected
//3. use sortByDesc collections to order results based number of matches, most matches ordered at the top
$search_results = User::where('approved', '=', 1)
->where('user_type_id', '=', 1)
->orWhere(function ($q) use ($search_items) {        
    
    //search users tables
    foreach ($search_items as $value) {
        $q->orWhere('name', 'like', "%{$value}%");
        $q->orWhere('location', 'like', "%{$value}%");
        $q->orWhere('company_name', 'like', "%{$value}%");
        $q->orWhere('biography', 'like', "%{$value}%");
        $q->orWhere('usually_active', 'like', "%{$value}%");
    }

})->whereHas('categories', function ($q) use($search_items) {
    
    //search categories table
    if(!empty($search_items)):
        $q->whereIn('name', $search_items);
        $q->orWhereIn('slug', $search_items);
    endif;

//Now we sort based on best matches
})->get()->sortByDesc(function($i, $k) use ($search_items, $props) {
    
    // The bigger the weight, the higher the record
    $weight = 0;

    // Iterate through search terms
    foreach($search_items as $item) {
        foreach($props as $prop) { 
            if(strpos($i->{$prop}, $item) !== false)
                $weight  = 1; // Increase weight if the search term is found
        }
    }

    return $weight;
});

//get the results and paginate
$search_results = $search_results->values()->all();
$search_results = $this->paginate($search_results);

CodePudding user response：

Short answer:

Add a select for the categories columns you want to filter. Be careful, the resulting dataset will contain columns like categories.name so you may want to adjust keys in your search_items accordingly or alias the added select.

To do so you need to join the categories table in the query, I guessed your foreign keys but feel free to correct them in the join clause to match what you have in database.

Doing so you can then simplify your where clauses in only one

<?
User::join('categories', 'user.categories_id', '=', 'categories.id')
where('approved', '=', 1)
->where('user_type_id', '=', 1)
->orWhere(function ($q) use ($search_items) {        
    
    //search users tables
    foreach ($search_items as $value) {
        $q->orWhere('name', 'like', "%{$value}%");
        $q->orWhere('location', 'like', "%{$value}%");
        $q->orWhere('company_name', 'like', "%{$value}%");
        $q->orWhere('biography', 'like', "%{$value}%");
        $q->orWhere('usually_active', 'like', "%{$value}%");
        $q->orWhereIn('categories.name', $search_items);
        $q->orWhereIn('categories.slug', $search_items);
    }

})
// Add the columns you want to sort on
->addSelect(['categories.name', 'categories.slug']) 
->get()->sortByDesc(function($i, $k) use ($search_items, $props) {
    
    // The bigger the weight, the higher the record
    $weight = 0;

    // Iterate through search terms
    foreach($search_items as $item) {
        foreach($props as $prop) { 
            if(strpos($i->{$prop}, $item) !== false)
                $weight  = 1; // Increase weight if the search term is found
        }
    }

    return $weight;
});

Longer answer:

When you start your query from a model, in your case, User, eloquent automatically adds this model's columns to the select, regardless of the relations that are defined on the model.

If you want to access columns on the joined tables, you need to manually add them to the select with ->addSelect([...]).

You can read more about it on the official docs: https://laravel.com/docs/8.x/queries#specifying-a-select-clause

CodePudding user response：

The solution was to use the withCount() method in order to determine the number of categories stored against a user in the pivot table. I could then use categories_count returned with each item to order the entries based on the number of tagged categories per user.

$search_results = User::where('approved', '=', 1)
    ->where('user_type_id', '=', 1)
    ->orWhere(function ($q) use ($search_items) {        
        
        //search users tables
        foreach ($search_items as $value) {
            $q->orWhere('name', 'like', "%{$value}%");
            $q->orWhere('location', 'like', "%{$value}%");
            $q->orWhere('company_name', 'like', "%{$value}%");
            $q->orWhere('biography', 'like', "%{$value}%");
            $q->orWhere('usually_active', 'like', "%{$value}%");
        }

    })->withCount([
    'categories' => function ($q) use($search_items) {
        
        //search categories table
        if(!empty($search_items)):
            $q->whereIn('name', $search_items);
            $q->orWhereIn('slug', $search_items);
        endif;
    }
])->orderByDesc('categories_count')->paginate(25);