When I select Category 1, 2, 3 it will show me all the Daytrips the belongs to Category 1. But not the Daytrips that only belongs to Category 2 or 3 or 2 and 3.
Below you will find the relations and the output of the query profiling.
BTW: when i print implode(",", $_POST[‘Categories’]); it will show me 1,2,3
thank you in advance
Model - relations:
public function relations() {
return array(
'rCity' => array(self::BELONGS_TO, 'City', 'CityId'),
'rCategory' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)'),
'rTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)'),
'categoryFilter' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)',
'condition' => 'categoryFilter.IdCategory IN (:IdCategory)',
),
);
}
Controller:
public function actionResults() {
if(Yii::app()->request->isPostRequest) {
Daytrip::model()->mapToLatLon($_POST['yt0_x'], $_POST['yt0_y']);
$lat = Daytrip::model()->Latitude;
$lon = Daytrip::model()->Longitude;
$criteria = new CDbCriteria;
$criteria->select = array('IdDaytrip', 'Daytrip', 'YouTube', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance"));
$criteria->condition = 'Active = 1';
$criteria->group = 'IdDaytrip';
$criteria->order = 'Distance ASC, Priority DESC, RAND()';
$criteria->limit = 100;
$withOption = array('rCity');
$withOption['categoryFilter']['params'][':IdCategory'] = implode(",", $_POST['Categories']);
$daytrips = Daytrip::model()->with($withOption)->together()->findAll($criteria);
$this->render('results', array('daytrips' => $daytrips));
}
}
Querying SQL: SELECT `Daytrips`.`IdDaytrip` AS `t0_c0`,
`Daytrips`.`Daytrip` AS `t0_c1`, `Daytrips`.`YouTube` AS `t0_c11`,
geodistkm(52.360194719, 4.89990234375, rCity.Latitude, rCity.Longitude) AS
Distance, `rCity`.`IdCity` AS `t1_c0`, `rCity`.`CountryId` AS `t1_c1`,
`rCity`.`Province` AS `t1_c2`, `rCity`.`Municipality` AS `t1_c3`,
`rCity`.`City` AS `t1_c4`, `rCity`.`Latitude` AS `t1_c5`,
`rCity`.`Longitude` AS `t1_c6`, `rCity`.`GoogleMatch` AS `t1_c7`,
`categoryFilter`.`IdCategory` AS `t2_c0`, `categoryFilter`.`Category` AS
`t2_c1` FROM `Daytrips` LEFT OUTER JOIN `Cities` `rCity` ON
(`Daytrips`.`CityId`=`rCity`.`IdCity`) LEFT OUTER JOIN `DaytripCategory`
`categoryFilter_categoryFilter` ON
(`Daytrips`.`IdDaytrip`=`categoryFilter_categoryFilter`.`DaytripId`) LEFT
OUTER JOIN `Categories` `categoryFilter` ON
(`categoryFilter`.`IdCategory`=`categoryFilter_categoryFilter`.`CategoryId`)
WHERE (Active = 1) AND (categoryFilter.IdCategory IN (:IdCategory)) GROUP
BY IdDaytrip ORDER BY Distance ASC, Priority DESC, RAND() LIMIT 100