DQL学说查询翻译 [英] DQL Doctrine query translation

问题描述

我有自己的分数表，其中有1个用户的多个分数。我想做的是为每个用户选择所有最高的分数。

I have my scores table where I have multiple scores for 1 user. What I am trying to do is to select all highest scores for each user.

我正在尝试在Doctrine DQL中做些小事：

I am trying to do the fallowing in 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 \Digital\ApplicationBundle\Entity\ChallengeScore s1
            LEFT OUTER JOIN \Digital\ApplicationBundle\Entity\ChallengeScore 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 '�'

我在做什么错了？

推荐答案

这在DQL中应该有效，您必须<$如果您没有为实体定义任何映射，并且仍然想加入2个具有共同属性的实体，则可以使用 WITH c $ c>子句

This should work in DQL you have to ON part handles by doctrine if you have defined any mapping for your entities if not and still want to join 2 entities with common attribute you can use WITH clause

SELECT s1 
FROM \Digital\ApplicationBundle\Entity\ChallengeScore s1
    LEFT OUTER JOIN \Digital\ApplicationBundle\Entity\ChallengeScore 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

OR

SELECT s1 
FROM \Digital\ApplicationBundle\Entity\ChallengeScore s1
    LEFT OUTER JOIN \Digital\ApplicationBundle\Entity\ChallengeScore 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

这篇关于DQL学说查询翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！