学说2 - 实体是以下组织的成员
举例来说：
我有以下架构：
users(id, name)
groups(id,name)
user_groups(user_id, group_id)
我想获取属于某个组的用户。问题是，我知道组 ID，所以我不想加入组表 - user_groups 就足够了。
结果应该是
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
我在doctrine2中的尝试导致了groups表的另一个连接。
我得到的最接近的东西是：
SELECT u FROM models\User u WHERE ?1 MEMBER OF u.groups
但它也会在“WHERE EXISTS(...)”中左连接它，
这可以在没有本机查询的情况下完成吗（使用 DQL/查询生成器）？
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)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
ORM 相对于 ActiveRecord 模式的优点之一是，您不需要为连接表创建新实体。
所以这里当你使用Doctrine时，
user_groups表会在两边进行映射。这意味着您无法直接访问 user_groups 表。除非你使用本机sql，我强烈建议不要这样做。
因此，解决您的情况的最佳方法是在组存储库上使用
findOneBy($group_id)并从中获取用户。这是理论上正确的实施方法。- 编辑 -
对于您的评论：
是的，我同意纯 db POV 更有效。
但是，如果没有本机查询，您将无法做到这一点，因为 DQL、查询生成器和与学说相关的所有内容都使用实体来执行操作，并且由于您在设计上使用 ORM，因此您不会有用于连接表的实体。
否则，唯一的方法是将映射从
使用联接表从用户到组的多对多关系更改为从用户到 user_groups 的一对多关系以及多对一从 user_groups 到 groups。这样您将拥有一个user_groups实体。然后您可以直接使用该实体来获取用户。
尽管它是一个黑客，但它在技术上并不重要。理论上这样使用是正确的。当然，除非连接表还有其他职责。
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_groupstable 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 tabletoa 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 foruser_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.