Yii2 - left join on multiple condition

Question

I have three tables with the following relations,

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

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;

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().

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

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.

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.

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

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

Solution 1

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

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.

Solution 2

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

Solution 3

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.

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.