CODEWITHSUNDEEP
phpmysqlyii
aelgoa
aelgoa

Reputation: 1201

why is yii omitting values from query with double inner join?

the following Yii model method should return 2 values, name and descrition, per record, instead it only returns the description. the same query works perfectly when executed directly in MySQL. is Yii preventing one of the joins to be executed?

public function reportEducation(){
  $criteria = array(
    'select' => "
                 concat(person.name_first," ",person.name_last),
                 group_concat(person_studylevel.description)",
    'join' => "
       inner join person on junior = person.id
       inner join person_studylevel on level = person_studylevel.id",
    'group by' => 'junior'
  );
  return PersonEducation::model()->findAll($criteria);
}

this is my query:

select 
   concat(person.name_first," ",person.name_last) as name,
   group_concat(person_studylevel.description separator ", ")
from person_junior_education
  left join person on junior = person.id
  left join person_studylevel on level = person_studylevel.id
group by junior

part of it seems to have something to do with the concat_group function, because if i remove it, it shows the description field correctly.

but the names field never shows up. maybe the problem is how yii handles the mysql functions?

Upvotes: 0

Views: 875

Answers (2)

Asgaroth
Asgaroth

Reputation: 4334

My Advice is, do not use CActiveRecord for complex queries it is just not made for that, it is not a replacement for SQL either. Its only there to take CRUD actions on rows easy.

What I would do, is create a view, and then create a Model for that view. so you can then simple do:

PersonEducationView::model()->findAll(); // easy

But if you must use active record, then may be create 2 attributes in your PersonEducation model, so that it has some place to store the results of the query, also update your query to use aliases, so that it knows where to store them:

   // protected/models/PersonEducation.php
   public $fullname;
   public $description;

   public function reportEducation(){
       $criteria = array(
       'select' => "
            concat(person.name_first," ",person.name_last) AS fullname,
            group_concat(person_studylevel.description) AS description",
        'join' => "
           inner join person on junior = person.id
           inner join person_studylevel on level = person_studylevel.id",
       'group by' => 'junior'
       );
       return PersonEducation::model()->findAll($criteria);
    }

Disclaimer: totally untested

Upvotes: 1

Haensel
Haensel

Reputation: 335

I am not 100% sure but this may be the case because you are not properly calling your column names as Yii is automatically aliasing the table of the model you are using with "t". So if you are refering to a column of your PersonEducation model you would need to prefix it with "t." to be sure your are calling the right one. If you want to change the alias you can use CDbCriteria's "alias" property, setting it to something meaningful like "person_education" or something like that. Hope it helps

Upvotes: 0

Related Questions