CODEWITHSUNDEEP
mysqlsymfonydoctrineleft-join
CaptainStiggz
CaptainStiggz

Reputation: 1907

Mixing LEFT JOIN results MySQL Doctrine

I have a doctrine based application where I want to select rows from two separate tables and order them by creation date. I'm (sort of) accomplishing it this way right now:

$query = $em->createQueryBuilder()
  ->from('Embed', 'e')
  ->select("e")
  ->leftJoin("User", "u", "WITH", "e.uid=u.id")
  ->leftJoin("Product", "p", "WITH", "e.pid=p.id")
  ->where("u.image > 0 OR p.image > 0")
  ->addOrderBy("u.timeCreated + p.timeCreated", "DESC")
  ->setMaxResults(28)
  ->getQuery();

I would like for product and user data to be mixed, ordered by creation date, but the ordering is incorrect. I've also tried using two addOrderBy statements, which orders users and products correctly but puts all the users first in the results, rather than mixing them.

EDIT: Sir Rufo had the correct approach - here's the working code:

$query = $em->createQueryBuilder()
  ->from('Embed', 'e')
  ->select("e")
  ->leftJoin("User", "u", "WITH", "e.uid=u.id")
  ->leftJoin("Product", "p", "WITH", "e.pid=p.id")
  ->addSelect('COALESCE( u.timeCreated, p.timeCreated ) as timeCreated')
  ->where("u.image > 0 OR p.image > 0")
  ->orderBy("timeCreated", "DESC")
  ->setMaxResults(28)
  ->getQuery();

Upvotes: 0

Views: 11273

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19106

You should try this

$query = $em->createQueryBuilder()
  ->from('Embed', 'e')
  ->select("e")
  ->leftJoin("User", "u", "WITH", "e.uid=u.id")
  ->leftJoin("Product", "p", "WITH", "e.pid=p.id")
  ->where("u.image > 0 OR p.image > 0")
  ->addOrderBy("COALESCE( u.timeCreated, p.timeCreated )", "DESC")
  ->setMaxResults(28)
  ->getQuery();

Upvotes: 3

Related Questions