Geek asks and answers

Laravel - Full text search in multiple tables

I am using Laravel 4 and the Eloquent model for the project we are working on. The database matches 3NF and everything works fine. Also, all MySQL tables were transferred back from InnoDB to MyISAM, since the version of MySQL is <5.6 (full-text search in InnoDB is only supported with 5.6 and higher).

When creating some database search filters, I find a lack using the Eloquent model and Query Builder. In particular, especially when trying to perform a full text search on columns from multiple tables (and stay in the context of the Eloquent object).

For simplicity, we have the following database structure:

--projects
    --id
    --name
    --status
    --...
--users
    --id
    --...
--roles
    --id
    --project_id
    --user_id
    --...
--notes
    --id
    --project_id
    --user_id
    --note
    --....

( ) , , ( projects).

if (Request::isMethod('post'))
        {

                $filters = array('type_id','status','division','date_of_activation','date_of_closure');

                foreach ($filters as $filter) {
                    $value = Input::get($filter);
                    if (!empty($value) && $value != -1) {//-1 is the value of 'ALL' option
                        $projects->where($filter,'=',$value);
                    }
                }

                $search = Input::get('search');

                if (!empty($search)) {
                    $projects->whereRAW("MATCH(name,description) AGAINST(? IN BOOLEAN MODE)",array($search));
                }

        }


// more code here...
// some more filters...
// and at the end I am committing the search by using paginate(10)

return View::make('pages/projects/listView',
            array(
            "projects" => $projects->paginate(10)
            )
        );

, - projects.name, projects.description notes.note. , Eloquent, Query Builder , , /:

  • Query Builder , Eloquent . , , Eloquent. Eloquent Project::find($id) , .
  • "where", , . , "" "Query Builder" .
  • , "".

Laravel API, SQL- Eloquent. whereRAW(), . , , .

, :

  • , Eloquent. , , Query Builder.
  • , Query Builder Eloquent? ( Project::find($id) ).
  • , , Eloquent Query Builder where , get() paginate(10) .

, Eloquent Query Builder - . Eloquent SQL-, , Eloquent . Query Builder Laravel.

, , / Laravel , , !

, , :)

+3
2

, /s

public function scopeSearch($query, $q) 
{        
   $match = "MATCH(`name`, `description`) AGAINST (?)";
   return $query->whereRaw($match, array($q))
                ->orderByRaw($match.' DESC', array($q));  
}

" "

$projects = Project::search(Input::get('search'))->get();

, , , .

+4

, , innoDB ( , ), , . ""

SELECT MATCH(name,description) AGAINST(? IN BOOLEAN MODE),
       MATCH(notes.note) AGAINST(? IN BOOLEAN MODE)
FROM ...

.

WHERE
MATCH(name,description) AGAINST(?) OR
MATCH(notes.note) AGAINST(?)
+1

More articles:


All Articles