Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support table aliases with BelongsToMany relationships. #26

Closed
budhajeewa opened this issue Apr 6, 2019 · 3 comments

Comments

Projects
None yet
2 participants
@budhajeewa
Copy link

commented Apr 6, 2019

Assume the following entities:

  • User
  • UserGroup
  • Organization

Assume the following relationships:

  • User belongs to many Organizations.
  • User belongs to many UserGroups.

Given above, we need a relationship User::organizationsOfUsersWithSharedUserGroups(), that would return all the Organizations that belongs to any of the Users that shares UserGroups with the calling User object.

PHP has to:

  1. Start with the current User object,
  2. get its UserGroups,
  3. get Users of those UserGroups (Thus coming back to the User class, through the same pivot table.),
  4. and get Organizations of those Users.

Following relationship definition accomplishes what I am after, with one caveat:

public function organizationsOfUsersWithSharedUserGroups() {
	return $this->hasManyDeep(
		Organization::class,
		[
			'user_user_group',
			UserGroup::class,
			'user_user_group2',
			User::class,
			'organization_user',
		]
	);
}

The caveat being user_user_group2 being a MySQL view defined as follows:

CREATE
OR REPLACE
VIEW `db`.`user_user_group2` AS select
    `db`.`user_user_group`.`id` AS `id`,
    `db`.`user_user_group`.`user_id` AS `user_id`,
    `db`.`user_user_group`.`user_group_id` AS `user_group_id`
from
    `db`.`user_user_group`

Table user_user_group and view user_user_group2 are identical.

If we replace user_user_group2 with user_user_group in User::organizationsOfUsersWithSharedUserGroups()'s definition, we get the following error:

SQLSTATE[42000]: Syntax error or access violation:
1066 Not unique table/alias: 'user_user_group'

(SQL:

select
	`organizations`.*,
	`user_user_group`.`user_id` as `laravel_through_key`
from
	`organizations`
	inner join `account_user`
		on `account_user`.`account_id` = `organizations`.`id`
	inner join `users`
		on `users`.`id` = `account_user`.`user_id`
	inner join `user_user_group`
		on `user_user_group`.`user_id` = `users`.`id`
	inner join `user_groups`
		on `user_groups`.`id` = `user_user_group`.`user_group_id`
	inner join `user_user_group`
		on `user_user_group`.`user_group_id` = `user_groups`.`id`
where
	`users`.`deleted_at` is null
	and `user_groups`.`deleted_at` is null
	and `user_user_group`.`user_id` = 1
	and `organizations`.`deleted_at` is null
)

I tried to specify an as in User::organizationsOfUsersWithSharedUserGroups()'s definition as follows, but it did not work.

public function organizationsOfUsersWithSharedUserGroups() {
	return $this->hasManyDeep(
		Organization::class,
		[
			'user_user_group',
			UserGroup::class,
			'user_user_group as alias',
			User::class,
			'organization_user',
		]
	);
}

Thus, I would like to propose supporting table aliases with BelongsToMany relationships.

@budhajeewa

This comment has been minimized.

Copy link
Author

commented Apr 6, 2019

I managed to find a solution by browsing through the old closed issues of this repo.

See #15 (comment) .

Following it's instructions, I created a pivot class for the user_user_group pivot table, classes can be aliased without an issue!

Here is my final relationship method:

public function organizationsOfUsersWithSharedUserGroups() {
	return $this->hasManyDeep(
		Organization::class,
		[
			UsersAndUserGroupsRelationship::class,
			UserGroup::class,
			UsersAndUserGroupsRelationship::class . ' as alias',
			User::class,
			'organization_user',
		]
	);
}

Here's the UsersAndUserGroupsRelationship class:

use Illuminate\Database\Eloquent\Relations\Pivot;
use Staudenmeir\EloquentHasManyDeep\HasTableAlias;

class UsersAndUserGroupsRelationship extends Pivot
{
    use HasTableAlias;
    
    protected $table = 'user_user_group';
}

I'll leave the issue open, in case @staudenmeir might want to update the README.md or do something else with this issue.

@staudenmeir

This comment has been minimized.

Copy link
Owner

commented Apr 6, 2019

Yes, aliasing a pivot table requires a custom model. I'll add an example to the README.

@staudenmeir staudenmeir closed this Apr 6, 2019

@budhajeewa

This comment has been minimized.

Copy link
Author

commented Apr 6, 2019

@staudenmeir : Thank you! 👍 😃

budhajeewa referenced this issue Apr 7, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.