Yii Framework Forum: MANY_MANY $withOption['filter']['params'] - Yii Framework Forum

Jump to content

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

MANY_MANY $withOption['filter']['params'] Rate Topic: -----

#1 User is offline   bas_vdl 

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

Posted 27 December 2009 - 08:09 AM

When I select Category 1, 2, 3 it will show me all the Daytrips the belongs to Category 1. But not the Daytrips that only belongs to Category 2 or 3 or 2 and 3.

Below you will find the relations and the output of the query profiling.

BTW: when i print implode(",", $_POST['Categories']); it will show me 1,2,3

thank you in advance

Model - relations:
public function relations() {
    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)',
            'condition' => 'categoryFilter.IdCategory IN (:IdCategory)',
        ),
    );
}


Controller:
    public function actionResults() {
        if(Yii::app()->request->isPostRequest) {
            Daytrip::model()->mapToLatLon($_POST['yt0_x'], $_POST['yt0_y']);
            
            $lat = Daytrip::model()->Latitude;
            $lon = Daytrip::model()->Longitude;
            
            $criteria = new CDbCriteria;
            $criteria->select = array('IdDaytrip', 'Daytrip', 'YouTube', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance"));
            $criteria->condition = 'Active = 1';
            $criteria->group = 'IdDaytrip';
            $criteria->order = 'Distance ASC, Priority DESC, RAND()';
            $criteria->limit = 100;
            
            $withOption = array('rCity');
            $withOption['categoryFilter']['params'][':IdCategory'] = implode(",", $_POST['Categories']);

            $daytrips = Daytrip::model()->with($withOption)->together()->findAll($criteria);
        
            $this->render('results', array('daytrips' => $daytrips));
        }
    }


Querying SQL: SELECT `Daytrips`.`IdDaytrip` AS `t0_c0`,
`Daytrips`.`Daytrip` AS `t0_c1`, `Daytrips`.`YouTube` AS `t0_c11`,
geodistkm(52.360194719, 4.89990234375, 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` 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`)
WHERE (Active = 1) AND (categoryFilter.IdCategory IN (:IdCategory)) GROUP
BY IdDaytrip ORDER BY Distance ASC, Priority DESC, RAND() LIMIT 100

0

#2 User is offline   bas_vdl 

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

Posted 28 December 2009 - 07:32 AM

i did some more debugging

Daytrip1 belongs to cat1, cat2, cat3
Daytrip2 belongs to cat1
Daytrip3 belongs to cat2

when i select cat 1, 2, 3 it will show me Daytrip1 and Daytrip2 but not Daytrip3
0

#3 User is offline   nexus246 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 02-December 09

Posted 28 December 2009 - 09:10 AM

Does it work when you hardcode category ids to relation?

'condition' => 'categoryFilter.IdCategory IN (1,2,3)',

0

#4 User is offline   bas_vdl 

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

Posted 28 December 2009 - 10:03 AM

View Postnexus246, on 28 December 2009 - 09:10 AM, said:

Does it work when you hardcode category ids to relation?

'condition' => 'categoryFilter.IdCategory IN (1,2,3)',



fuck yeah, that's working...

and now???

EDIT:
this is working as well
'condition' => 'categoryFilter.IdCategory IN ' . new CDbExpression('(1,2,3)'),


EDIT2:
this is working as well too
            $cats = implode(",", $_POST['Categories']);
            $withOption = array('rCity', 'rTag', 'rCategory', 'categoryFilter' => array('condition' => "categoryFilter.IdCategory IN ($cats)"));
            
            $daytrips = Daytrip::model()->with($withOption)->together()->findAll($criteria);


it's just the params :IdCategory
0

#5 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 20 July 2010 - 05:27 AM

I believe php PDO does not support IN..
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