Hi guys, i build a website with a search feature but i have a problem with the LIMIT in my query. There are 8000+ items in my database and based on the search criterea i want to show the user always 100 items.
But at the moment it’s always showing less then the LIMIT 100 i set.
EDIT: when i fire the query directly in PHPMyadmin i get 100 results…
query
Querying SQL: SELECT `t`.`IdDaytrip` AS `t0_c0`, `t`.`Daytrip` AS `t0_c1`,
`t`.`Street` AS `t0_c3`, `t`.`Number` AS `t0_c4`, `t`.`ZipCode` AS `t0_c6`,
`t`.`YouTube` AS `t0_c11`, `t`.`Active` AS `t0_c14`, `t`.`Phone` AS
`t0_c7`, geodistkm(52.360194719, 4.88891601562, 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`.`Deeplink` AS `t1_c5`, `rCity`.`Latitude` AS `t1_c6`,
`rCity`.`Longitude` AS `t1_c7`, `rCity`.`GoogleMatch` AS `t1_c8`,
`rCategory`.`IdCategory` AS `t2_c0`, `rCategory`.`Category` AS `t2_c1`,
`rTag`.`IdTag` AS `t3_c0`, `rTag`.`Tag` AS `t3_c1` FROM `Daytrips` `t`
LEFT OUTER JOIN `Cities` `rCity` ON (`t`.`CityId`=`rCity`.`IdCity`) INNER
JOIN `DaytripCategory` `rCategory_rCategory` ON
(`t`.`IdDaytrip`=`rCategory_rCategory`.`DaytripId`) INNER JOIN `Categories`
`rCategory` ON
(`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`) INNER JOIN
`DaytripTag` `rTag_rTag` ON (`t`.`IdDaytrip`=`rTag_rTag`.`DaytripId`) INNER
JOIN `Tags` `rTag` ON (`rTag`.`IdTag`=`rTag_rTag`.`TagId`) WHERE (Active =
1 AND rCategory.IdCategory IN (1,2,3,4,5,6)) ORDER BY Distance ASC,
Priority DESC, RAND(), CategoryId, Tag LIMIT 100
Controller
$criteria = new CDbCriteria(array(
'condition' => 'Active = 1 AND rCategory.IdCategory IN ('.$cats.')',
'order' => 'Distance ASC, Priority DESC, RAND()',
'limit' => 100
));
$daytrips = Daytrip::model()->distance()->with(array('rCity', 'rCategory', 'rTag'))->together()->findAll($criteria);
Model
public function relations() {
return array(
'rCity' => array(self::BELONGS_TO, 'City', 'CityId'),
'rCategory' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)',
'joinType' => 'INNER JOIN',
'order' => 'CategoryId',
),
'rTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',
'joinType' => 'INNER JOIN',
'order' => 'Tag'
),
);
}
public function scopes() {
return array(
'distance' => array(
'select' => array('IdDaytrip', 'Daytrip', 'Street', 'Number', 'ZipCode', 'YouTube', 'Active', 'Phone', new CDbExpression("geodistkm($this->Latitude, $this->Longitude, rCity.Latitude, rCity.Longitude) AS Distance")),
)
);
}