PHP - 一个查询中的 SQL 过滤器类别 [英] PHP - SQL Filter category in one query

问题描述

我的数据库中有 3 个表:Categories、Category_relationships 和 Article.

I have 3 table in my database: Categories, Category_relationships and Article.

Category_relationships 就像

id | category | article
---+----------+--------
1  |     2    |   3
2  |     4    |   3

我得到一个这样的网址:filter.php?category[]=2&category[]=4

Im getting a url like this: filter.php?category[]=2&category[]=4

我想用一个 SQL 查询列出第 3 篇文章.
我该怎么做?

I want to list article number 3 with one SQL query.
How can I do that?

对不起我的英语:)

推荐答案

有多种方法可以得到想要的结果，下面的查询是基于匹配文章是否有这 2 个类别，如果有 3 个类别并且你需要对每个匹配应用不同类别 id 的匹配条件 3 次

There could be multiple approaches to get the desired results, Following queries are based on to match if article has these 2 categories, if there are 3 categories and you need apply matching criteria 3 times with different category id for each match

使用聚合

select a.id, a.name
from article a
join category_relationships cr on a.id = cr.article_id
join category c on c.id = cr.category_id
group by  a.id, a.name
having count(case when c.id = 2 then 1 else null) > 0
and count(case when c.id = 4 then 1 else null) > 0

或

select a.id, a.name
from article a
join category_relationships cr on a.id = cr.article_id
join category c on c.id = cr.category_id
where c.id in(2,4)
group by a.id, a.name
having count(c.id) = 2 

这里 2 是变量取决于要匹配的类别数量，此外，如果有重复的可能性，例如每篇文章有多个具有相同类别 ID 的条目，则使用 count(distinct c.id) = 2

Here 2 is variable depends upon how many categories to match, Also if there is chance of duplicates like there are more than one entries per article with same category id then use having count(distinct c.id) = 2

使用 EXISTS

select a.id, a.name
from article a
where exists(
    select 1
    from category_relationships 
    where a.id = article_id
    and category_id = 2
) and exists(
    select 1
    from category_relationships 
    where a.id = article_id
    and category_id = 4
)

这篇关于PHP - 一个查询中的 SQL 过滤器类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！