通过雄辩的查询在多个表中搜索

Question

I have two tables: posts and pages. Based on the keyword, I want to search different columns of the two tables for the occurrence of keyword. The two tables do not have related content.

I have written two queries one for each table. Following are the queries:

$result1 = Post::where('title_en', 'LIKE', '%' . $keyword . '%')
                ->or_where('title_np', 'LIKE', '%' . $keyword . '%')
                ->order_by('id', 'desc')
                ->paginate(10);

$result2 = Page::where('title', 'LIKE', '%' . $keyword . '%')
                ->or_where('content', 'LIKE', '%' . $keyword . '%')
                ->order_by('id', 'desc')
                ->paginate(10);

The above queries return two different Laravel\Paginator object. But I want a single Laravel\Paginator object so that a single pagination is displayed on the page which works for both the queries or on a single query which achieves the functionality of both the above queries. How would I be able to do that?

Answer 1

Since they are unrelated tables, you need to do some trickery that's simply too complex for Eloquent to handle, you need to do something similar to the following to join the two queries together, and still be able to do your limit / ordering on the combined queries:

$results = DB::query('SELECT id, title FROM ( 
SELECT id, title FROM `post` where `title_np` LIKE "%'.$keyword.'%" OR `title_en` LIKE "%'.$keyword.'%"
UNION ALL
SELECT id, title FROM `page` where `title` LIKE "%'.$keyword.'%" OR `content` LIKE "%'.$keyword.'%"
) temp_table
ORDER BY `id` desc
LIMIT 0, 10
')

NB the above is untested, purely an example of the approach you'll need to take, not sure this will actually work.