prchakal
(Paulo)
July 17, 2012, 9:56pm
1
Hi,
I found a bug that when i use "->with(…relations…)" and "findAll($criteria)" with limit and offset on criteria, it ignore my relation and dont make the query with relations.
My method:
public function actionTeste()
{
$criteria = new CDbCriteria();
$criteria->condition = 'options.real_product_id = :real_product_id';
$criteria->params = array(':real_product_id' => 12345);
$criteria->offset = 0;
$criteria->limit = 10;
$list = Product::model()->with('sites', 'options')->findAll($criteria);
}
My relations:
public function relations()
{
return array(
'sites' => array(self::MANY_MANY, 'Site', 'product_site(product_id, site_id)'),
'options' => array(self::HAS_MANY, 'ProductOption', 'product_id'),
);
}
Generated query:
CDbCommand falhou ao executar o comando SQL: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘options.real_product_id’ in ‘where clause’. The SQL statement executed was: SELECT t
.id
AS t0_c0
, t
.category_main
AS t0_c1
, t
.category_id
AS t0_c2
, t
.producttitle
AS t0_c3
, t
.detailed_descrip
AS t0_c4
, t
.or_price
AS t0_c5
, t
.or_price_alt
AS t0_c6
, t
.peso_product
AS t0_c7
, t
.fornecedor_id
AS t0_c8
, t
.external_product_id
AS t0_c9
, t
.audio
AS t0_c10
, t
.cost_price
AS t0_c11
, t
.real_product_id
AS t0_c12
, t
.lote
AS t0_c13
, t
.type_payment_ship
AS t0_c14
, t
.meta_keywords
AS t0_c15
, t
.meta_description
AS t0_c16
FROM product
t
WHERE (options.real_product_id = :real_product_id) LIMIT 10
I use Yii since 1.1 version and never have problems with it and all my customers websites i use it. But ow i have this problem, i think that it is a bug, because is too basic to have anything wrong with code and i think that im not doing it wrong.
Can anyone check it?
mdomba
(Maurizio Domba Cerin)
July 17, 2012, 10:21pm
2
By documentation - http://www.yiiframew …ia#alias-detail
the default table alias for the current model is "t" so instead of "options.real_product_id" you should use "t.real_product_id".
prchakal
(Paulo)
July 18, 2012, 12:22am
3
You dont understand the problem.
I dont want the field from main table. I want from the relation (called options). You see the relation on the query?
If you comment offset/limit form criteria, it works, but without the offset/limit.
mdomba
(Maurizio Domba Cerin)
July 18, 2012, 6:32am
4
Maybe I don’t understand your problem… but I just read the error message you posted… and that error is very clear… it says:
So… did you try what I suggested above?
prchakal
(Paulo)
July 18, 2012, 11:01am
5
This is caused because i want the field from relation, it is clear that the main table and the other table has the same column name. Both tables has the same field name (real_product_id) or in one big database i can have only one column with the same name? So simple to understand.
prchakal
(Paulo)
July 18, 2012, 6:43pm
6
Can anyone help me with this bug?
jacmoe
(Jacob Moen)
July 18, 2012, 7:17pm
7
This is obviously a bug in your own code.
So I am going to move this to Yii 1.1 General Discussion.
I think you need to be more explicit in your ‘actionTeste’:
public function actionTeste()
{
$criteria = new CDbCriteria();
$criteria->with = array('sites', 'options');
$criteria->condition = 'options.real_product_id = :real_product_id';
$criteria->params = array(':real_product_id' => 12345);
$criteria->offset = 0;
$criteria->limit = 10;
$list = Product::model()->findAll($criteria);
}
prchakal
(Paulo)
July 18, 2012, 7:46pm
8
Why this is a bug in my code?
If i use relations and use limit/offset in the same criteria, the relations are not created in query and the field that i want doesnt exists(because the table with alias options doesnt exists - it come from relation) and the bug is in my code?
Where is the LEFT JOIN in the query? Im using WITH WITH WITH, so WHERE IS THE RELATION IN THE GENERATED QUERY? ? ? ? ?
abennouna
(Abennouna)
July 18, 2012, 8:58pm
9
Hi there, I think that you can’t use with(relation) and findAll($criteria) like you did, I believe this is the bug.
Where have you read in the documentation that you can mix both?
IMHO, what fellow members suggest is a (the?) correct way to achieve what you want.
Just try it.
jacmoe
(Jacob Moen)
July 18, 2012, 9:09pm
10
Instead of ‘options’ you should probably use ‘ProductOption’ or ‘productOption’ (depending on your database):
public function actionTeste()
{
$criteria = new CDbCriteria();
$criteria->with = array('ProductOption');
$criteria->condition = 'ProductOption.real_product_id = :real_product_id';
$criteria->params = array(':real_product_id' => 12345);
$criteria->offset = 0;
$criteria->limit = 10;
$list = Product::model()->findAll($criteria);
}
And the amount of CAPS and smileys really doesn’t make me want to help you out more - quite the opposite.
prchakal
(Paulo)
July 18, 2012, 10:24pm
11
@bennouna : You are the first that understand the problem!!!! Thanks man!!!!!
My question is about mix it, when i mix relation with limit/offset, it doesnt generate the left join on query, and when i remove limit/offset from criteria, it create the query with the left join.
So, i think that is a bug, no?
What the problem in using JOIN with LIMIT ?
softark
(Softark)
July 19, 2012, 12:21am
12
It’s not a bug, but so-called ‘BY DESIGN’.
You have to set ‘together’ to true in order to filter by a HAS_MANY relation when you set LIMIT and OFFSET.
http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-performance
It’s a little complicated, but there’s a rule.
‘with’ => eager loading
‘with’ + ‘LIMIT/OFFSET’ => lazy loading
‘with’ + ‘LIMIT/OFFSET’ + ‘together’ => eager loading
The eager loading executes one single lengthy query, joining related tables.
The lazy loading executes one main query for the main table, and then executes N queries for the related table per each retrieved main row.
In order to filter by the relation, you need to do the eager loading.
However, when you want a correct pagination, you need to do the lazy loading.
In your case, you can filter by the relation by specifying ‘together’ to true, but, unfortunately, you will not get the correct count/offset of rows.
prchakal
(Paulo)
July 19, 2012, 12:40am
13
@softark
WOW Man!!! It works
Very helpfull
The solution was:
$criteria = new CDbCriteria();
$criteria->condition = 'options.real_product_id = :real_product_id';
$criteria->params = array(':real_product_id' => 12345);
$criteria->offset = 0;
$criteria->limit = 10;
$criteria->with = array(
'options' => array('together'=>true),
);
$list = Product::model()->with('sites')->findAll($criteria);
Many thanks man, problem solved!!
softark
(Softark)
July 19, 2012, 12:44am
14
Really?
You should check the actual count of rows that get retrieved.
When one of your Product has 2 or more Options, then you’ll get less than 10 rows.
jacmoe
(Jacob Moen)
July 19, 2012, 12:49am
15
And so what if it does have less than 10 rows?
The only thing which matters here is that it will always return a maximum of 10 rows.
softark
(Softark)
July 19, 2012, 1:14am
16
Well, I’m thinking about the scenario when there are more than 10 rows and some of the main row has 2 or more related rows.
Eagerly loaded joined data
Row Product Option
1 1 A
2 1 B
3 2 C
4 3 D
5 4 E
6 4 F
7 4 G
8 5 H
9 6 I
10 7 J
---------------- limit
11 7 K
12 8 L
...
Retrieved AR objects
Row Product Options
1 1 A, B
2 2 C
3 3 D
4 4 E, F, G
5 5 H
6 6 I
7 7 J (missing K)
As you see, there are 3 possible problems.
count of rows may be less than expected
offset other than 0 may not be correct
AR object may not have all the related objects