PHP (Laravel 4) and PostgreSQL search

Question

I have 2 tables "messages" and "users", structures as the following:

users
id | first_name | last_name | ...


messages
id | subject | message | from_user | to_user

I want to implement a search function where a user can search for example "Hello World" and the result would include all matching columns from both tables for first_name, last_name, subject, message for "Hello" and "World".

I am using laravel 4, tried doing something like this:

 return Message::where(function($query) use ($userId, $keyword)
                {
                    $query->where('subject', 'like', $keyword)
                            ->orWhere('message', '=', $keyword); 
                })
                ->where('is_deleted', '=', false)
                ->orderBy('is_seen', 'asc')                                 
                ->paginate(20);

No idea, how to loop to get search results for each of "Hello" and "world". Also how to put in the join and search first_name and last_name from users table. Would very much appreciate help.

Answer 1

Since you are using PostgreSQL, try using PostgreSQL's full text search capabilities to improve performance on searches. A tutorial can help, Otherwise, you can go with the 'database agnostic' method as answered.

Answer 2

You can search your messages for all keywords of a search string pretty easily using this closure you already have in the query:

return Message::where(function($query) use ($userId, $searchString)
                {
                    foreach(explode(' ', $searchString) as $keyword)
                    {
                        $query->where('subject', 'like', '%'. $keyword .'%')
                                ->orWhere('message', 'like', '%'. $keyword .'%'); 
                    }
                })
                ->where('is_deleted', '=', false)
                ->orderBy('is_seen', 'asc')                                 
                ->paginate(20);