DQL原则查询翻译

Question

我有我的分数表，我有多个分数为1个用户。我要做的是为每个用户选择所有最高分。

我正在尝试在Doctrine DQL中执行以下操作:

        SELECT * FROM scores s1
        LEFT OUTER JOIN scores s2 ON
          s1.user_id = s2.user_id
          AND ((s1.score < s2.score) OR (s1.score = s2.score AND s1.date_added < s2.date_added))
        WHERE s2.score IS NULL
        ORDER BY s1.score DESC
        LIMIT 10

我的当前状态是:

    $rowQuery = $this->getEntityManager()->createQuery('
        SELECT s1 FROM DigitalApplicationBundleEntityChallengeScore s1
            LEFT OUTER JOIN DigitalApplicationBundleEntityChallengeScore s2
        ON (
            s1.user = s2.user
            AND
            (s1.score < s2.score OR (s1.score = s2.score AND s1.date_added < s2.date_added))
        )
        WHERE s2.score IS NULL
        AND s1.date_added BETWEEN :monday AND :sunday
        ORDER BY s1.score DESC
    ');

    $rowQuery->setParameter('monday', $startDate->format('Y-m-d'))
             ->setParameter('sunday', $endDate->format('Y-m-d'));
    $rowQuery->setMaxResults($limit);
    return $rowQuery->getResult();

，我得到以下错误:

[Syntax Error] line 0, col 188: Error: Expected Literal, got '�'

我做错了什么?

Answer 1

尝试在命名空间中加入两个反斜杠。如:

$rowQuery = $this->getEntityManager()->createQuery('
    SELECT s1 FROM \Digital\ApplicationBundle\Entity\ChallengeScore s1
    LEFT OUTER JOIN \Digital\ApplicationBundle\Entity\ChallengeScore s2 ...

如果这是不工作，尝试做查询的小部分，找出问题在哪里。

Answer 2

这应该在DQL中工作，ON部分按原则处理，如果你已经为你的实体定义了任何映射，如果没有，仍然想用一个共同的属性连接两个实体，你可以使用WITH子句

SELECT s1 
FROM DigitalApplicationBundleEntityChallengeScore s1
    LEFT OUTER JOIN DigitalApplicationBundleEntityChallengeScore s2
    WITH s1.user = s2.user
    AND CASE WHEN s1.score = s2.score
            THEN s1.date_added < s2.date_added
            ELSE s1.score < s2.score
    END
WHERE s2.score IS NULL
AND s1.date_added BETWEEN :monday AND :sunday
ORDER BY s1.score DESC

或

SELECT s1 
FROM DigitalApplicationBundleEntityChallengeScore s1
    LEFT OUTER JOIN DigitalApplicationBundleEntityChallengeScore s2
    WITH s1.user = s2.user
    AND (
        s1.score < s2.score OR (s1.score = s2.score AND s1.date_added < s2.date_added)
    )
WHERE s2.score IS NULL
AND s1.date_added BETWEEN :monday AND :sunday
ORDER BY s1.score DESC