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:
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
- 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)?