Yii2 - 在多个条件下左连接

2022-01-07 00:00:00 php mysql yii2

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

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 条件相互渗透"引起的；join 和 where 都应用于 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 这样的原始查询，你应该用 andOnCondition 补充你的 joinWith 关系(这会在你的 join 语句中添加需要的 where 条件).

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.

相关文章