Laravel Entity Attribute Value Filter Multipe Attributes

Question

我正在尝试开发一个post模型，它与postmeta模型(包括id,post_id,meta_key,meta_value列)具有一对多关系，与term模型(包括term_id等列，以及包括id,post_postId,term_term_term_id列的post_term表)具有多对多关系。

我创建了一个表单，其中包含一些post_metas和术语来搜索和过滤我的帖子。

这个示例PHP代码突出了我使用的内容:

$posts_query = Post::where('post_type', 'add')->where('author', Auth::id())->where(function ($q0) use($filter_metas){
foreach ($filter_metas as $index => $meta) {
$q0->whereHas('postmetas', function ($q0) use ($index,$meta) {
$q0->where('meta_key', $index)->where('meta_value', $meta);
});
}
});

，这是为了得到结果而执行的查询。

select * from `posts` where `post_type` = ? and `author` = ? and (exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` in (?, ?, ?, ?)) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` in (?, ?, ?)) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` > ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?) and exists (select * from `postmeta` where `posts`.`postId` = `postmeta`.`post_id` and `meta_key` = ? and `meta_value` = ?)

我可以从这个查询得到正确的结果，但查询执行太慢，这是我的主要问题。有其他的方法吗?

Answer 1

您可以使用带有聚合的单个连接查询来过滤每个帖子的元属性

$posts = Post::select(['posts.postId','posts.name'])
->where('post_type', 'add')
->where('author', Auth::id())
->join('postmeta', 'posts.postId', '=', 'postmeta.post_id')
->groupBy('posts.postId','posts.name')
->where(function ($query) use ($filter_metas) {
foreach ($filter_metas as $index => $meta) {
$query->havingRaw('sum(case when meta_key = ? and meta_value = ? then 1 else 0 end) > 0', [$index, $meta]);
}
});

在上面的查询havingRaw部分是关键，它将有条件地过滤掉与元信息不匹配的帖子，

Answer 2

试试这个代码，不确定它是否工作，希望你能得到的想法。

$posts_query = Post::where('post_type', 'add')
->where('author', Auth::id())
->leftJoin('postmeta', function($join){
$join->on('posts.postId', 'postmeta.post_id');
})
->where(function ($query) use ($filter_metas) {
foreach ($filter_metas as $index => $meta) {
$query->where('postmeta.meta_key', $index)
->where('postmeta.meta_value', $meta);
}
});