Doctrine 2 native query order by custom column

Question

I have problem with custom ordering of result in Native Query Doctrine 2. I have this SQL tested on server:

SELECT
    c.id, c.is_verified, c.email, c.phone, i.id as identityId,
    i.firstname, i.lastname, g.id as groupId, g.name as groupName,
    r.id as roleId, r.name as roleName
FROM
    UserCredential c
JOIN
    UserIdentity i
ON
    i.id = c.identity_id
JOIN
    Role r
ON
    r.id = c.role_id
LEFT JOIN
    `Group` g
ON
     g.id = c.group_id
ORDER BY
     i.firstname ASC
LIMIT
    10
OFFSET
    0

Everything works but, when I rewrote this to Native Query in Custom repository:

    $query = $this->_em->createNativeQuery('
        SELECT
            c.id,
            c.is_verified,
            c.email,
            c.phone,
            i.id as identityId,
            i.firstname,
            i.lastname,
            g.id as groupId,
            g.name as groupName,
            r.id as roleId,
            r.name as roleName
        FROM
            UserCredential c
        JOIN
            UserIdentity i
        ON
            i.id = c.identity_id
        JOIN
            Role r
        ON
            r.id = c.role_id
        LEFT JOIN
            `Group` g
        ON
            g.id = c.group_id
        ORDER BY
            :orderBy
        LIMIT
            :lmt
        OFFSET
            :ofs
    ', $rsm);

    $query->setParameter('orderBy', implode(', ', $orderBy));
    $query->setParameter('lmt', $limit);
    $query->setParameter('ofs', $offset);

Where $orderBy is array $column => $orderType.

But if I change order by parameters result from this query is always returned with default ordering (c.id ASC).

I've tested this and query is generated OK but I don't know why is result always ordered by default. Is Doctrine 2 somehow ignoring ORDER BY?

Supposedly not (ignoring ORDER BY would not make any sense for a native query, right?). But probably something is cached? — hakre, Jan 30 '12 at 16:20
No, I'm running it in development, so there is ArrayCache, which means it caches only on per-request basis. — Michal K., Jan 30 '12 at 16:27
Ok I've solved this. You can't pass order by params as parameter to clause. It has to be set in string ('ORDER BY ' . implode(...) . '..'). — Michal K., Jan 30 '12 at 17:15
Ah, then this is probably related [Mysqli Prepare Statements + Binding Order BY](http://stackoverflow.com/questions/4741845/mysqli-prepare-statements-binding-order-by) - Which database driver are you using? Also please add your solution as an answer below. — hakre, Jan 30 '12 at 17:17

Doctrine 2 native query order by custom column

0 Answers0