Yii2 Ar: Join Dropped?

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?

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

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:

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.

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:

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.

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.

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!

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.