Example searching via relations attributes

[visualight]

The structure of my database is as follows:
Table listings:

protected $ fillable = [
        'user_id',
        'category_id',
        'title',
        'slug',
        'description',
        'address',
        'number',
        'city',
        'latitude',
        'longitude',
        'phone',
        'E-mail',
        'website',
        'Facebook',
        'twitter',
        'Youtube',
        'has_timetable',
        'timetable',
        'cover',
        'gallery',
        'is_verified',
        'in_postmoderation',
        'is_featured',
        'status'
    ];

Table Keywords:

protected $ fillable = [
        'listing_id',
        'title',
        'slug'
    ];

I query the keywords table against the listing_id to find out if there are keywords for the listing table.

Suddenly my searchable results gives me more results than expected. 11 results for 4 real records in the listings table). Changing the join method does not change anything. If I apply distinct () in the SQL request, the elements are well filtered but the response for:

$ listings = $ results-> paginate (1); // test
$ listings-> lastPage (); // => results 11 (not 4).

I guess if I apply a groupBy it will solve the problem but goupBy seems not to want to work.

I have the following error if I apply as follows:

LISTING MODEL

protected function applySearchableJoins ($ query)
    {
        foreach ($ this-> searchableJoins () as $ table => $ join) {
            $ joinMethod = $ join [2] ?? 'leftJoin';
            $ query -> {$ joinMethod} ($ table, $ join [0], '=', $ join [1]) -> groupBy ('listings.id'); // id or other fields not work
        }
    }

ERROR
SQLSTATE [42000]: Syntax error or access violation: 1055 Expression # 29 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hybrisdev3.keywords.title' which is not functionally dependent on columns in GROUP BY

SQL
select listings. *, (LOCATE ('b', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), '' ), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 1) + LOCATE ('o', CONCAT (IFNULL ( (listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 2) + LOCATE ('u', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')) , 3) + LOCATE ('l', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ( (keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 4) + LOCATE ('a', CONCAT (IFNULL ((listings.title ), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ( (CONCAT (users.firstname, "", users.lastname)), '')), 5) + LOCATE ('n', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description ), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '' ))), 6) + LOCATE ('g', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 7) + LOCATE ('e', CONCAT (IFNULL ((listings .title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users .firstname, "", users.lastname)), '')), 8) + LOCATE ('r', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), '' ), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 9 ) + LOCATE ('i', CONCAT (IFNULL ((listings.title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), I FNULL ((keywords.title), ''), IFNULL ((CONCAT (users.firstname, "", users.lastname)), '')), 10) + LOCATE ('e', CONCAT (IFNULL ((listings .title), ''), IFNULL ((listings.description), ''), IFNULL ((categories.title), ''), IFNULL ((keywords.title), ''), IFNULL ((CONCAT (users .firstname, "", users.lastname)), '')), 11)) AS sort_index from listings left join users on users.id = listings.user_id left join categories on categories.id = listings.category_id left join keywords on keywords.listing_id = listings.id where listings.status =? and (listings.title like "% boulangerie%" OR listings.description like "% boulangerie%" OR categories.title like "% boulangerie%" OR keywords.title like "% boulangerie%" OR CONCAT (users.firstname, "" , users.lastname) like "% boulangerie%") and listings.deleted_at is null group by listings.id, listings.id, listings.id order by sort_index asc

LISTING MODEL

protected $searchable = [
        'columns' => [
            'listings.title',
            'listings.description',
            'categories.title',
            'keywords.title',
            'author_full_name' => 'CONCAT(users.firstname, " ", users.lastname)'
        ],
        'joins' => [
            'users' => ['users.id', 'listings.user_id'],
            'categories' => ['categories.id', 'listings.category_id'],
            'keywords' => ['keywords.listing_id', 'listings.id']
        ]
    ];

How I can get only 4 record s as expected without a groupBy method OR how i can apply the groupBy on the search query ? Thank you

[ajcastro]

UPDATE: groupBy does not really solve this

@visualight maybe you need to put the groupBy outside the foreach loop.

protected function applySearchableJoins ($ query)
    {
        foreach ($ this-> searchableJoins () as $ table => $ join) {
            $ joinMethod = $ join [2] ?? 'leftJoin';
            $ query -> {$ joinMethod} ($ table, $ join [0], '=', $ join [1]);
        }
        $query-> groupBy ('listings.id');
    }

also pls send me sqldump file of your database so I can test it in my local.

[visualight]

@ajcastro I tested without success.I did not know why right join give same results as left join. Here is my SQL dump for tests.

hybrisdev3.sql.zip

[ajcastro]

@VisualLight.. right join will really include each rows in keywords that's why it duplicates those rows...

here is what you can do...

in your ExactSearch, override the sortColumns():

// Assuming that your $searchable property looks like this
class Listing extends Model 
{
    protected $searchable = [
        'columns' => [
            'listings.title',
            'listings.description',
            'categories.title',
            'keywords.title',
            'user_name' => 'CONCAT (users.firstname, "" , users.lastname)',
        ],
    ];

}

class ExactSearch extends SublimeSearch 
{
    public function sortColumns()
    {
        // Exclude the keywords.title 
        return collect(parent::sortColumns())->reject(function($column) {
            return $column === 'keywords.title'; // exclude 'keywords.title' in sort by relevance
        })->all();
    }
}

// Then call distinct() after search() to remove duplicate results
$query->search($searchStr)->distinct();

[visualight]

@ajcastro the final result is the same. $query->search($str)->distinct()->paginate(1) give me a not correct value when $query->lastPage(); ==> 11 (it will be 2). lastPage() is used in Ajax (show more results button) and is provided natively by eloquent. paginate() give me more pages than expected.

[ajcastro]

@visualight are you sure you exclude the keywords.title in sortColumns()? can you paste here dump sql here

[visualight]

Yes. Here is a resume of my configuration :

Listing Model

/**
     * Searchable configuration
     * @var array
     */

    protected $searchable = [
        'columns' => [
            'listings.title',
            'listings.description',
            'categories.title',
            'keywords.title',
            'author_full_name' => 'CONCAT(users.firstname, " ", users.lastname)',
        ],
        'joins' => [
            'users' => ['users.id', 'listings.user_id'],
            'categories' => ['categories.id', 'listings.category_id'],
            'keywords' => ['keywords.listing_id', 'listings.id'],
        ]
    ];

    /**
     * Override defaultSeach to exact search match
     * @return ExactSearch
     */
    public function defaultSearchQuery()
    {
        return new ExactSearch($this, $this->searchableColumns(), $this->sortByRelevance, 'where');
    }

ExactSearch Model

class ExactSearch extends SublimeSearch
{
    protected function parseSearchStr($searchStr)
    {
        return "%{$searchStr}%"; // produces "where `column` like '%{$searchStr}%'"
    }

    public function sortColumns()
    {
        // Exclude the keywords.title
        return collect(parent::sortColumns())->reject(function($column) {
            return $column === 'keywords.title'; // exclude 'keywords.title' in sort by relevance
        })->all();
    }
}

SQL DUMP

dd($results = Listing::where('listings.status', 2)->with(['category' => function($q){
            $q->with('rubrique');
        }])->search('boulangerie')->distinct()->toSQl());

Results here => http://dev3.hybris-studio.be/sql

[visualight]

Note pagination :

dd($results = Listing::where('listings.status', 2)->with(['category' => function($q){
            $q->with('rubrique');
        }])->search('boulangerie')->distinct()->paginate(1));

Results here : http://dev3.hybris-studio.be/test-paginate

Edit : Response for total is 11. It will be 2 if I disable keywords searching.

[ajcastro]

Results here => http://dev3.hybris-studio.be/sql

^ @visualight Please fix this so it display only string sql, because it is currently dumping the query builder instance..

Results here : http://dev3.hybris-studio.be/test-paginate

^ Please return the paginator instead of dumping it so we only see the actual data

[ajcastro]

I am thinking of a feature to search on the relation instead of doing join for that use-case.
Remove the keywords.title in the columns and joins sections.

<?php

class Listing extends Model
{
    protected $searchable = [
        'columns' => [
            'listings.title',
            'listings.description',
            'categories.title',
            'author_full_name' => 'CONCAT(users.firstname, " ", users.lastname)',
        ],
        'joins' => [
            'users' => ['users.id', 'listings.user_id'],
            'categories' => ['categories.id', 'listings.category_id'],
        ],
        'relations' => [
            'keywords' => ['title'],
        ],
    ];

    /**
     * Override defaultSeach to exact search match
     * @return ExactSearch
     */
    public function defaultSearchQuery()
    {
        return new ExactSearch(
            $this, 
            $this->searchableColumns(), 
            $this->sortByRelevance, 
            'where', 
            $this->searchable['relations'] ?? [] // let's pass our relations here
        );
    }

}

class ExactSearch extends SublimeSearch
{
    protected $relations; 
    /**
     * Override construct so we can pass the relations option
     */
    public function __construct(
        $query, 
        $columns = [], 
        $sort = true, 
        $searchOperator = 'where', 
        $relations = []
    ) {
        parent::__construct($query, $columns, $sort, $searchOperator);
        $this->relations = $relations;
    }

    protected function parseSearchStr($searchStr)
    {
        return "%{$searchStr}%"; // produces "where `column` like '%{$searchStr}%'"
    }

    /**
     * Override search() method so we can perform the search in the relations
     */
    public function search($searchStr)
    {
        $query = parent::search($searchStr);

        /*
        $this->relations looks like this:
        [
            'keywords' => ['title'],
        ],
         */
        $searchStr = $this->parseSearchStr($searchStr);
        foreach ($this->relations as $relation => $columns) {
            $query->orWhereHas($relation, function ($query) use ($searchStr, $columns) {
                foreach ($columns as $column) {
                    $query->where($column, 'like', $searchStr);
                }
            });
        }

        return $query;
    }
}

[visualight]

Results here => http://hybris-studio.be/sql

^ @visualight Please fix this so it display only string sql, because it is currently dumping the query builder instance..

Results here : http://hybris-studio.be/test-paginate

^ Please return the paginator instead of dumping it so we only see the actual data

@ajcastro Dumps have been fixed.

[visualight]

I am thinking of a feature to search on the relation instead of doing join for that use-case.
Remove the keywords.title in the columns and joins sections.

@ajcastro Your patch works like a charm. Have you planned an update for a simpler integration or should I just leave the patch as such?

[ajcastro]

@visualight great!
I am planning to do it maybe later. I'll update here when it is done.

[ajcastro]

I thought about this for a while. In the meantime, the acceptable solution is like this, overriding the methods so that anyone can customize it on their own application level.