CODEWITHSUNDEEP
phpmysqldoctrine-orm
galchen
galchen

Reputation: 5290

doctrine2 - entity is member of

for the sake of example:

i have the following schema:

users(id, name)
groups(id,name)
user_groups(user_id, group_id)

i want to fetch users that belong to a certain group. problem is, i know the group id, so i don't want to join the groups table - user_groups is sufficient.

the result should be

SELECT * FROM `users` AS u
LEFT JOIN `user_groups` AS ug ON (ug.`user_id` = u.`id)
WHERE ug.`group_id` = X
// i know the value of X

my attempts in doctrine2 resulted in another join of the groups table.

the closest thing i got is:

SELECT u FROM models\User u WHERE ?1 MEMBER OF u.groups

but it will also LEFT JOIN it inside the "WHERE EXISTS(...)"

can this be done without native query (using DQL/query builder)?

Upvotes: 0

Views: 3644

Answers (1)

Anush Prem
Anush Prem

Reputation: 1481

One of the advantages of ORM over ActiveRecord pattern is that, you don't need to create a new entity for join tables.

So here when you use Doctrine, the user_groups table will be mapped on both sides.

That means you cannot access the user_groups table directly. Unless you use native sql, which i strongly advice against.

So the best way to tackle your situation is to use findOneBy($group_id) on group repo and get the users from it. That the theoretically correct way to implement it.

-- EDIT -- For your comment:

yes, i agree that from the pure db POV its more efficient.

But you won't be able to do it without native query as DQL, query builder and everything related to doctrine uses Entities to perform action and since you are using an ORM by design, you won't have an entity for Join tables.

Else the only way is to change your mapping from a many to many relation from user to groups using join table to a one-to-many from users to user_groups and a many-to-one from user_groups to groups. That way you will have an entity for user_groups.

Then you can directly use that entity to get the users.

Even though its a hack, its not technically & theoretically correct to use like that. Unless of course the join table has other responsibilities also.

Upvotes: 3

Related Questions