Hi,
I’m having trouble figuring out how to run a query where the criteria is in a related table.
I’ve got two tables in a many to many relationship
table1: athletes (class Athlete) ...
id: int
first_name varchar(45) etc...
table2: events (class Event)
id_event: int (yes, I know my naming conventions aren't consistent)
event: varchar(45) etc..
And then there’s a table that relates the two:
table3:
athletes_events
id: int
id_athlete: int
id_event: int
in the model for athletes, I’ve specified this relation:
'events'=>array(self::MANY_MANY, 'Event','athletes_events(id_athlete,id_event)'),
I’m trying to retrieve athletes participating only in a certain event, so now in the Athlete controller, I’ve set up $dataProvider as follows…
$dataProvider=new CActiveDataProvider('Athlete', array(
'criteria'=>array(
'with'=>'events',
'together'=>true,
'condition'=>'events.id_event=:id_event',
'params'=>array(':id_event'=>$_GET['id_event']),
'order'=>'last_name',
),
'pagination'=>array(
'pageSize'=>self::PAGE_SIZE,
),
));
but for some reason this is returning an error. I’ve noticed Yii is running two queries- why?
the first is seems to be getting a count for some reason - and this executes the join specified by the "with" clause of the criteria:
SELECT COUNT(DISTINCT `t`.`id`) FROM `athletes` `t` LEFT OUTER JOIN `athletes_events` `events_events` ON (`t`.`id`=`events_events`.`id_athlete`) LEFT OUTER JOIN `events` `events` ON (`events`.`id_event`=`events_events`.`id_event`) WHERE (events.id_event=:id_event). Bind with parameter :id_event='6'
what’s weird is that the next query - which is the one that retrieves the data from the athlete table - does NOT execute a join, and so it returns an error:
SELECT `t`.`id` AS `t0_c0`, `t`.`first_name` AS `t0_c1`, `t`.`last_name` AS `t0_c2` FROM `athletes` `t` WHERE (events.id_event=:id_event) ORDER BY last_name LIMIT 40. Bind with parameter :id_event='6'
So… I guess I’ve got two questions:
- what is the purpose of that first COUNT query
and
- why is the COUNT query joining on events, but the second query which is retrieving data is NOT (causing an error: Base table or view not found: 1109 Unknown table ‘events’ in where clause)?