Doctrine ORDER BY column but DISTINCT on another column

Question

Can't seem to wrap my head round a Doctrine query which I am trying to execute for a monitoring system I am working on. My underlying DB is SQLite.

My table is fairly simple:

CREATE TABLE disk_space_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    disk_name VARCHAR(255) NOT NULL,
    size INTEGER NOT NULL,
    free INTEGER NOT NULL,
    d_profile DATETIME NOT NULL --(DC2Type:datetime_immutable),
    site_id INTEGER NOT NULL
)

Each time the import from the data gathering system runs we will have multiple new records created for each "site_id". Each group of records for a "site_id" will have the same "d_profile" value.

I want to query Doctrine to get the latest set of records available for a given "site_id".

For example, say "site_id" 12345 has 5 disks, "d1", "d2", "d3", "d4" and "d5" and that this import had been running for 100 days consecutively. There would be 500 records related to "site_id" 12345. I would want to get the latest 5.

However, the number of disks will vary from site to site, and may vary for any given site as disks are commissioned and decommissioned. Additionally, there is no guarantee that the import will run every day, so I need to get the most recent set of records for this site, regardless of date and regardless of the number of disks.

My query currently looks like this:

return $this->createQueryBuilder('dsu')
    ->select()
    ->distinct()
    ->where('dsu.site = :customer')
    ->groupBy('dsu.diskName')
    ->orderBy('dsu.dProfile', 'DESC')
    ->setParameter('customer', $customerSite)
    ->getQuery()
    ->getResult();

However, while this returns a single set of disks for this site, they are the oldest, not the most recent records.

Any and all help appreciated! :)

M

Answer 1

I think this should solve your problem.
distinc() isn't required and will fail because some fields are différent for each entry.

I specified the data to be selected to have only fields with same value.
To know the most recent, I used MAX() to select dProfil.
The groupBy() will make it so you get only unique/distinct result.
Then returned a scalar result (array instead of object).

return $this->createQueryBuilder("disque")
            ->select("disque.id, disque.diskName, disque.size, disque.free, MAX(disque.dProfile) AS dProfile")
            ->andWhere("disque.site = :customer")
            ->groupBy("disque.diskName")
            ->orderBy("disque.dProfile", "DESC")
            ->setParameter('customer', $customerSite)
            ->getQuery()
            ->getScalarResult();