Yii Framework Forum: Yii2 Ar: Join Dropped? - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Yii2 Ar: Join Dropped?

#1 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 19 May 2013 - 09:36 AM

Hi,

I was reading through the roadmap for the Yii2 and noticed this paragraph.

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.


Why the need to drop SQL joins for related models? Will there be a way to use joins - especially when eager loading?

Thanks,

Matt
0

#2 User is offline   sensorario 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,986
  • Joined: 07-September 10
  • Location:Cesena (Italy)

Posted 19 May 2013 - 10:29 AM

Maybe a performance stuff?
0

#3 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,724
  • Joined: 17-January 09
  • Location:Russia

Posted 19 May 2013 - 12:35 PM

No significant performance hit, easier to cache, much less code to implement, very flexible (i.e. will fit noSQL as well).
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#4 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 19 May 2013 - 03:25 PM

Hmm, I haven't looked into the code, what how do you find this? (Using the Blog db design)

All Posts, with Comments that have been commented less then one week ago?
0

#5 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,895
  • Joined: 04-October 08
  • Location:DC, USA

Posted 19 May 2013 - 06:18 PM

See the examples given in the documentation:


$customer = Customer::find(1);
// lazy loading: SELECT * FROM tbl_order WHERE customer_id=1 AND subtotal>100
$orders = $customer->getOrders()->where('subtotal>100')->all();

// eager loading: SELECT * FROM tbl_customer LIMIT 10
                  SELECT * FROM tbl_order WHERE customer_id IN (1,2,...) AND subtotal>100
$customers = Customer::find()->limit(100)->with(array(
	'orders' => function($query) {
		$query->andWhere('subtotal>100');
	},
))->all();


Note that you don't need to use table alias at all, unlike Yii 1.
1

#6 User is offline   Fredi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 11
  • Joined: 17-January 12

Posted 19 May 2013 - 10:41 PM

View Postqiang, on 19 May 2013 - 06:18 PM, said:


// eager loading: SELECT * FROM tbl_customer LIMIT 10
                  SELECT * FROM tbl_order WHERE customer_id IN (1,2,...) AND subtotal>100
$customers = Customer::find()->limit(100)->with(array(
	'orders' => function($query) {
		$query->andWhere('subtotal>100');
	},
))->all();



Looking at queries it looks like you will still get all customers, not only those with orders 'subtotal>100'. Is there a way around this ?
0

#7 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,627
  • Joined: 04-March 10
  • Location:UK

Posted 20 May 2013 - 06:14 AM

How would you handle querying based on the data in more than one table? For example, if I need to bring back all of the records which match a condition in a joined table. It's something I tend to have to do in more complicated grid views.
0

#8 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 122
  • Joined: 09-January 11

Posted 20 May 2013 - 01:13 PM

If the conditions span multiple tables and the order clause spans multiple tables and you need to paginate the results, isn't a jojn the only way?
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#9 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,895
  • Joined: 04-October 08
  • Location:DC, USA

Posted 20 May 2013 - 04:02 PM

If your query involves filtering on multiple tables, you have to use join explicitly. The relational query is mainly used to bring back relational records. It's not designed to be used for joint filtering purpose in Yii 2. So you have to use query like this:


$customers = Customer::find()->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id')
	->with('orders')->where('subtotal>100')->all();


The main good thing about the new AR is that you no longer need to deal with those tricky table aliases (unless you are doing joint query like above).
0

#10 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 20 May 2013 - 04:11 PM

View Postqiang, on 20 May 2013 - 04:02 PM, said:

It's not designed to be used for joint filtering purpose in Yii 2.


Hmm, isn't there a better solution?

This code..
$customers = Customer::find()->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id')
	->with('orders')->where('subtotal>100')->all();

feels like it should be..
$customers = Customer::find()->with('orders')->where('subtotal>100')->all();


This is a "feeling" though, of how I read the code in normal language. My feeling says I want to A) Find Customers; B) with related Orders; C) that only have a subtotal above hundred; D) All of those.


However.. I suppose how it actually works is like:
$customers = Customer::find()->all()->thenGetRelated('orders')->where('subtotal>100');

0

#11 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,895
  • Joined: 04-October 08
  • Location:DC, USA

Posted 20 May 2013 - 10:20 PM

The order of with() and where() doesn't matter. You can rearrange them to make them more readable. Just make sure the all() call be the last one.

The key difference is the explicit innerJoin() call in Yii 2.
In Yii 1.1, the goal B and C are mixed together via with() call; while in Yii 2, they are separated. Of course in Yii 2, you can easily define a scope to make it look better:

$customers = Customer::find()->orderSubtotalGreaterThan(100)->with('orders')->all();

0

#12 User is offline   Psih 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 114
  • Joined: 30-June 10

Posted 21 May 2013 - 04:53 AM

Although aliasing in 1.1 is not perfect and has some flaws (actually it's easy to get around them - stop writing aliases by hand and use "$this->getTableAlias(false, false)" when writing the model code, scopes and other stuff - different queries with different alias names referencing the same table start to work like a charm), it's kind'a brilliant when you get to know it well.

I think you should really consider implementing the relational stuff, just make it explict only without the nessesity to write a innerJoin('bla bla bla bla bla'). With the new AR design I feel that enforcing a proper alias should not be too big of a problem. Just make it a strict rule to use alias symbols when writing WHERE, JOIN, HAVING and other statements like from example above "with('orders')->addWhere('@orders.subtotal>100')". The easy way would be using the AR as it is in examples. Remove the magic of guessing the alias - make us write them explictly. If I wana reference the related table, I have to write it like "@relation.field" and not "@rel.field" - this way the relation I want to reference is explict.

Us, who write complex stuff, understand that there is a price to pay - we can manage writing proper aliasing templates in our queries. I do it now anyway, because otherwise it just does not work.

The only real caveat I see is the fact the there is no more "relations" method with the relation names defined as keys, so this could be a problem.
NoSQL support is great and stuff, but it is not a reason to drop near perfect SQL relational stuff from 1.1, it just need some re-thinking and re-designing.

Just some thoughts on the subject, actuall stuff needs quite a bit of designing. My head is allready near the explosion point of trying to think it though, so I rest my case :)
0

#13 User is offline   softark 

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

Posted 21 May 2013 - 05:30 AM

I like the idea of the separation of the "searching" of the main model and the "fetching" of the related model(s). It will make the searching and fetching of a model with HAS_MANY (or MANY_MANY) relations quite cleaner and simpler than in Yii 1.1.

"together" has been the trickiest part in Yii 1.1 AR ...

You have to set "together" to true when you want to filter on a HAS_MANY related column, but you have to set "together" to false when you want the proper offset and limit on the main model. It was a built-in dilemma that results from the relational AR design that searches and fetches the relational data all together.
1

#14 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,627
  • Joined: 04-March 10
  • Location:UK

Posted 21 May 2013 - 06:01 AM

Slightly off topic (and you may already know this), but "together" can be used successfully with the correct offset and limit. It just requires that any HAS_MANY or MANY_MANY relations are grouped on the primary key of the main table. There's a useful guide here.

While it would be nice to be able to request joins be done together in Yii 2, I can understand the drive for cleaner and simpler core code, so I'm happy to settle for the more verbose syntax when performing queries on joined tables.
1

#15 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 122
  • Joined: 09-January 11

Posted 21 May 2013 - 06:56 AM

View Postqiang, on 20 May 2013 - 04:02 PM, said:

If your query involves filtering on multiple tables, you have to use join explicitly. The relational query is mainly used to bring back relational records. It's not designed to be used for joint filtering purpose in Yii 2. So you have to use query like this:


$customers = Customer::find()->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id')
	->with('orders')->where('subtotal>100')->all();


The main good thing about the new AR is that you no longer need to deal with those tricky table aliases (unless you are doing joint query like above).


I might prefer this, maybe because I know how to express myself in SQL and joins.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#16 User is offline   Psih 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 114
  • Joined: 30-June 10

Posted 21 May 2013 - 07:22 AM

View Postfsb, on 21 May 2013 - 06:56 AM, said:

I might prefer this, maybe because I know how to express myself in SQL and joins.

Writing joins by hand may have some unexpected consiquences like if a table is named as a SQL keyword in mysql, you have to wrap it up in `name` for MySQL, for PostgreSQL it probably is different and so on. As far as I'm aware, 1.1 does that automatically based on the driver used.
0

#17 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 122
  • Joined: 09-January 11

Posted 21 May 2013 - 07:24 AM

View PostKeith, on 21 May 2013 - 06:01 AM, said:

There's a useful guide here.


That looks familiar!

yJeroen: I didn't know that you had finished it. This looks very useful. I had put my repo on ice because I lost contact with you. I'll use this instead of referring to my incomplete

It seems to me the default approach\ Yii2 takes is what we did in the KeenActiveDataProvider: fetch the root model's records with any 1:1 related records, extract keys from those results, and use the keys to fetch batches of related records.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#18 User is offline   Psih 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 114
  • Joined: 30-June 10

Posted 21 May 2013 - 09:11 AM

View Postfsb, on 21 May 2013 - 07:24 AM, said:

It seems to me the default approach\ Yii2 takes is what we did in the KeenActiveDataProvider: fetch the root model's records with any 1:1 related records, extract keys from those results, and use the keys to fetch batches of related records.

And that's good. Default behaviour should be like this. It's just when you need to go trully relational, I don't really wana take a step back to stone age and start to write half the query by hand. Recently I started to really appriciate the defaultScope, custom scopes, with() + together() (with the sidenote that I'm using a proper way to get aliases, that takes some effort). When you plan accordingly and take time to do right your models - it's a joy to write the code. Everything just falls in line just in a few lines of code. And caching is just... WOW!
0

#19 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,724
  • Joined: 17-January 09
  • Location:Russia

Posted 21 May 2013 - 12:53 PM

Quote

Writing joins by hand may have some unexpected consiquences like if a table is named as a SQL keyword in mysql, you have to wrap it up in `name` for MySQL, for PostgreSQL it probably is different and so on. As far as I'm aware, 1.1 does that automatically based on the driver used.

You can use 'SELECT [[id]] FROM {{posts}}' and Yii will automaticall change these depending on DB driver used.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
1

#20 User is offline   Fredi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 11
  • Joined: 17-January 12

Posted 21 May 2013 - 02:12 PM

I tried to run code posted by qiang on test database (changed subtotal to total to match schema).
$customers = Customer::find()->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id')
    ->with('orders')->where('total>100')->all();


It tries to assign all result columns to Customer object and produces error:

Setting unknown property: app\models\Customer::customer_id

0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • 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