Yii Framework Forum: best way to select - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

best way to select Rate Topic: -----

#1 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 28 December 2009 - 05:54 AM

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)

0

#2 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 28 December 2009 - 10:18 AM

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

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#3 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 28 December 2009 - 11:54 AM

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

0

#4 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 28 December 2009 - 02:48 PM

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

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#5 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 28 December 2009 - 03:03 PM

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

0

#6 User is offline   steffen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 14-December 09
  • Location:Reutlingen, DE

Posted 28 December 2009 - 04:15 PM

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
0

#7 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 28 December 2009 - 04:25 PM

View Poststeffen, 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)',
    ),
);

0

#8 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 29 December 2009 - 06:49 AM

View Postbas_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 ;D
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#9 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 30 December 2009 - 04:58 AM

View PostHoracio 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 ;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

0

#10 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 06 January 2010 - 11:31 AM

once more, can someone give my topic some final attention
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users