ajcastro / searchable Goto Github PK

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

When I indicate a table join and the table contains several values, I get in search response the elements multiplied by the number of occurrences of the join table. Is there a method to declare the type of join? (Left, Right, Inner)? Thank you

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'] // SUCKS HERE (i get 10 records the same in search response)
        ]
    ];

I have successfully implemented your package and I am very happy with it but the search results are not strict enough. Is there a method to make an exact mactch of what we type in the search? Your package is intended to find trades. Suddenly if I currently type: "gardener", the search also sends me booksellers when no occurrence of this word exists in the text.

Tests carried out with several records and only one.
Thank you

To be clear, this is not Full-Text searching. This is pattern matching, which causes a db to do a full scan on the table instead of using indexes.

Full-Text queries use much different sql statements.

I'm not sure how this was supposed to work, but if I search for TEST, MySQL query is %T%E%S%T%—which looks for those letters.

In SublimeSearch::parseSearchStr, I see:

'%'.join('%', str_split($searchStr)).'%';

What's the purpose of this?