Yii Framework Forum: Bug using relation and pagination in a FindAll from ActiveRecord - Yii Framework Forum

Jump to content

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

Bug using relation and pagination in a FindAll from ActiveRecord Rate Topic: -----

#1 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 17 July 2012 - 04:56 PM

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:

Quote

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:

Quote

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:

Quote

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?
0

#2 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,545
  • Joined: 12-October 09
  • Location:Croatia

Posted 17 July 2012 - 05:21 PM

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".
Find more about me.... btw. Do you know your WAN IP?
0

#3 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 17 July 2012 - 07:22 PM

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.
0

#4 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,545
  • Joined: 12-October 09
  • Location:Croatia

Posted 18 July 2012 - 01:32 AM

Maybe I don't understand your problem... but I just read the error message you posted... and that error is very clear... it says:

Quote

Unknown column 'options.real_product_id' in 'where clause'.


So... did you try what I suggested above?
Find more about me.... btw. Do you know your WAN IP?
0

#5 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 18 July 2012 - 06:01 AM

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.
0

#6 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 18 July 2012 - 01:43 PM

Can anyone help me with this bug?
0

#7 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 3,697
  • Joined: 10-October 10
  • Location:Denmark

Posted 18 July 2012 - 02:17 PM

View Postprchakal, on 18 July 2012 - 01:43 PM, said:

Can anyone help me with this bug?

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

"Less noise - more signal"
0

#8 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 18 July 2012 - 02:46 PM

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?????????????????
0

#9 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,180
  • Joined: 05-January 12
  • Location:Morocco

Posted 18 July 2012 - 03:58 PM

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.
0

#10 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 3,697
  • Joined: 10-October 10
  • Location:Denmark

Posted 18 July 2012 - 04:09 PM

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. :P
"Less noise - more signal"
0

#11 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 18 July 2012 - 05:24 PM

@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 ?
0

#12 User is online   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,425
  • Joined: 16-February 11
  • Location:Japan

Posted 18 July 2012 - 07:21 PM

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.yiiframew...ery-performance

It's a little complicated, but there's a rule.

1) 'with' => eager loading
2) 'with' + 'LIMIT/OFFSET' => lazy loading
3) '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.
1

#13 User is offline   prchakal 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 20-January 10

Posted 18 July 2012 - 07:40 PM

@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!!
0

#14 User is online   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,425
  • Joined: 16-February 11
  • Location:Japan

Posted 18 July 2012 - 07:44 PM

View Postprchakal, on 18 July 2012 - 07:40 PM, said:

Many thanks man, problem solved!!

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.
0

#15 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 3,697
  • Joined: 10-October 10
  • Location:Denmark

Posted 18 July 2012 - 07:49 PM

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.
"Less noise - more signal"
0

#16 User is online   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,425
  • Joined: 16-February 11
  • Location:Japan

Posted 18 July 2012 - 08:14 PM

View Postjacmoe, on 18 July 2012 - 07:49 PM, said:

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.


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.
1) count of rows may be less than expected
2) offset other than 0 may not be correct
3) AR object may not have all the related objects
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