How to make complicated SQL query in doctrine

Question

I have 2 tables: users and their points.

Users have fields:

• id

• name

Points have fields:

• id

• start_date

• end_date

• count_of_points

• user_id

So some users may have or not have points. Points entries limited by time interval (from start_date to end_date) and have count of points that user have at this interval.

I need display table of users sorted by total sum of points at this moment (timestamp must be between start_date and end_date) and display this sum value later in view. If user have no points this count must be equals 0.

I have something like this:

$qb = $this->getEntityManager()
        ->getRepository('MyBundle:User')
        ->createQueryBuilder('u');
    $qb->select('u, SUM(p.count_of_points) AS HIDDEN sum_points')
        ->leftJoin('u.points', 'p')
        ->orderBy('sum_points', 'DESC'); 
$qb->groupBy('u');
        return $qb->getQuery()
            ->getResult();

But this have no limits by date interval and have no field for sum points that I can use in view from object.

I tried to find how to solve this task and I made something like this in SQL:

SELECT u.*, up.points FROM users AS u LEFT OUTER JOIN
      (SELECT u.*, SUM(p.count_of_points) AS points FROM `users` AS u
      LEFT OUTER JOIN points AS p ON p.user_id = u.id
      WHERE p.start_date <= 1463578691 AND p.end_date >= 1463578691
      ) AS up ON u.id = up.id ORDER BY up.points DESC

But this query give me only users with entries in points table, so I think I must use another JOIN to add users without points. It's complicated query. I have no idea how implements this in doctrine because DQL can't use inner queries with LEFT JOIN.

Maybe there are other ways to solve this task? Maybe my tables schema is wrong and I can do this different way?

Answer 1

EDIT: forgot the date conditions. Corrected answer:

In plain MySQL your query would look like this:

SELECT u.id, u.name, COALESCE(SUM(p.count_of_points),0) AS sum_points 
FROM Users u 
LEFT JOIN Points p ON p.user_id=u.id 
WHERE (p.start_date <= 1463578691 AND p.end_date >= 1463578691) OR p.id IS NULL
GROUP BY u.id
ORDER BY sum_points DESC

The COALESCE function sends back the first not NULL argument, so if a user doesn't have points, the sum would result in NULL, but the COALESCE in 0.

I'm not sure of the translation using the Doctrine query builder, but you could try:

$qb = $this->getEntityManager()->createQueryBuilder();

$qb->select('u')
   ->addSelect('COALESCE(SUM(p.count_of_points),0) AS sum_points')
   ->from('User', 'u')
   ->leftjoin('u.points', 'p')
   ->where('(p.start_date <= ?1 AND p.end_date >= ?1) OR p.id IS NULL')
   ->groupBy('u.id')
   ->orderBy('sum_points','DESC')
   ->setParameter(1, $date_now);