Laravel – search with multiple keywords against multiple columns with the search result to be ordered in relevance

by

I’m not sure whether this has another method. But this is what I thought of it

$word1 = 'word1';
$word2 = 'word2';
$word3 = 'word3';

$all = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->where('meta_name', 'like', "%{$word2}%")
    ->where('meta_name', 'like', "%{$word3}%")
    ->orWhere(function($query) use ($word1, $word2, $word3) {
        $query->where('meta_description', 'like', "%{$word1}%")
              ->where('meta_description', 'like', "%{$word2}%")
              ->where('meta_description', 'like', "%{$word3}%");
    });

$twoWords = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->where('meta_name', 'like', "%{$word2}%")
    ->orWhere(function($query) use ($word1, $word2) {
        $query->where('meta_description', 'like', "%{$word1}%")
              ->where('meta_description', 'like', "%{$word2}%");
    })
    ->whereNotIn('id', $all->pluck('id'));

$oneWord = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->orWhere('meta_description', 'like', "%{$word1}%")
    ->whereNotIn('id', $all->pluck('id'))
    ->whereNotIn('id', $twoWords->pluck('id'));

and finally union all

$posts = $all->union($twoWords)->union($oneWord)->get(); // check this first
# or
$posts = $all->union($twoWords)->union($oneWord)->skip($start)->take($this->rowperpage)->get();

More Related Contents:

  1. convert mysql query to laravel query builder
  2. Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
  3. Implementation of Levenshtein distance for mysql/fuzzy search?
  4. MySQL find_in_set with multiple search string
  5. MySQL search and replace some text in a field
  6. Search for string within text column in MySQL
  7. Search all columns of a table using a single where condition with single keyword in mysql
  8. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  9. How to fuzzy match email or telephone by Elasticsearch?
  10. How to integrate ElasticSearch with MySQL?
  11. Is there a way to “limit” the result with ELOQUENT ORM of Laravel?
  12. How can I implement single table inheritance using Laravel’s Eloquent?
  13. SQL search multiple values in same field
  14. Laravel join with 3 Tables
  15. Laravel Eloquent group by most recent record
  16. Accent insensitive search query in MySQL
  17. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  18. Laravel Eloquent Ignore Casing
  19. Creating MYSQL Procedure in Laravel 4 Migrations
  20. How to allow fulltext searching with hyphens in the search query
  21. How to group by week in MySQL?
  22. Mysql order by specific ID values
  23. Row number per group in mysql
  24. Resetting ROOT password in MySQL 5.6
  25. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  26. Field ‘id’ doesn’t have a default value?
  27. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  28. MySQL – SQL_BIG_SELECTS
  29. Can I launch a trigger on select statement in mysql?
  30. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci

Leave a Comment