CODEWITHSUNDEEP
phpsymfonyormdoctrine-orm
JohnnyBeGood
JohnnyBeGood

Reputation: 39

Symfony - using orWhere() in doctrine query builder

I want to return query result for the words I am searching in a message but just where userOne or userTwo is currently logged in user.

I think I have trouble with defining my query builder right because when testing otherwise logged in user is returned correctly.

I am trying with orWhere() clause but it always returns all results of that word not just for logged in user.

My code:

 public function search($word, $user)
{
    return $this->getMessageRepository()
        ->createQueryBuilder('a')
        ->where('a.message LIKE :message')
        ->andWhere("a.toUser = $user OR fromUser = $user")
        ->setParameter('message', '%' . $word. '%')
        ->setParameter('toUser', $user)
        ->setParameter('fromUser', $user)
        ->getQuery()
        ->getResult();
}

Upvotes: 0

Views: 1529

Answers (2)

Йося Гисем
Йося Гисем

Reputation: 71

You need to specify all fields with a prefix a because you create this prefix in createQueryBuilder('a');

If you have more 1 params use setParameters.

And you can OR write in QueryBuilder type -> $builder->expr()->orX.

Your query example:

public function search($word, $user)
    {
        $builder = $this->createQueryBuilder('a');
    
        return $builder
            ->where('a.message LIKE :message')
            ->andWhere($builder->expr()->orX(
                $builder->expr()->eq('a.toUser', ':user'),
                $builder->expr()->eq('a.fromUser', ':user'),
            ))
            ->setParameters([
                'message' => '%' . $word . '%',
                'user' => $user,
            ])
            ->getQuery()
            ->getResult();
    }

Upvotes: 1

xtothea
xtothea

Reputation: 169

The logic of the where statement should work as expected. But it seems like you are using incorrect parameter binding.

toUser and fromUser are columns and therefore no need to bind them.

$user is the target user that we want to filter on, thus it should be bound to the query.

An example:

{
    return $this->getMessageRepository()
        ->createQueryBuilder('a')
        ->where('a.message LIKE :message')
        ->andWhere("a.toUser = :user OR a.fromUser = :user")
        ->setParameter('message', '%' . $word. '%')
        ->setParameter('user', $user)
        ->getQuery()
        ->getResult();
}

Upvotes: 3

Related Questions