英文:
Mysql query where relationship many to many contains all value of array at least
问题
我尝试制作一个SQL请求，以获取包含一组类别的所有产品。
产品==多对多==类别
我选择了类别ID为11和197，我需要找到同时包含这两个类别的所有产品。
SELECT
DISTINCT p0_.id AS id_0,
p0_.titre AS titre_1
FROM
produit p0_
LEFT JOIN produit_categorie_produit p2_ ON p0_.id = p2_.produit_id
LEFT JOIN categorie_produit c1_ ON c1_.id = p2_.categorie_produit_id
WHERE
c1_.id IN (197, 11)
ORDER BY
titre_1 ASC
LIMIT
12;
这里的IN是一个大问题，因为我从一个类别和其他类别中获取产品，但这不是我想要的。
在经过多个小时的测试后，我真的很困惑。
我在Symfony中使用Doctrine，它生成了这个SQL请求。以下是我的Doctrine查询：
$qb = $this->createQueryBuilder('p')->leftJoin('p.categories', 'c');
$qb->addSelect('p.titre as titre');
if ($value){
$qb->andWhere('c in (:cat)')->setParameter('cat', $value );
}
return $qb->getQuery();
我删除了一些不重要的addSelect部分。
祝一切顺利。
英文:
i try to make an SQL request to get all products contains an array of categories.
Products ==ManyToMany== Categories
I select categories id 11 and 197 and i need to find all products having both of these categories.
SELECT
DISTINCT p0_.id AS id_0,
p0_.titre AS titre_1
FROM
produit p0_
LEFT JOIN produit_categorie_produit p2_ ON p0_.id = p2_.produit_id
LEFT JOIN categorie_produit c1_ ON c1_.id = p2_.categorie_produit_id
WHERE
c1_.id IN (197, 11)
ORDER BY
titre_1 ASC
LIMIT
12;
Here IN is a big probleme because i get product from one categorie and from others, but it's not what i want.
Im really lost here after many hours of test....
I'm using doctrine in symfony and that generate that sql request here is my doctrine query
$qb = $this->createQueryBuilder('p')->leftJoin('p.categories', 'c');
$qb->addSelect('p.titre as titre');
if ($value){
$qb->andWhere('c in (:cat)')->setParameter('cat', $value );
}
return $qb->getQuery();
I removed some addSelect with no important informations.
Best regards
答案1
得分: 2
在纯SQL中，通常会使用 group by 和 having 来确保你选择的产品同时具有两个类别：
SELECT p0_.id AS id_0, p0_.titre AS titre_1
FROM produit p0_
INNER JOIN produit_categorie_produit p2_ ON p0_.id = p2_.produit_id
INNER JOIN categorie_produit c1_ ON c1_.id = p2_.categorie_produit_id
WHERE c1_.id IN (197, 11) -- 任意两个类别中的一个
GROUP BY p0_.id, p0_.titre
HAVING COUNT(*) = 2 -- 两者都匹配
ORDER BY titre_1
LIMIT 12;
如果同一产品可能出现两次相同的类别，那么你需要使用 COUNT(DISTINCT c1_.id)。
请注意，我将 LEFT JOIN 更改为 INNER JOIN，因为查询实际上执行的是内连接。
英文:
In pure SQL, you would typically use group by and having to ensure that the products you select have both categories:
SELECT p0_.id AS id_0, p0_.titre AS titre_1
FROM produit p0_
INNER JOIN produit_categorie_produit p2_ ON p0_.id = p2_.produit_id
INNER JOIN categorie_produit c1_ ON c1_.id = p2_.categorie_produit_id
WHERE c1_.id IN (197, 11) -- any of the two categories
GROUP BY p0_.id, p0_.titre
HAVING COUNT(*) = 2 -- both match
ORDER BY titre_1
LIMIT 12;
If the same category may show up twice for a given product, then you would need COUNT(DISTINCT c1_.id) instead.
Note that I changed the LEFT JOINs to INNER JOINs, since that's what the query does anyway.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论