CODEWITHSUNDEEP
mysqlsqlsymfonydoctrinequery-builder
ahmadox
ahmadox

Reputation: 33

how to convert this simple sql query to doctrine querybuilder

I'm using php doctrine, and i can't convert this sql query to querybuilder . i have problems with "group by" any help please ?

SELECT * FROM message WHERE sender_id='2' OR receiver_id = '2' Group By (if(sender_id > receiver_id, sender_id, receiver_id)) , (if(sender_id <= receiver_id, sender_id,receiver_id)) Order BY createdAt DESC

this is what i did but i need condition on groupby

  $queryBuilder = $this->createQueryBuilder('m');
  $queryBuilder->where('m.sender = :user or m.receiver =:user')
  ->groupBy('m.sender','m.receiver')
  ->orderBy('m.createdAt', 'Desc')
  ->setParameter('user', $user);

Upvotes: 0

Views: 303

Answers (1)

user4545769
user4545769

Reputation:

This should probably work:

$qb = $this->createQueryBuilder('m');
$qb->where($qb->expr()->orX(
    $qb->expr()->eq('m.sender', ':user'),
    $qb->expr()->eq('m.receiver', ':user')
))->groupBy('m.sender')
->addGroupBy('m.receiver')
->orderBy('m.createdAt', 'DESC')
->setParameter('user', $user);

So apart from factoring out your where statement into relevant expr() statements (documentation) the only other change is splitting your groupBy into groupBy and then addGroupBy. You can do the same if you ever need to order by more than one things (orderBy and addOrderBy).

Upvotes: 0

Related Questions