1
votes

I'm using Symfony 4 with EasyAdmin and I need to make a select query that retrieve all the users, but I want the connected user to be selected as the first result.

I don't know how to proceed at all. I was thinking maybe do multiple select in the same query ? but I don't even know how to do this.

So far I have this :

   $repo = $this->getDoctrine()->getRepository(User::class);
   $authUser = $this->getUser();
   $queryBuilder = $repo->createQueryBuilder('u');
   return $queryBuilder;

// Doctrine createQueryBuilder looks like this

  public function createQueryBuilder($alias, $indexBy = null)
        {
            return $this->_em->createQueryBuilder()->select($alias)->from($this->_entityName, $alias, $indexBy);
        }

EDIT : I imperatively need to return the queryBuilder object, not the result set, that's why it's tricky.

3
Look at this stackoverflow.com/questions/34615027/… Either use CASE or UNION.Vyctorya
Do you have a property in your db that denotes whether someone is connected or not? If so, you could just and an "order by" option to your query builder.ehymel
@ehymel I need to compare the email of the authenticated user to the user that has this email in th dbnaspy971
In that case you won't be able to get your answer through sql/doctrine alone. You'll have to do a custom sort post-retrieval in your controller, perhaps with php's usort.ehymel

3 Answers

1
votes

As the first comment pointed out, you will need to use a UNION, which is not supported in DQL.

Therefore your only option is to use a native query using the Connection which you need to get from the EntityManager.

It would look something like this:

$id = $this->getUser()->getId();
$sql = 'SELECT * FROM `users` WHERE `id` = :id UNION SELECT * FROM `users` WHERE`id` != :id'
$users = $this->getDoctrine()->getConnection()->fetchAll($sql, compact('id'));
1
votes

An approach that will query all users, but gives you an array in the order you describe:

$users = $userRepository->findall();
foreach ($users as $user) {
    $this->getUser() == $user ? $currentUser[] = $user : $otherUsers[] = $user;
}
$myUserList = array_merge($currentUser, $otherUsers);

Two sidenotes: 1: This queries all users and then splits them. I'm not sure if this could be what you want. 2: if no user is currently logged in this code will not work because $currentUser won't be defined. Of course, you can change it so it will also work when no user is logged in.

Edit based on the fact that returning the querybuilder is imperative:

You can use CASE to test and create a property, which you can hide with AS HIDDEN in your query and then order by this hidden property like so:

public function currentUserFirst($userId){
    $qb = $this->createQueryBuilder('u')
        ->select('u, CASE WHEN u.id = :userId THEN 0 ELSE 1 END AS HIDDEN order')
        ->setParameter('userId', $userId)
        ->orderBy('order', 'ASC');
    return $qb;
}
0
votes

You can use setMaxResults:

    $qb->andWhere('q.start <= :start')
        ->setParameter(':start', $value)
        ->setMaxResults(1)
    ;