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