有没有办法在 Doctrine2 中查询数组字段是否包含某个值?
从 Symfony2 + Doctrine 开始.
Starting out with Symfony2 + Doctrine.
我有一个包含用户对象 (fos_user) 的表，我的架构包含一个 'array' 类型的 roles 列.
I have a table with User objects (fos_user), for which my schema contains a roles column of an 'array' type.
Doctrine 通过将它们从 php 'array' 序列化为 'longtext'(在 mysql 的情况下)来保存这种类型的字段.
Doctrine saves fields of this type by serializing them from php 'array' to 'longtext' (in mysql's case).
假设我将以下用户保存到数据库中:
So let's say I have the following users saved into DB:
User1: array(ROLE_ADMIN, ROLE_CUSTOM1)
User2: array(ROLE_ADMIN, ROLE_CUSTOM2)
User3: array(ROLE_CUSTOM2)
现在在我的控制器中，我想选择所有设置了 ROLE_ADMIN 的用户.有没有办法编写一个直接返回我User1和User2的DQL查询?或者我是否需要获取所有用户以让 Doctrine 反序列化角色列，然后为每个用户执行 in_array('ROLE_ADMIN', $user->getRoles()) ?
Now in my controller I want to select all users with ROLE_ADMIN set. Is there a way to write a DQL query which would directly return me User1 and User2? Or do I need to fetch all users to have Doctrine to unserialize roles column and then for each of them do in_array('ROLE_ADMIN', $user->getRoles())?
我搜索了 手册的 DQL 部分，但到目前为止还没有找到与我的需求类似的东西...
I have searched the DQL part of the manual, but so far did not find anything similar to my needs...
UPD: 发现了一个 问题 关于包含工作查询代码的同一件事
UPD: Found a question about the same thing which contains a working query code
推荐答案
您可以简单地使用本手册中描述的 LIKE 语句(是的，即使是在序列化对象上).
You can simply use LIKE statement described in this manual (yes, even on serialized objects).
我建议您为角色创建一个实体类，并通过ManyToMany 关联将其与用户实体连接.
I'd suggest you to create an entity class for roles and join it with the user entity by ManyToMany association.
相关文章