Yii2 Ar: Join Dropped?

[size="2"]Hi,[/size]

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

Why the need to drop SQL joins for related models? Will there be a way to use joins - [size="2"]especially when eager loading?[/size]

Thanks,

Matt

Maybe a performance stuff?

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

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?

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.

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 ?

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.

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?

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

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



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



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 :)

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.

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.

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.

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.

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!

You can use ‘SELECT [[id]] FROM {{posts}}’ and Yii will automaticall change these depending on DB driver used.

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