php Yii2 - 在多个条件下左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34512457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Yii2 - left join on multiple condition
提问by Pedro del Sol
I have three tables with the following relations,
我有三个表具有以下关系，
------- 1 0..* ------------
|Product|-------------|Availability|
------- ------------
1 |
|
1 |
--------
|MetaData|
--------
my raw sql looks like this
我的原始 sql 看起来像这样
SELECT p.ID FROM product p
LEFT JOIN availability a ON a.productID=p.ID
AND a.start>=DATE_ADD(DATE(now()), INTERVAL 7 DAY)
LEFT JOIN meta_data m ON m.ID=p.meta_dataID
WHERE a.ID IS NULL
AND m.published_state=1;
That is, find each Productwith a MetaData.published_stateequal to 1and with no Availabilitysuch that Availability.startmore than 7 days from now().
也就是说，发现每Product一个MetaData.published_state等于1并没有Availability使得Availability.start从超过7天now()。
I'm trying to accomplish the same using ActiveRecordmethods, using something like the following,
我正在尝试ActiveRecord使用类似以下的方法来完成相同的操作，
$products = Product::find()
->joinWith('metaData')
->joinWith('availability')
->onCondition(['>=', 'availability.start', strtotime('+7 days')])
->where(['is', 'availability.ID', NULL])
->andWhere(['=', 'meta_data.published_state', 1])
->all();
however, this is returning no results. Using Connection::createCommand()to run the raw sql returns the rows I'd expect so there is no issue with the data.
然而，这没有返回任何结果。使用Connection::createCommand()运行原始SQL回报我所期待的行，所以有与数据没有问题。
I suspect the issue is being caused by the joinconditions and the whereconditions 'bleeding' into each other; both join and where being applied to either the joining or the where rather than separately.
我怀疑问题是由join条件和where条件“相互渗透”引起的；join 和 where 都应用于 join 或 where 而不是分开。
How can I output the actual sql query being run? this is in an action being called from a console controller.
如何输出正在运行的实际 sql 查询？这是从控制台控制器调用的操作。
How can I alter my code to return the desired Products?
如何更改我的代码以返回所需的Products？
回答by Yerke
I believe this one is better solution. Instead of using Raw queries like leftJoinyou should complement your joinWithrelations with andOnCondition(which adds needed where conditions into your join statement).
我相信这是更好的解决方案。而不是像leftJoin你那样使用原始查询来补充你的joinWith关系andOnCondition（这将需要的 where 条件添加到你的 join 语句中）。
$products = Product::find()
->joinWith(['metaData' => function (ActiveQuery $query) {
return $query
->andWhere(['=', 'meta_data.published_state', 1]);
}])
->joinWith(['availability' => function (ActiveQuery $query) {
return $query
->andOnCondition(['>=', 'availability.start', strtotime('+7 days')])
->andWhere(['IS', 'availability.ID', NULL]);
}])
->all();
In addition it looks cleaner when you write whereclauses inside relations. It works the same as writing it outside (if I'm not wrong), but when refactoring your query, you can easily delete the whole relation without forgetting relation conditions outside.
此外，当您where在关系中编写子句时，它看起来更干净。它的工作原理与在外面写它一样（如果我没记错的话），但是在重构查询时，您可以轻松删除整个关系而不会忘记外面的关系条件。
回答by Ishan Shah
Just use like below condition.
只需使用如下条件。
$query = Product::find()
-> leftJoin('availability', 'availability.productID=product.ID AND a.start>=DATE_ADD(DATE(now()), INTERVAL 7 DAY)')
->leftJoin('meta_data', 'meta_data.ID=product.meta_dataID')
->where(['is', 'availability.ID', NULL])
->andWhere(['=', 'meta_data.published_state', 1])
->all();
回答by Krisan Paulino
Use this:
用这个：
$sql = 'SELECT p.ID FROM product p
LEFT JOIN availability a ON a.productID=p.ID
AND a.start>=DATE_ADD(DATE(now()), INTERVAL 7 DAY)
LEFT JOIN meta_data m ON m.ID=p.meta_dataID
WHERE a.ID IS NULL
AND m.published_state=1';
$products = Product::findBySql($sql);
Yii Active Record has a findBySql($sql)method that allows you to run and get the data from database using a raw SQL query. It helps a lot when you got confused with Yii's query method or when your query get more complicated to be ran with Yii as in your case I suppose.
Yii Active Record 有一个findBySql($sql)方法允许你使用原始 SQL 查询运行和从数据库中获取数据。当您对 Yii 的查询方法感到困惑时，或者当您的查询变得更加复杂时，使用 Yii 运行时它会很有帮助，就像我想的那样。
So basically, in above block of codes, you just put your raw SQL query to a variable called $sql, and use it as the parameter value of findBySql()method.
所以基本上，在上面的代码块中，您只需将原始 SQL 查询放在一个名为 的变量中$sql，并将其用作findBySql()方法的参数值。
