理论上如何进行复杂的SQL查询

Question

我有两个表：用户和他们的点。

用户有字段：

• id

• 名称

点有字段：

• id

• 开始日期

• 结束日期

• 计数点

• user_id

所以有些用户可能有积分，也可能没有积分。受时间间隔限制的积分条目(从start_date到end_date)，并具有用户在此间隔拥有的积分计数。

我需要显示此时按积分总和排序的用户表(时间戳必须在start_date和end_date之间)，并在稍后的视图中显示此总和值。如果用户没有积分，则此计数必须等于0。

我有这样的东西：

$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();

但这并没有日期间隔的限制，也并没有可以在对象视图中使用的和点字段。

我试图找到如何解决这个任务，我在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

但这个查询只给了我在积分表中有条目的用户，所以我认为我必须使用另一个JOIN来添加没有积分的用户。这是一个复杂的问题。我不知道如何在原则中实现这一点，因为DQL不能使用带有LEFT JOIN的内部查询。

也许还有其他方法可以解决这个问题？也许我的表模式是错误的，我可以用不同的方式来做？

Answer 1

编辑：忘记了日期条件。更正答案：

在普通MySQL中，您的查询将如下所示：

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

COALESCE函数返回第一个非NULL参数，因此，如果用户没有点数，则总和将导致NULL，但COALESCE0。

我不确定使用Doctrine查询生成器的翻译，但您可以尝试：

$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);