Yii Framework Forum: Active Record Join - Yii Framework Forum

Jump to content

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

Active Record Join

#1 User is offline   CVP 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 12-March 12

Posted 18 November 2013 - 06:38 AM

I was reading this webpage about upgrading from 1.1 to 2.0 and this cought my eye :
https://github.com/y...rade-from-v1.md

Quote

When loading relational records in an eager way, Yii 2.0 does it differently from 1.1. In particular, in 1.1 a JOIN query would be used to bring both the primary and the relational records; while in 2.0, two SQL statements are executed without using JOIN: the first statement brings back the primary records and the second brings back the relational records by filtering with the primary keys of the primary records.


Wouldn't that be slower ?

So what happens if i want to load 500 primary records and 10 detail records for each primary. Will yii execute
1 query for 500 and then 1 query for each 500 records = 501 queries ?

Can someone elaborate on this ?

C.
0

#2 User is offline   ORey 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,637
  • Joined: 20-April 09
  • Location:Moscow, Russia

Posted 18 November 2013 - 07:02 AM

View PostCVP, on 18 November 2013 - 06:38 AM, said:

Wouldn't that be slower ?


No.

View PostCVP, on 18 November 2013 - 06:38 AM, said:

So what happens if i want to load 500 primary records and 10 detail records for each primary. Will yii execute
1 query for 500 and then 1 query for each 500 records = 501 queries ?

Can someone elaborate on this ?

C.


No.
Btw, some db engines like MySQL restricts max number of values inside IN(...), this should be taken into account.

Also, you still can use a join (for example, if you need filtering by related column).
http://www.yiiframew...2-ar-and-joins/

UPD: my bad, misread. Updated
God is real unless declared as integer
0

#3 User is offline   softark 

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

Posted 18 November 2013 - 08:21 AM

View PostCVP, on 18 November 2013 - 06:38 AM, said:

So what happens if i want to load 500 primary records and 10 detail records for each primary. Will yii execute
1 query for 500 and then 1 query for each 500 records = 501 queries ?

No, Yii will use 2 queries when it's an eager loading: one query for the main 500 records, and another query for the related 500 x 10 records. In the 2nd query Yii will use the PKs retrieved in the 1st query, using IN conditional clause.
0

#4 User is offline   CVP 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 12-March 12

Posted 18 November 2013 - 08:33 AM

View Postsoftark, on 18 November 2013 - 08:21 AM, said:

No, Yii will use 2 queries when it's an eager loading: one query for the main 500 records, and another query for the related 500 x 10 records. In the 2nd query Yii will use the PKs retrieved in the 1st query, using IN conditional clause.


Yes but in the 2nd query i will have 500 filtering parameters. Wouldn't that be too much for MySQL and drop the query ?

C.
0

#5 User is offline   softark 

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

Posted 18 November 2013 - 08:42 AM

It depends on your environment, I think.
They say that "max_allowed_packet" will limit the number of parameters.

I think you'll hit the memory limit of PHP before you hit that of MySQL.
2

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