How can I fetch distinct values of doctrine2 field with type array

Question

I have FOSUser table and the users have roles with data type array, which are saved in the database in a serialized form, i tried doing a distinct query but it returns each row separately, here's how the query looks like ( this is in the user repository file )

public function getAllRoles()
{
    $roles = $this->createQueryBuilder("u")
        ->select("DISTINCT u.roles");
    return $roles->getQuery()->execute();
}

The returned value though needs more processing, here's how it looked like with sample data i have of two users

Array
(
    [0] => Array
        (
            [roles] => Array
                (
                    [0] => ROLE_ADMIN
                    [1] => ROLE_TEST
                )
        )
    [1] => Array
        (
            [roles] => Array
                (
                    [0] => ROLE_TEST@
                )
        )

    )

I'm wondering if somehow i could get the array with only 3 items

array(ROLE1, ROLE2, ROLE3, ETC)

Answer 1

You can't via SQL, you will need to process the data in PHP with something like the following (quick and dirty example)

public function getAllRoles() 
{
    $roles = $this->createQueryBuilder("u")
        ->select("DISTINCT u.roles");

    $data = $roles->getQuery()->execute();

    $roles = array();
    foreach ($data as $row) {
        $roles = array_merge($roles, $row['roles']);
    }

    return $roles;
}