DQL And Equivalent SQL Not Returning Same Number Of Result Set

Question

Here is my doctrine query running code:

        $queryString = "SELECT ct, count(ct.id), IDENTITY(a.service) "
                        . "FROM ConnectionTriple ct "
                        . "JOIN ct.account_connection ac "
                        . "JOIN Account a WITH (a = ac.account_1 OR a = ac.account_2) "
                        . "GROUP BY a.service, ct.property, ct.value";
        $query = $em->createQuery($queryString);
        //echo $query->getSQL();
        $results = $query->getResult();
        echo count($results);

This above code is returning 2 results(final two from below screenshot) instead of 4(expected). But, when I run the equivalent SQL(got by $query->getSQL()) on phpmyadmin, it returns expected 4 rows which is as below:

Equivalent SQL Query:

SELECT u0_.id AS id0, u0_.value AS value1, u0_.status AS status2, u0_.flag AS flag3, count(u0_.id) AS sclr4, u1_.service_id AS sclr5, u0_.property_id AS property_id6, u0_.account_connection_id AS account_connection_id7 FROM usc_connection_triple u0_ INNER JOIN usc_account_connection u2_ ON u0_.account_connection_id = u2_.id AND (u2_.status = 1) INNER JOIN usc_service_subscriber u1_ ON ((u1_.id = u2_.account_1_id OR u1_.id = u2_.account_2_id)) WHERE (u0_.status = 1) AND (u1_.status = 1) GROUP BY u1_.service_id, u0_.property_id, u0_.value

PHPMyAdmin Result:

enter image description here

So, I guess, there is something wrong in result to object hydration by doctrine, I guess. Anyone has any idea why this might happen/possible solution?

My Doctrine version are:

"doctrine/dbal": "2.3.2",
"doctrine/orm": "2.3.2",

Update: I am certain about the hydration issue. Because, I tried with individual column retrieving and using scalar hydration:

$results = $query->getResult(\Doctrine\ORM\Query::HYDRATE_SCALAR);

This is returning perfectly. Which is expected number of rows, 4 and data as well.

You're aware that columns that aren't in the `GROUP BY` or an aggregate return a "random" value if there's more than one row, right? Pretty much all other RDBMSs throw a syntax error for this statement. I don't know that it effects your problem here, but it's something you need to be aware of. — Clockwork-Muse, Jul 18 '14 at 23:44
Thanks for the point. However, I am not having any issue on mysql side, and as I updated, I am pretty sure the issue is on doctrine library side. I will try with version upgrading soon and see if have any luck. — Rana, Jul 19 '14 at 05:43
just for the record: when you got some error like this, please check you're pointing to the right database in order not to spend some hours trying to point out what's wrong with Doctrine' code, just to realize it's the wrong DB (like I just did). :D — Nelson Teixeira, Sep 24 '15 at 23:09

score 0 · Answer 1 · answered Aug 11 '14 at 07:17

I would say this is totally normal.

As you pointed it out, doctrine (default) hydration mode would represent the result set as an object graph.

An object graph always have a root object (in your case ConnectionTriple ct). There are 2 ct in your result set (with id 1 and 2). Doctrine object hydrator will be smart enough to return you and array of 2 ConnectionTriple objects, each line containing the related data.

The scalar hydrator, howerver will simply return the raw resultset, without building a graph from it.

DQL And Equivalent SQL Not Returning Same Number Of Result Set

1 Answers1