[bas_vdl]

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)

[gallego123]

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)

This post has been edited by Horacio Segura: 28 December 2009 - 10:34 AM

[bas_vdl]

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

[gallego123]

bas_vdl, on 28 December 2009 - 11:54 AM, said:

EDIT: is this the new way to go with in 1.1rc?

I do not know

Quote

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

in the controller

 $criteria->condition='CategoryId IN (1,2,3)';
 $models=Daytrips::model()->with('tags','categories')->findAll($criteria);

[bas_vdl]

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

[steffen]

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

http://www.yiiframew...dpost__p__31044


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

HTH

[bas_vdl]

steffen, on 28 December 2009 - 04:15 PM, said:

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

http://www.yiiframew...dpost__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)',
    ),
);

[gallego123]

bas_vdl, on 28 December 2009 - 03:03 PM, said:

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.

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

[bas_vdl]

Horacio Segura, on 29 December 2009 - 06:49 AM, said:

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

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

[bas_vdl]

once more, can someone give my topic some final attention