Count is not made on the primary column when it is in a joined table.

[MaxandreOgeret]

Hello everyone.

I have a problem and I can't solve it.
I have to display a grid, containing datas from a table and a joined table.
The primary column is set correctly, but the count query is not made on this column ! It is made on the id of the first table !

Why should the primary column not be used to count? I don't understand. Is it a bug?

SELECT count(DISTINCT h0_.id) AS sclr_0 FROM hierabc h0_ INNER JOIN AdresseAll a1_ ON (h0_.idpeople= a1_.idpeople) WHERE h0_.idpeoplesup= ('XXXXXXXXXXX'');
Here h0.id, is the id of the entity selected as source. But it is not the column set as primary.

Don't hesitate to ask me anything if you need further details!

Thanks a lot for your help !!

[DonCallisto]

Hello, I'm trying to investigate it but, conceptually, isn't the result the same?
If a row is filtered out by join condition, it will not appear. If it is in there, you can count on pretty much every (unique) column.

In your case is not id a unique field so the count is not properly reported?

[MaxandreOgeret]

Thanks for your answer.

No, the result was not the same, as the count is a count distinct.

[001|value1]
[001|value2]
[002|value3]
[002|value4]

This will only count 2, while it should count 4.

[DonCallisto]

Ok, so because it's not a unique value field where value* is; am I right?

[DonCallisto]

What is your Source? Entity?

[DonCallisto]

@MaxandreOgeret check this out https://github.com/APY/APYDataGridBundle/blob/58f4e370ba79bd7d593a3759010ab002b3946b61/Resources/doc/columns_configuration/annotations/column_annotation_property.md

Note 4: If you have and id field and want to use another field as primary, you need to set primary=false on the id field.

[MaxandreOgeret]

Yes you're right. and my source is an entity

Setting the field as primary do not work with the count query. The count walker, takes the primary key of the source entity, not the column set as primary.

[DonCallisto]

@MaxandreOgeret follow my last comment, it should resolve your issue.

[MaxandreOgeret]

I tried, but it don't change anything. :/

[DonCallisto]

Can you show me relevant code?

[MaxandreOgeret]

I don't have the sources right now, but i'll try to give you an exemple.

I have two tables :

[---CUSTOMER---]
[ID |NAME |LEVEL]
[001|value1| 01]
[002|value2| 02]
[003|value3| 02]
[004|value4| 03]
[005|value5| 03]
[006|value6| 03]
[007|value7| 03]

[---------CUSTOMER-TREE---------]
[CUSTOMER ID|PARENT CUSTOMER]
[ 002| 001]
[ 003| 001]
[ 004| 002]
[ 005| 002]
[ 006| 003]
[ 007| 003]

These tables represents this tree :

            1
           /  \
         2     3
       /  \    /  \
      4   5  6   7

Let's say customers have more than one adresses and i want to list those adresses like :

[max_parent|customer| adress]
[ 01| 01| adress1]
[ 01| 02| adress2]
[ 01| 03| adress3]
[ 01| 04| adress4]
[ 01| 05| adress5]
[ 01| 06| adress6]
[ 01| 07| adress7]

If my primary entity is CUSTOMER and max_parent comes from it, it will count only one.

Tell me if you want more explanations.

[MaxandreOgeret]

You may just check that the count request is made on the column marked as primary.

[DonCallisto]

I understand.
Let me tackle this (hopefully) in the next days: I'll try to reproduce it and let you know.

BTW I can't guarantee that I'll take a look soon as from today I'm in vacation :)

Last question: can you tell me what source are you using? Entity, Document (Mongo) or Vector as grid source?

[MaxandreOgeret]

I am using Entity as grid source, thanks you :)