DQL Doctrine query translation

Question

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.

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

My current state is:

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

And I am getting the following error:

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

What am I doing wrong?

Answer 1

Try put two backslashes in the namespaces.. like:

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

if this doe's not work, try to do the query in small parts, to figure out where is the problem..

Answer 2

This should work in DQL, ON part handles by doctrine if you have defined any mapping for your entities if not and still want to join 2 entities with a 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