有没有一种方法可以查询数组字段在Doctrine2中是否包含某个值? [英] Is there a way to query if array field contains a certain value in Doctrine2?

问题描述

从Symfony2 +主义开始.

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())?

我搜索了

I have searched the DQL part of the manual, but so far did not find anything similar to my needs...

UPD :找到了推荐答案

您可以简单地使用本手册中所述的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.

这篇关于有没有一种方法可以查询数组字段在Doctrine2中是否包含某个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！