Filter on a date field which is not part of the grid group results with "having"

Hi,

I am trying to filter the results of a grouped grid which pulls some summary statistic. My main file contains an Identification Date, which is part of the file, but not selected in the final output, but I would still like to be able to filter on the date range.

The grid code does compile and the filter shows up on screen, but when I enter a date range with existing dates, it does not properly select the appropriate transactions with the Having clause and returns the whole dataset with all dates results;

Here is the Datagrid code:

YAML

rapport-identifications-par-agence-statut-count: pageTitle: "Identifications par Agence et statut (A ce jour)" source: type: orm acl_resource: application.core.identification.identification_view query: select: - Bu.name - Identity.statut - COUNT(Identity.id) as NombreIds where: and: - (Identity.isFraude is NULL or Identity.isFraude <> true or (Identity.isFraude=true and Identity.isRejet = true)) - Bu.name IS NOT NULL - Identity.statut in ('Identification Validée', 'Identification Complétée', 'En modification', 'Identification Rejetée','Identification PréValidée', 'Identification Bloquée') # - Identity.identifiedAt BETWEEN :startdate and :enddate from: - { table: 'ApplicationCoreIdentificationBundle:Identification', alias: Identity } join: left: - { join: Identity.bunit, alias: Bu } groupBy: Bu.name, Identity.statut columns: name: { label: "Nom agence" } statut: { label: "Statut" } NombreIds: { label: "#Identifications", frontend_type: number } sorters: columns: name: { data_name: Bu.name } statut: { data_name: Identity.statut } filters: columns: identifiedAt: label: "Date selection" type: date data_name: Identity.identifiedAt filter_by_having: true options: entityHint: report data export: true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

rapport-identifications-par-agence-statut-count:         pageTitle: "Identifications par Agence et statut (A ce jour)"         source:             type: orm             acl_resource: application.core.identification.identification_view             query:                 select:                     - Bu.name                     - Identity.statut                     - COUNT(Identity.id) as NombreIds                 where:                     and:                         - (Identity.isFraude is NULL or Identity.isFraude <> true or (Identity.isFraude=true and Identity.isRejet = true))                         - Bu.name IS NOT NULL                         - Identity.statut in ('Identification Validée', 'Identification Complétée', 'En modification', 'Identification Rejetée','Identification PréValidée', 'Identification Bloquée') #                        - Identity.identifiedAt BETWEEN :startdate and :enddate                                          from:                     - { table: 'ApplicationCoreIdentificationBundle:Identification', alias: Identity }                 join:                     left:                         - { join: Identity.bunit, alias: Bu }                 groupBy:  Bu.name, Identity.statut         columns:             name:   { label: "Nom agence" }             statut: { label: "Statut" }             NombreIds:  { label: "#Identifications",  frontend_type: number }         sorters:             columns:                 name:   { data_name: Bu.name }                 statut: { data_name: Identity.statut }         filters:             columns:                 identifiedAt:                     label:            "Date selection"                     type:             date                     data_name:        Identity.identifiedAt                     filter_by_having: true                          options:             entityHint: report data             export: true

My understanding is that the filter by having should work just like the SQL Having clause in MYSQL which actually works in that case.

Topic

[mmiasnikovParticipant]

Hello, phil5.

As I can understand from your code example you should add Identity.identifiedAt to ‘groupBy’ clause or filter it on ‘where’ conditions, not in ‘having’.

[Author]

Replies