Yii Framework Forum: [SOLVED] Don't select fields from joined tables - Yii Framework Forum

Jump to content

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

[SOLVED] Don't select fields from joined tables Rate Topic: -----

#1 User is offline   bas_vdl 

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

Posted 20 October 2010 - 06:17 AM

If you have some realtions defined it will automaticly select all the fields from the joined tables. But how can i tell Yii not to select these fields?

Even when I use the select it's still selecting the fields from the joined tables.
0

#2 User is offline   lgoss007 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 88
  • Joined: 01-October 09

Posted 20 October 2010 - 06:55 AM

Fill in the select statement from a CDbCriteria object. Check out this page for an example:
Reading Records for AR

For multiple column names be sure to look up the CDbCriteria documentation for select:
CDbCriteria - select
0

#3 User is offline   bas_vdl 

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

Posted 20 October 2010 - 07:15 AM

that's what i did. But it is still selecting fields from the rCity and rCategory relation that mess up the output of the query...

$criteria = new CDbCriteria(array(
    'distinct' => true,
    'select' => array('IdDaytrip', 'Daytrip', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance")),
    'condition' => 'Active = 1',
    'order' => 'Distance ASC, Priority DESC, RAND()',
    'limit' => 100,
    'with' => array('rCity', 'rCategory'),
    'together' => true
));


SELECT DISTINCT `t`.`IdDaytrip` AS `t0_c0`, `t`.`Daytrip` AS
`t0_c1`, geodistkm(52.3534536564, 4.8779296875,
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`.`Deeplink` AS `t1_c5`, `rCity`.`Latitude` AS `t1_c6`,
`rCity`.`Longitude` AS `t1_c7`, `rCity`.`GoogleMatch` AS `t1_c8`,
`rCategory`.`IdCategory` AS `t2_c0`, `rCategory`.`Category` AS `t2_c1`

FROM `Daytrips` `t`
LEFT OUTER JOIN `Cities` `rCity`
ON (`t`.`CityId`=`rCity`.`IdCity`)
LEFT OUTER JOIN `DaytripCategory` `rCategory_rCategory`
ON (`t`.`IdDaytrip`=`rCategory_rCategory`.`DaytripId`)
LEFT OUTER JOIN `Categories` `rCategory`
ON (`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`)
WHERE ((Active = 1) AND (rCategory_rCategory.CategoryId IN (:ycp0, :ycp1))) ORDER BY Distance ASC, Priority DESC, CategoryId, RAND()
LIMIT 100
0

#4 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 21 October 2010 - 01:27 AM

Why it is messing up your result? You can simply ignore.

You can check the documentation of with, it seems that is possible to specify as array, wich params are the same of CDbCriteria, so it should be possible doing something like:

'with' => array('rCity'=>array('select'=>''), 'rCategory'=>array('select'=>'')),


I am not sure about syintax nad I have not tested, but looking at the doc it looks possible
0

#5 User is offline   bas_vdl 

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

Posted 21 October 2010 - 04:16 AM

using 'select'=>'' i get:
Active record "City" is trying to select an invalid column "". Note, the column must exist in the table or be an expression with alias.

using ''select'=>array() it will only select the primary keys of the joined tables.

SELECT DISTINCT `t`.`IdDaytrip` AS
`t0_c0`, `t`.`Daytrip` AS `t0_c1`, `t`.`Street` AS `t0_c3`, `t`.`Number` AS
`t0_c4`, `t`.`ZipCode` AS `t0_c6`, `t`.`Website` AS `t0_c10`, `t`.`Phone`
AS `t0_c7`, `t`.`YouTube` AS `t0_c11`, `t`.`Active` AS `t0_c14`,
geodistkm(52.3871589691, 4.8779296875, rCity.Latitude, rCity.Longitude) AS
Distance, `rCity`.`IdCity` AS `t1_c0`, `rCategory`.`IdCategory` AS `t2_c0`
FROM `Daytrips` `t`
LEFT OUTER JOIN `Cities` `rCity`
ON (`t`.`CityId`=`rCity`.`IdCity`)
LEFT OUTER JOIN `DaytripCategory` `rCategory_rCategory`
ON (`t`.`IdDaytrip`=`rCategory_rCategory`.`DaytripId`)
LEFT OUTER JOIN `Categories` `rCategory`
ON (`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`)
WHERE ((Active = 1) AND (CategoryId IN (:ycp0, :ycp1)))
ORDER BY Distance ASC, Priority DESC, RAND(), CategoryId
LIMIT 100

Because some Daytrips belongs to more categories it will select every daytrip with more than one category twice. So i'm using distinct to get rit of the redundant daytrips. But as long as yii selects fields from the joined tables i still get redundant Daytrips.

With the fields selected you get something like:

IdDatrip | Daytrip | CategoryId
1 | Bla | 2
1 | Bla | 5
2 | Too | 1


with the distinct i get rid of the redundant records but than the limit is not right. I set a limit to get 100 records and Yii gives me in the situation as shown above only 2. Because it selct 100 records and then removes (DISTINCT) the redundant records. so you will get less than 100 records...

bottom line, without the fields from the joined tables in the select statement it will solve my problem :)
0

#6 User is offline   lgoss007 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 88
  • Joined: 01-October 09

Posted 22 October 2010 - 09:21 AM

What version of Yii are you using? I thought 1.1.4 was supposed to fix together..

If you want to fix it yourself the easiest way is to create a database view or don't use ActiveRecord and build the query yourself in Yii with the CDbCommand. Not sure if there is any other way around???
0

#7 User is offline   bas_vdl 

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

Posted 22 October 2010 - 09:51 AM

View Postlgoss007, on 22 October 2010 - 09:21 AM, said:

What version of Yii are you using? I thought 1.1.4 was supposed to fix together..

If you want to fix it yourself the easiest way is to create a database view or don't use ActiveRecord and build the query yourself in Yii with the CDbCommand. Not sure if there is any other way around???


v. yii-1.1.4.r2429

i will look into views or handwritten queries
0

#8 User is offline   bas_vdl 

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

Posted 01 November 2010 - 06:22 AM

Still havn't solved the problem!

Can i use input parameters for views? otherwise views are not a solution for my problem...

But there must be a solution to prevent Yii selecting fields from the joined tables if you dont need them...
0

#9 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 01 November 2010 - 07:21 AM

Did you try to specify the select in the width?

$criteria = new CDbCriteria(array(
    'distinct' => true,
    'select' => array('IdDaytrip', 'Daytrip', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance")),
    'condition' => 'Active = 1',
    'order' => 'Distance ASC, Priority DESC, RAND()',
    'limit' => 100,
    'with' => array('rCity'=>array('select'=>''), 'rCategory'=>array('select'=>'')),
    'together' => true
));


0

#10 User is offline   bas_vdl 

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

Posted 01 November 2010 - 07:28 AM

View Postzaccaria, on 01 November 2010 - 07:21 AM, said:

Did you try to specify the select in the width?

$criteria = new CDbCriteria(array(
    'distinct' => true,
    'select' => array('IdDaytrip', 'Daytrip', new CDbExpression("geodistkm($lat, $lon, rCity.Latitude, rCity.Longitude) AS Distance")),
    'condition' => 'Active = 1',
    'order' => 'Distance ASC, Priority DESC, RAND()',
    'limit' => 100,
    'with' => array('rCity'=>array('select'=>''), 'rCategory'=>array('select'=>'')),
    'together' => true
));




yes i did. but then you get:

Active record "Category" is trying to select an invalid column "". Note, the column must exist in the table or be an expression with alias.


also tried, array('select'=>array())
0

#11 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 01 November 2010 - 08:14 AM

I don't understand what is your problem.

If you don't want any field of the related table, simply avoid doing with, use a condition.
0

#12 User is offline   bas_vdl 

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

Posted 01 November 2010 - 08:43 AM

View Postzaccaria, on 01 November 2010 - 08:14 AM, said:

I don't understand what is your problem.

If you don't want any field of the related table, simply avoid doing with, use a condition.


without the with() for rCategroy my addInCondition is not working...: "CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CategoryId' in 'where clause'".

Yii can only apply the addInCondition when you select the corresponding field. But when i select the field it messes up the LIMIT in the query...

Because there is a difference in query results if you select the fields from the joined tables, thats why i need to get rit of those fields.

result from phpmyadmin with fields selected from joined tables:
img: http://img146.images...redundancy.jpg/
you see that Daytrip 'Amsterdam Tourist Board' shows up twice. But one of those records will not be displayed in the page. So the query limit is 100 with redundancy... Yii shows 99 records

result from phpmyadmin without fields (`rCity`.`City`, `rCity`.`IdCity`, `rCategory`.`IdCategory`) selected from joined tables:
img: http://img178.images.../i/correct.jpg/
No redundancy, so 100 unique Daytrips.

Code:
            $criteria = new CDbCriteria();
            $criteria->distinct = true;
            $criteria->select = array('t.*');
            $criteria->condition = 'Active = 1';
            $criteria->order = 'Distance ASC, Priority DESC, RAND()';
            $criteria->limit = 100;
            $criteria->together = true;

                $criteria->with = array('rCity'=>array('select'=>array('City')), 'rCategory'=>array('select'=>array()));
                $criteria->addInCondition('CategoryId', $_POST['Categories']);

            $daytrips = Daytrip::model()->distance()->findAll($criteria);


Query from yii:
SELECT DISTINCT geodistkm(52.3669357815, 4.88891601562,
rCity.Latitude, rCity.Longitude) AS Distance, `t`.`IdDaytrip` AS `t0_c0`,
`t`.`Daytrip` AS `t0_c1`, `t`.`ContactPerson` AS `t0_c2`, `t`.`Street` AS
`t0_c3`, `t`.`Number` AS `t0_c4`, `t`.`CityId` AS `t0_c5`, `t`.`ZipCode` AS
`t0_c6`, `t`.`Phone` AS `t0_c7`, `t`.`Fax` AS `t0_c8`, `t`.`Email` AS
`t0_c9`, `t`.`Website` AS `t0_c10`, `t`.`YouTube` AS `t0_c11`,
`t`.`Priority` AS `t0_c12`, `t`.`Modified` AS `t0_c13`, `t`.`Active` AS
`t0_c14`, `t`.`Status` AS `t0_c15`, `t`.`IdDaytrip` AS `t0_c0`,
`rCity`.`City` AS `t1_c4`, `rCity`.`IdCity` AS `t1_c0`,
`rCategory`.`IdCategory` AS `t2_c0` FROM `Daytrips` `t`  LEFT OUTER JOIN
`Cities` `rCity` ON (`t`.`CityId`=`rCity`.`IdCity`)  LEFT OUTER JOIN
`DaytripCategory` `rCategory_rCategory` ON
(`t`.`IdDaytrip`=`rCategory_rCategory`.`DaytripId`) LEFT OUTER JOIN
`Categories` `rCategory` ON
(`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`)  WHERE
((Active = 1) AND (CategoryId IN (:ycp0, :ycp1))) ORDER BY Distance ASC,
Priority DESC, RAND(), CategoryId LIMIT 100

0

#13 User is offline   lgoss007 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 88
  • Joined: 01-October 09

Posted 01 November 2010 - 01:04 PM

Yes you can use input parameters for a view.. but the parameters you want to filter by have to be in the select statement. And since you're trying to leave out those columns and yet query by them you've got yourself in a predicament. Custom SQL is your best option.
0

#14 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 02 November 2010 - 05:05 AM

What about avoiding with?

Write your own join statement.

$criteria->join = "JOIN rCity [...]";



Like this Yii will not select any related field.
0

#15 User is offline   bas_vdl 

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

Posted 02 November 2010 - 10:37 AM

View Postzaccaria, on 02 November 2010 - 05:05 AM, said:

What about avoiding with?

Write your own join statement.

$criteria->join = "JOIN rCity [...]";



Like this Yii will not select any related field.


PERFECT! It's working now. Even better than using the with option.

Because now i only join the DaytripCategory table to filter Daytrips by CategoryId. Using the with option yii is joining the Category table too.

bottom line: when you need a many-to-many filter, just use the CDbCriteria()->join

            $criteria = new CDbCriteria();
            $criteria->distinct = true;
            $criteria->select = array('t.*');
            $criteria->condition = 'Active = 1';
            $criteria->order = 'Distance ASC, Priority DESC, RAND()';
            $criteria->limit = 100;
            $criteria->together = true;

            $criteria->with = array('rCity'=>array('select'=>array('City')));
            $criteria->join = 'LEFT JOIN DaytripCategory ON IdDaytrip = DaytripId';
            $criteria->addInCondition('CategoryId', $_POST['Categories']);

            $daytrips = Daytrip::model()->distance()->findAll($criteria);

This post has been edited by bas_vdl: 02 November 2010 - 02:42 PM

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