Useless queries on not displayed related entities

[jocel1]

After taking a look at the SQL queries, I've just figured out Api platform was requesting useless queries from object not displayed in the JSON.

How to reproduce:

In the DB:

DROP DATABASE IF EXISTS apibug;
CREATE DATABASE apibug;

Then:

git clone https://github.com/Softizy/api-bug
cd api-bug;
./bin/console doctrine:schema:create
./bin/console server:start 

curl -X POST "http://127.0.0.1:8000/api/mums" -H "accept: application/ld+json" -H "Content-Type: application/ld+json" -d "{ \"mumId\": \"0test0\"}"

 curl -X GET "http://127.0.0.1:8000/api/mums" -H "accept: application/ld+json" -H "Content-Type: application/ld+json"

{  
   "@context":"\/api\/contexts\/Mums",
   "@id":"\/api\/mums",
   "@type":"hydra:Collection",
   "hydra:member":[  
      {  
         "@id":"\/api\/mums\/0test0",
         "@type":"Mums",
         "mumId":"0test0"
      }
   ],
   "hydra:totalItems":1
}

In the doctrine debug, you'll see:

cat ./var/log/dev.log
[2019-06-17 19:23:49] doctrine.DEBUG: SELECT m0_.mum_id AS mum_id_0 FROM mums m0_ ORDER BY m0_.mum_id ASC LIMIT 10 [] []
[2019-06-17 19:23:49] doctrine.DEBUG: SELECT t0.latitude AS latitude_1, t0.longitude AS longitude_2, t0.mum_id AS mum_id_3 FROM mum_locations t0 WHERE t0.mum_id = ? ["0test0"] []
[2019-06-17 19:23:49] doctrine.DEBUG: SELECT count(m0_.mum_id) AS sclr_0 FROM mums m0_ [] []

The query on mum_locations seems to be useless, it shouldn't be hydrated by getOneOrNullResult
Am I missing something?

[soyuka]

Did you set up serialization groups? You could also disable eager loading for this resource.

[jocel1]

Already try disabling the eager loading, even with EXTRA_LAZY it's still doing the fetch :)
You can see the entity here:
https://github.com/Softizy/api-bug/blob/master/src/Entity/Mums.php
Serialization groups are indeed enabled, but the mum_locations properly doesn't have any serialization group, so it should be excluded (and it's indeed excluded from the json result)

[teohhanhui]

As an aside, naming the entity in plural is really confusing! 🙈

[teohhanhui]

Please see doctrine/orm#4389 (comment) and doctrine/orm#4389 (comment)

It's how Doctrine works.

[teohhanhui]

So indeed, what we could do in API Platform is to always eager load such an association, even if it's not in the serializer group. Counter intuitive, I know! Doing an unnecessary join ends up saving us one query.

[teohhanhui]

The alternative is to enable partial loading, but that comes with its own problems... (Hence not enabled by default.)

[jocel1]

Hi @teohhanhui! Thanks for the feedback, indeed those plural entities need to be changed ;)