Hy guys im looking for the best way to get the following information from the database and print this on my yii page.
The user need to be able to select on Category.
can this be done by using one query and in view a couple of foreach loops to print the tags and categories or do i need to fire for every Daytrip a new query to select te categories and tags?
When i select Category 1, 2 and 3 it will only show for a Daytrip that belongs to Category 1, 2, 3, 4, 5 and 6 the categories that are selected (1, 2, 3).
It have to print all the categories where the daytrip belongs to, not only the categories that are selected.
$cats = implode(",", $_POST['Categories']);
$criteria = new CDbCriteria;
$criteria->select = array('IdDaytrip', 'Daytrip', 'YouTube', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance"));
$criteria->condition = "Active = 1 AND IdCategory IN ($cats)";
$criteria->group = 'IdDaytrip';
$criteria->order = 'Distance ASC, Priority DESC, RAND()';
$criteria->limit = 100;
$daytrips = Daytrip::model()->with('rCity', 'rCategory', 'rTag')->together()->findAll($criteria);
$criteria->condition='exists (select 1
from DaytripCategory dc2
where dc2.DaytripId=Daytrips.DaytripId //*** I guess the alias is Daytrips
and dc2.CategoryId IN (1,2,3))';
$models=Daytrips::model()->with('tags','categories')->findAll($criteria);
when i remove ‘rCategory’ from: $daytrips = Daytrip::model()->with(‘rCity’, ‘categoryFilter’, ‘rTag’)->together()->findAll($criteria);
it’s working but than i get lazy loading. the first query is to make the selection of the daytrips and then i get 100 queries to get the categories (lazy loading)
does this effect the performance… if yes in a bad way then i need a solution
11:03:33.94506 profile system.db.CDbCommand.query
end:system.db.CDbCommand.query(SELECT `Daytrips`.`IdDaytrip` AS `t0_c0`,
`Daytrips`.`Daytrip` AS `t0_c1`, `Daytrips`.`YouTube` AS `t0_c11`,
geodistkm(53.4387647226, 3.427734375, 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`, `rTag`.`IdTag` AS `t3_c0`, `rTag`.`Tag` AS `t3_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`)
LEFT OUTER JOIN `DaytripTag` `rTag_rTag` ON
(`Daytrips`.`IdDaytrip`=`rTag_rTag`.`DaytripId`) LEFT OUTER JOIN `Tags`
`rTag` ON (`rTag`.`IdTag`=`rTag_rTag`.`TagId`) WHERE (Active = 1 AND
categoryFilter.IdCategory IN (3,1,6)) GROUP BY IdDaytrip ORDER BY Distance
ASC, Priority DESC, RAND() LIMIT 100)
11:03:33.425767 trace system.db.ar.CActiveRecord
lazy loading Daytrip.rCategory
11:03:33.446822 trace system.db.CDbCommand
Querying SQL: SELECT `rCategory`.`IdCategory` AS `t1_c0`,
`rCategory`.`Category` AS `t1_c1` FROM `Categories` `rCategory` INNER JOIN
`DaytripCategory` `rCategory_rCategory` ON
(`rCategory_rCategory`.`DaytripId`=:ypl0) AND
(`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`) ORDER BY
CategoryId
etc
etc
etc