通过雄辩的查询在多个表中搜索 [英] Search in multiple tables by eloquent query

问题描述

我有两个表:posts和pages.基于关键字，我想在两个表的不同列中搜索关键字的出现.这两个表没有相关内容.

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);

以上查询返回两个不同的Laravel\Paginator对象.但是我想要一个Laravel\Paginator对象，以便在该页面上显示单个分页，该页面既可用于两个查询，也可在单个查询上显示，可实现上述两个查询的功能.我该怎么做?

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?

推荐答案

由于它们是不相关的表，因此您需要进行一些复杂的操作，以至于Eloquent无法处理，因此您需要执行以下类似操作才能加入两个查询一起使用，仍然可以对合并的查询进行限制/排序:

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.

这篇关于通过雄辩的查询在多个表中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！