SQL Request using AND condition on Jointure

Yii 1.1 General Discussion
1

Hi,

After trying hundreds of solution for hours I just can’t manage to generate the following request in a proper way using Yii tools. My problem is that I use a AND condition on the jointure that I don’t know how to recreate… Here is the SQL request:




SELECT * FROM picture Pic

LEFT JOIN updatePicture Upi ON Upi.idPicture = Pic.idPicture

  AND Upi.dateUpdate = (SELECT dateUpdate FROM updatePicture WHERE idPicture = Pic.idPicture ORDER BY dateUpdate DESC LIMIT 0 , 1)

My relations between tables have been declared in my model classes so I am using the $criteria->with as follow:




$criteria = new CDbCriteria;

$criteria->alias = 'Pic';

$criteria->together = true;

$criteria->with = array('updatePicture');

But how to specify the AND condition on the jointure? Or maybe it’s just not the best way to write this request…? I am not an expert with SQL either…

Thank you for any help on that…!

2

Hi

use

$criteria->condition = "foreign_table1.col1=:col_val AND

                    foreign_table3.col3=:col_val2";

$criteria->params = array(’:col_val’ => some_val, ‘:col_val2’ => other_val);

$criteria->order = ‘foreign_table3.col5 DESC’;

$criteria->limit = 10;

Thanks

3

Thanks for your reply.

By using your way I will just order the final results, so I don’t think it will do what I want.

On my table updatePicture, I can have multiple line with the same idPicture that I will use for the jointure. I just want to do the jointure on the line with the most recent updateDate, so I need to Order and Limit “before” to join the 2 tables… I’m not sure my explanation is very clear but here is what I tried and worked for the request:




$criteria = new CDbCriteria;

$criteria->alias = 'Pic';

$criteria->select = ' Pic.idUser, Upi.idUpdatePicture';

$criteria->join = ' LEFT JOIN UpdatePicture AS Upi ON Upi.idPicture = Pic.idPicture ';

$criteria->join .= ' AND Upi.dateUpdate = (SELECT dateUpdate FROM updatePicture WHERE idPicture = Pic.idPicture ORDER BY dateUpdate DESC LIMIT 0 , 1)';

The problem is that I don’t know how to display the datas:




$dataReader = $this->findAll($criteria);


foreach ($dataReader as $row){

    echo $row['idUser'];    

    echo $row['idUpdatePicture'];

}

$row[‘idUser’] works but $row[‘idUpdatePicture’] doesn’t work because it looks for Picture.idUpdatePicture…

Any idea how to solve this problem?

Thanks

4

Adding this in my Picture model class was enough:




public idUpdatePicture;

But I don’t think it is the proper way to do it… I think I am missing something :blink: