Yii2 Advanced Relations

Yii 2.0 General Discussions
1

Hello and thank you in advance for your help!

My issue is that I have multiple Many to Many relations with information that is vital to that relations within the actual linking, pivot table (yii2 documentation).

this is an example relation.

Table 1:

tbl_users

id

username

Table 2:

tbl_series

id

name

url

Table 3:

tbl_episodes

id

series_id

name

synopsis

Table 4:

tbl_users_episodes

user_id

episode_id

timestamp

complete

So I have the Series -> Episodes relation working, and I have a the same type of Users -> Episodes relation working, but the issue is that I currently can not get that relations timestamp and complete information from the tbl_users_episodes without doing the previous relation and then do a SQL Query to hard load that database row.

While I believe I could get this working with the method of just straight SQL above I was wondering if there was any better ways to accomplish this type of relations? with the minimal amount of SQL/Interaction as possible? as the system is planned for 1000’s of users with 1000’s of series and 1000’s of episodes each.

Thanks again in advance, Also I am loving Yii2 keep up the good work!!

2

Multiple things to look at - and it depends on your application design.

For just QUERY and SEARCH (esp since you say large number of records)… one method to do this is by creating Database Views (not sure many use it). You can create a yii AR model for a DB View as well. The primary use cases for using DB Views are:

  1. When you have many joins and want to eagerly load data from all related tables through one single database fetch.

  2. Most optimal for performance - since you do not need PHP/Yii to parse relations or other stuff. You can also optimize your view query based on your database and index design.

  3. When you need to scan database only for query and search. For insert/update/delete you may still individually hit the base tables.

However, there are other things to look at (when you consider the CRUD)… but that’s just an initial thought.

3

After doing some research into this subject I guess I will simplify my question a bit.

Is there any way to duplicate this feature in Yii2?

in Model:




	return $this->belongsToMany('Role')->withPivot('foo', 'bar');

in Controller or wherever:




	$user = User::find(1);

	foreach ($user->roles as $role)

	{

		echo $role->pivot->created_at;

		echo $role->pivot->foo;

	}

etc

This is taken from Laravel 4 eloquent Pivot Table documentation

[size="2"]laravel {dot} com/docs/eloquent#working-with-pivot-tables[/size]