best way to select

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?

below a mock-up of the preferred output:

Playground (A’dam)

Tags: slides, free

Categories: Kids, Fun

Swimming pool (R’dam)

Tags: free, water slides

Categories: Water

etc.

Tables:




[b]Daytrips[/b]

IdDaytrip (1,2)

CityId (3,4)

Daytrip (Playground, Swimming pool)


[b]Cities[/b]

IdCity (3,4)

City (A'dam, R'dam)


[b]DaytripCategory[/b]

DaytripId (1,1,2)

CategoryId (1,2,3)


[b]Categories[/b]

IdCategory (1,2,3)

Category (Kids, Fun, Water)


[b]DaytripTag[/b]

IdDaytrip (1,1,2,2)

IdTag (1,3,3,5)


[b]Tags[/b]

IdTag (1,3,5)

Tag (slides, free, water slides)



first elucidate the picture

at least for me

Tables:

Cities

(IdCity,City)

(3 , A’dam)

(4, R’dam)

Daytrips

(IdDaytrip,CityId,Daytrip)

(1,3,Playground)

(2,4,Swimming pool)

Categories

IdCategoryCategory

(1,Kids)

(2,Fun)

(3,Water)

DaytripCategory

DaytripIdCategoryId

(1,1)

(1,2)

(2,3)

Tags

IdTag Tag

(1,slides)

(3,free)

(5,water slides)

DaytripTag

IdDaytrip ,IdTag

(1,1)

(1,3)

(2,3)

(2,5)

below a mock-up of the preferred output:

Playground (A’dam)

Tags: slides, free

Categories: Kids, Fun

Swimming pool (R’dam)

Tags: free, water slides

Categories: Water

etc.

in the model




   $models=Daytrips::model()->with('tags','categories')->findAll($criteria);



in the view




 foreach($models as $n=>$model)


   $categories=array();

   foreach ($model->categories as $n=>$cat)

   $categories[]=$cat->Category;

   $tags=array();

   foreach ($model->tags as $n=>$tag)

   $tags[]=$tag->Tag;




      echo CHtml::encode($model->Daytrip.'('. $model->city->City.')');

      echo CHtml::encode('Tags:'. implode(',', $tags));

      echo CHtml::encode('Categories:'. implode(',', $categories));

end foreach;



I hope is what you are looking

[edit]

implode(",", $categories)

implode(",", $tags)

EDIT: it’s working, is this the new way to go with in 1.1rc?

last thing how to make a selection on Category, WHERE CategoryId IN (1,2,3)




<?php

foreach($daytrips as $n=>$model):

    $categories=array();

    foreach ($model->rCategory as $n=>$cat)

        $categories[]=$cat->Category;

        $tags=array();

    foreach ($model->rTag as $n=>$tag)

        $tags[]=$tag->Tag;


        echo CHtml::encode($model->Daytrip.'('. $model->rCity->City.')');

        echo CHtml::encode('Tags:'. implode(',', $tags));

        echo CHtml::encode('Categories:'. implode(',', $categories)) . "<br>";

endforeach;

?>



I do not know

in the controller




 $criteria->condition='CategoryId IN (1,2,3)';

 $models=Daytrips::model()->with('tags','categories')->findAll($criteria);



Thank you!

final problem.

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);



For me it sounds like a right join on category could help.

http://www.yiiframework.com/forum/index.php?/topic/5963-wtih-problem-bei-relationalem-ar/page__view__findpost__p__31044

Using the structure from the linked thread you could set the joinType to right join specifically for category.

HTH

i tried a RIGhT JOIN for rCategory and for categoryFilter. but no luck




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)',

    ),

);



try this




   $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);




no tested ;D

no luck.

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



once more, can someone give my topic some final attention