mysql - Silverstripe LeftJoin WHERE 两个值

Question

我在类客户和类产品之间有很多关系。例如

数据库客户

| ID  | Name  |
-----------------------
| 01 | Jack   |
| 02 | Joe    |
| 03 | Claire |

数据库产品

| ID  | ProductName |
---------------------------------
| 01 | watch        |
| 02 | pen          |
| 03 | car          |

因此，在开发/构建之后，我得到一个名为 Customer_Products 的新数据库表，其中包含两个类的 ID。假设客户添加了一些产品，如下所示数据库客户_产品

| ID  | CutomerID | PruductID |
----------------------------------------------------
| 01 |  01        |  01       |    -> Jack added watch
| 02 |  02        |  01       |    -> Joe added watch
| 03 |  01        |  02       |    -> Jack added pen
| 04 |  01        |  03       |    -> Jack added car
| 05 |  03        |  01       |    -> Claire added watch

现在我只想显示添加了watch AND pen AND car 的客户(例如)。所以我想使用查询只显示 Jack，因为他添加了 3 种产品。这是我的问题。我的代码:

$sqlQuery = new SQLQuery();
$sqlQuery->setFrom("Customer"); 
$sqlQuery->addLeftJoin(' Customer_Products ','"Customer"."ID" = " Customer_Products "."CustomerID"');
$sqlQuery->addWhere("PruductID = 01  ");

// with this next two lines the result is empty, I know this is wrong but maybe this gives you an idea of what I m looking for. 
// $sqlQuery->addWhere("PruductID = 02 ");                  
// $sqlQuery->addWhere("PruductID = 03  ");     

$rawSQL = $sqlQuery->sql();
$rec = $sqlQuery->execute();

有没有一种方法可以通过一个查询来做到这一点？或者我怎么能做到这一点？感谢帮助!

Answer 1

有几种方法。我喜欢 group by 和 having 方法，因为它是最灵活的:

select c.*
from customers c join
     customer_products cp
     on c.customerid = cp.customerid join
     products p
     on p.productid = cp.productid
where p.name in ('Pen', 'Car', 'Watch')
group by c.customerid
having sum(p.name = 'Pen') > 0 and
       sum(p.name = 'Car') > 0 and
       sum(p.name = 'Watch') > 0;

having 子句中的每个条件都在测试一种产品的存在。 where 子句是可选的，但它可以帮助提高性能。