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:
1. what is the purpose of that first COUNT query
2. 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)?