ActiveRecord - relations - JOIN instead of WHERE id IN

Yii 2.0 General Discussions
1

Hey folks,

I have PHP experience for several years, but I just dipped into Yii2 last week. And I regret that I haven’t done it earlier :D. This is so great.

My question refers to the relations in a ActiveRecord Model.

Let’s say I have two tables:

[indent]member[/indent]


id | id_department | name

[indent]department[/indent]


id | name

One member can only have one department. And a department can have multiple member.

In a classic SQL Query I just would do the following:

[sql]SELECT

m.id, m.name AS member, d.name AS department

FROM

member m

LEFT JOIN

department d ON d.id = m.id_department[/sql]

Now I want to integrate the department name in my member model.

I read the relational part of the ActiveRecord Guide and there is this hasMany() and hasOne() thing.

Perhaps i got something wrong, but when I do that I won’t get the JOIN-query I posted above.

As far as i understand I get something like that:

[sql]SELECT * FROM member;

SELECT * FROM department WHERE id IN (…)[/sql]

But that is not what I want to achieve. I would like to have that classic JOIN query since I only need the department name.

At the moment I have the following workaround:


<?php

$query = Member::find()

	->from('member m')

	->select([

		'id' => 'm.id',

		'member' => 'm.name',

		'department' => 'd.name',

	])

	->leftJoin('department d', 'd.id = m.id_department');

?>

I feel not right with that. That is against the model architecture, isn’t it?

When the member table grows and has more fields, I have to manually add all of them in the select method.

And when there are more connected tables, I need to add the left joins as well.

I hope my situation became clear. Is there any best practice for that? I have that kind of cases very often.

Cheers,

Salbei

2

I’ve just noticed that I missed the joinWith() topic when reading the docs :rolleyes:

I just tried it out, but the second SELECT * WHERE id IN (…) query is still listed in the debugger.

Is there any way to avoid this second query and get all infos out of the base query? Since we join the table, we could select something like “department.*”, but unfortunately this doesn’t prevent the second query.

I am a bit concerned about loading mass queries when having a lot of big tables and connected fields.

3

Welcome to Yii 2 and to this forum.

There’s a lot to say about the subject, but I hope you’ll find the following wiki article a good starting point.

http://www.yiiframework.com/wiki/834/relational-query-lazy-loadnig-and-eager-loading-in-yii-2-0

4

You may get worried about the performance problem arising from this strategy.

But, the fact is, I believe, it works quite well most of the time.

As long as you handle db operations with ActiveRecord and/or ActiveDataProvider, this strategy is fast enough.

It’s true that sometimes you may want to write more optimized SQLs for the sake of speed and low memory footprint. But it is not because of this strategy, but because of the nature of ActiveRecord itself. You can give up the easiness and convenience of ActiveRecord object and go down to the lower level of raw SQLs and plain array of result set to get the maximized performance. It’s a trade off.

Fortunately, the transition from ActiveRecord to raw SQL in Yii 2 is quite smooth. You can start with ActiveRecord and modify it to raw SQL later without much rewrite of the code.

And, once again, Yii 2’s ActiveRecord is fast enough most of the time. Don’t try to optimize the db operation with raw SQLs too early in the development process, until you get real performance issue.