2

I have a native query in Doctrine, which generate the right SQL, which runs in PostgreSQL. However the Doctrine returns null/empty results.

public count unReadedNotifications(User $user) {

    $rsm = new ResultSetMapping();

    $sql =
        "SELECT count(ele->'status') FROM notification CROSS JOIN LATERAL jsonb_array_elements(items) status(ele) WHERE notification.id = ? and (ele->'status')::jsonb @> '1'";

    $query = $this->getEntityManager()->createNativeQuery($sql,$rsm)->setParameter('1', $user->getNotification()->getId());
    return $query->getSingleScalarResult();
}

This Doctrine native query returns null. If I check the profiler I see the generated query is:

SELECT count(ele->'status') FROM notification CROSS JOIN LATERAL jsonb_array_elements(items) status(ele) WHERE notification.id = 21 and (ele->'status')::jsonb @> '1';

If I copy-paste this into pgAdmin it runs and it retrieve the desired "2".

So pgAdmin can gives me the right result, but Doctrine not.

Can somebody see, where and what went wrong?

Doctrine version: 2.6.3, Postgres is 10.

2
  • have you managed to find the solution? I got stuck in a similar issue where Doctrine not giving result but PhpMyAdmin Does with the same generated query Commented Sep 26, 2019 at 7:13
  • You should be able to find your solution here. Commented Feb 13, 2021 at 6:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.