[SOLVED] Don't select fields from joined tables

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.

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

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, [b]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[/b]

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

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

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

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

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…

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

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.imageshack.us/i/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.imageshack.us/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



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.

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