length in twig - why not using a count query?

Discussion:

Sören Martius

2013-01-12 11:48:20 UTC

Lets say we have an ArrayCollection of Objects messages. when i try to
count the number of the containing objects i can do it in twig like:
message.length ..

If i understood the workflow correct doctrines lazy loading will fire an
query to select all messages... why isnt doctrine using a count query?
--
--
If you want to report a vulnerability issue on Symfony, please read the procedure on http://symfony.com/security

You received this message because you are subscribed to the Google
Groups "Symfony2" group.
To post to this group, send email to symfony2-/***@public.gmane.org
To unsubscribe from this group, send email to
symfony2+unsubscribe-/***@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/symfony2?hl=en

Christophe COEVOET

2013-01-12 12:30:50 UTC

Permalink

Post by SÃ¶ren Martius
Lets say we have an ArrayCollection of Objects messages. when i try to
message.length ..
If i understood the workflow correct doctrines lazy loading will fire
an query to select all messages... why isnt doctrine using a count query?

Define your collection as EXTRA_LAZY in the mapping of your relation and
this is what Doctrine will do.
--
Christophe | Stof
--
--
If you want to report a vulnerability issue on Symfony, please read the procedure on http://symfony.com/security

You received this message because you are subscribed to the Google
Groups "Symfony2" group.
To post to this group, send email to symfony2-/***@public.gmane.org
To unsubscribe from this group, send email to
symfony2+unsubscribe-/***@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/symfony2?hl=en

Sören Martius

2013-01-15 20:11:42 UTC

Permalink

hi stof,

i am not talking about an association. I am talking about an
ArrayCollection Entity Objects.

For example i got a Collection of messages with the magic findBy() Method
in the Controller which i pass to my Template. When i count the messages
like messages.length i am wondering cause symfony is not using a Count
Query.

Do you understand about what i am talking?

Post by SÃ¶ren Martius
Lets say we have an ArrayCollection of Objects messages. when i try to
message.length ..
If i understood the workflow correct doctrines lazy loading will fire an
query to select all messages... why isnt doctrine using a count query?

--
--
If you want to report a vulnerability issue on Symfony, please read the procedure on http://symfony.com/security

You received this message because you are subscribed to the Google
Groups "Symfony2" group.
To post to this group, send email to symfony2-/***@public.gmane.org
To unsubscribe from this group, send email to
symfony2+unsubscribe-/***@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/symfony2?hl=en

Sören Martius

2013-01-15 20:13:46 UTC

Permalink

i had an typo in my post.. i am talking about messages|length ! Sorry. Do
you have any advice?

Post by SÃ¶ren Martius
hi stof,
i am not talking about an association. I am talking about an
ArrayCollection Entity Objects.
For example i got a Collection of messages with the magic findBy() Method
in the Controller which i pass to my Template. When i count the messages
like messages.length i am wondering cause symfony is not using a Count
Query.
Do you understand about what i am talking?

Post by SÃ¶ren Martius
Lets say we have an ArrayCollection of Objects messages. when i try to
message.length ..
If i understood the workflow correct doctrines lazy loading will fire an
query to select all messages... why isnt doctrine using a count query?

Christian Flothmann

2013-01-17 18:17:53 UTC

Permalink

Hi,

Post by SÃ¶ren Martius
i am not talking about an association. I am talking about an
ArrayCollection Entity Objects.
For example i got a Collection of messages with the magic findBy() Method
in the Controller which i pass to my Template. When i count the messages
like messages.length i am wondering cause symfony is not using a Count
Query.

Well, if you have an ArrayCollection you would likely use the elements of it
in some way. As a consequence Doctrine would need to fire two queries (one for
counting and the second for selecting the entries) in most cases where just
one query is sufficient. If you are only interested in the number of results you
shouldn't use a collection but query the number of entries yourself.

To be honest, this is just what I guess why Doctrine behaves this way and not
based on any 'official' statement.

--

Jasper N. Brouwer

2013-01-18 08:50:56 UTC

Permalink

Christian is correct here. To be more specific:

When you use the findAll, findBy or magic findBy* methods (of \Doctrine\ORM\EntityRepository), the will return a plain php array containing entity classes as result.
They will *not* return a \Doctrine\Common\Collections\ArrayCollection class (or any other implementation of \Doctrine\Common\Collections\Collection).

The Twig "length" function will simply perform a php count() on the property it is used on (which is an array or class implementing \Countable). In some implementations of \Doctrine\Common\Collections\Collection this _might_ result in Doctrine performing a count-query.

So down the line, in your case, you are performing a php count() on a plain php array. There is no way Symfony or Doctrine can derive that you actually want to perform a count-query.

PS: IMHO it doesn't make sense you want to perform a count-query, because you already have the actual result in memory. It's more efficient to just count that result, just like Christian said.
--
Jasper N. Brouwer

Post by Christian Flothmann
Well, if you have an ArrayCollection you would likely use the elements of it
in some way. As a consequence Doctrine would need to fire two queries (one for
counting and the second for selecting the entries) in most cases where just
one query is sufficient. If you are only interested in the number of results you
shouldn't use a collection but query the number of entries yourself.
To be honest, this is just what I guess why Doctrine behaves this way and not
based on any 'official' statement.

Sören Martius

2013-01-18 09:51:53 UTC

Permalink

hi, i ve added extra_lazy to the association of my entity. in the current
template i only need the counted number of objects.

when i call in my template messages.comments|length doctrine will fire a
count query and that is fine. But when i call the same command a few lines
later doctrine will fire a second count query. why does this happen? Why
not getting the result from the memory?

Post by SÃ¶ren Martius
Hi,

Post by SÃ¶ren Martius
i am not talking about an association. I am talking about an
ArrayCollection Entity Objects.
For example i got a Collection of messages with the magic findBy()

Method

Post by SÃ¶ren Martius
in the Controller which i pass to my Template. When i count the messages
like messages.length i am wondering cause symfony is not using a Count
Query.

Well, if you have an ArrayCollection you would likely use the elements of it
in some way. As a consequence Doctrine would need to fire two queries (one for
counting and the second for selecting the entries) in most cases where just
one query is sufficient. If you are only interested in the number of results you
shouldn't use a collection but query the number of entries yourself.
To be honest, this is just what I guess why Doctrine behaves this way and not
based on any 'official' statement.

Jasper N. Brouwer

2013-01-18 10:28:59 UTC

Permalink

I'm not exactly sure (I haven't looked at the code), but from your usecase it's clear the result of the count isn't reused.

One of the things that's made possible with extra-lazy associations (and some other features) is that you can fetch a part of the association from the database based on criteria. Something like: I want all associated messages less than a week old.

Doctrine could have implemented some kind of storage mechanism that remembers the result each unique set of criteria. This potentially could take up quite a bit of memory. So I guess they choose not to store anything (and just run the queries again).

As far as I know Doctrine doesn't (yet) support using a result-cache on associations. IMHO this would be a nice storage mechanism for the usecase you describe (and many others).
--
Jasper N. Brouwer

hi, i ve added extra_lazy to the association of my entity. in the current template i only need the counted number of objects.
when i call in my template messages.comments|length doctrine will fire a count query and that is fine. But when i call the same command a few lines later doctrine will fire a second count query. why does this happen? Why not getting the result from the memory?

Sören Martius

2013-01-18 10:31:48 UTC

Permalink

Yes your are right. I decided to use extra_lazy cause auf make it possible
to run automatically count querys with doctrine when using length function
in twig.

Post by Jasper N. Brouwer
I'm not exactly sure (I haven't looked at the code), but from your usecase
it's clear the result of the count isn't reused.
One of the things that's made possible with extra-lazy associations (and
some other features) is that you can fetch a part of the association from
the database based on criteria. Something like: I want all associated
messages less than a week old.
Doctrine could have implemented some kind of storage mechanism that
remembers the result each unique set of criteria. This potentially could
take up quite a bit of memory. So I guess they choose not to store anything
(and just run the queries again).
As far as I know Doctrine doesn't (yet) support using a result-cache on
associations. IMHO this would be a nice storage mechanism for the usecase
you describe (and many others).
--
Jasper N. Brouwer

Post by SÃ¶ren Martius
hi, i ve added extra_lazy to the association of my entity. in the

current template i only need the counted number of objects.

Post by SÃ¶ren Martius
when i call in my template messages.comments|length doctrine will fire a

count query and that is fine. But when i call the same command a few lines
later doctrine will fire a second count query. why does this happen? Why
not getting the result from the memory?