Yii2-在多个条件下左联接 [英] Yii2 - left join on multiple condition

问题描述

我有三个具有以下关系的表，

I have three tables with the following relations,

  ------- 1        0..* ------------
 |Product|-------------|Availability|
  -------               ------------
    1 |
      |
    1 |
  --------
 |MetaData|
  --------

我的原始sql看起来像这样

my raw sql looks like this

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;

也就是说，找到每个Product且MetaData.published_state等于1且不包含Availability，以使Availability.start从now()开始超过7天.

That is, find each Product with a MetaData.published_state equal to 1 and with no Availability such that Availability.start more than 7 days from now().

我正在尝试使用ActiveRecord方法完成以下任务，

I'm trying to accomplish the same using ActiveRecord methods, using something like the following,

$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();

但是，这没有返回任何结果.使用Connection::createCommand()运行原始sql返回我期望的行，因此数据没有问题.

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.

我怀疑问题是由join条件和where条件彼此渗出"引起的；联接和位置都适用于联接或位置，而不是单独应用.

I suspect the issue is being caused by the join conditions and the where conditions 'bleeding' into each other; both join and where being applied to either the joining or the where rather than separately.

如何输出正在运行的实际sql查询?这是从控制台控制器调用的操作.

How can I output the actual sql query being run? this is in an action being called from a console controller.

如何更改代码以返回所需的Products?

How can I alter my code to return the desired Products?

推荐答案

我相信这是一个更好的解决方案.代替使用像leftJoin这样的Raw查询，您应该用andOnCondition补充joinWith关系(这将需要的条件添加到连接语句中).

I believe this one is better solution. Instead of using Raw queries like leftJoin you should complement your joinWith relations with andOnCondition (which adds needed where conditions into your join statement).

$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();

此外，当您在关系内编写where子句时，它看起来更干净.它的作用与在外部编写相同(如果我没记错的话)，但是在重构查询时，您可以轻松删除整个关系而不会忘记外部的关系条件.

In addition it looks cleaner when you write where clauses 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.

这篇关于Yii2-在多个条件下左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！