Subquery on JOIN

Yii 1.1 General Discussion
1

Hi everyone,

I am willing to use a subquery on a join but can’t figure out how to modelize it using Yii tools and how to get the results. The SQL Query looks like that (this one works):




SELECT TR.idTruth, FA.nbFavourite

FROM truth TR

LEFT JOIN

  (SELECT ULC.idTruth, count(ULC.idTruth) AS nbFavourite

   FROM userListContent ULC

   INNER JOIN userList UL ON UL.idUserList = ULC.idUserList 

   WHERE UL.idUser = 1

   GROUP BY ULC.idTruth) FA ON FA.idTruth= TR.idTruth

Truth Model Relations




'userListContents' => array(self::HAS_MANY,'Userlistcontent', 'idTruth'),

Userlistcontent Model Relations




'userList' => array(self::BELONGS_TO, 'Userlist','idUserList),

Because I didn’t know how to modelize it using Yii tools, I am using directly




$criteria->select = "TR.idTruth, FA.nbFavourite";

$criteria->join = "

  LEFT JOIN

  (SELECT ULC.idTruth, count(ULC.idTruth) AS nbFavourite

   FROM userListContent ULC

   INNER JOIN userList UL ON UL.idUserList = ULC.idUserList 

   WHERE UL.idUser = 1

   GROUP BY ULC.idTruth) FA ON FA.idTruth= TR.idTruth";


$model = new Truth;

$datas = $model->findAll($criteria);

foreach ($datas as $row) {

  echo $row->nbFavourite;

}

I am getting the error:

[color="#FF0000"]Active record "Truth" is trying to select an invalid column "FA.nbFavourite". Note, the column must exist in the table or be an expression with alias. [/color]

I tried to declare “public $nbFavourite” in the Truth Model but didn’t work as well. I think my method is wront but can’t figure out any other way to do it. Any help?

2

I’m not sure. but try to use $criteria->with = array(relationId);

as far as i know relation in query may require that

CMIIW

3

Hi Xent,

Thanks for your reply. I already tried to use the "with" but can not figure out how to use it with a customized subquery… The thing is, as far as I know, when you declare the Relation it will automatically generate the :




LEFT JOIN ClassName ON ClassName.primaryKey = t.ForeignKey

Considering:

‘RelationName’=>array(‘RelationType’, ‘ClassName’, ‘ForeignKey’, …additional options)

And what I need is:




LEFT JOIN (SELECT ....)

I found some ways to solve my problem but they are not clean and efficient:

  1. Create a view to avoid the subquery

  2. Using this query instead, that is according to me far less efficient about performances than a join:


 

SELECT TR.idTruth, 

(SELECT count(ULC.idTruth) AS nbFavourite

   FROM userListContent ULC

   INNER JOIN userList UL ON UL.idUserList = ULC.idUserList 

   WHERE UL.idUser = 1 and ULC.idTruth = TR.idTruth

   GROUP BY ULC.idTruth) AS nbFavourite


FROM truth TR

So I am still looking for a way to make my first idea work :)

4

Found this old thread looking for help using a column returned from a subquery in a join.

If you need to use any of the columns returned by the subQuery, you need to add properties to the Yii2 model class, e.g.

$subQuery = FinancialTransaction::find()
    ->select( new \yii\db\Expression( 'SUM(amount) as owing') )
    ->addSelect('booking_id')
    ->groupBy('booking_id');
$query = $query
    ->addSelect(['b.*', 'owing'])
    ->leftJoin(['ft' => $subQuery], 'b.booking_display_id = ft.booking_id');

To access “owing” the model has to have the property (PHPdoc optional):

/**
 * @var float
 */
public $owing=0;