Support latestOfMany/oldestOfMany for UUID columns in PostgreSQL

[aimeos]

Currently, latestOfMany/oldestOfMany use MAX/MIN to get the latest/oldest record of a relation:

(
    select
        MAX(\"cms_versions\".\"id\") as \"id_aggregate\",
        \"cms_versions\".\"versionable_id\",
        \"cms_versions\".\"versionable_type\" 
    from \"cms_versions\"
    where \"cms_versions\".\"tenant_id\" = demo
    group by \"cms_versions\".\"versionable_id\", \"cms_versions\".\"versionable_type\"
) as \"latestOfMany\"

If the "id" column is of UUID type, this fails in PostgreSQL because it doesn't support MAX/MIN on UUID columns and this is already stated in the Laravel docs.

When rewriting the latest/oldest sub-query to a SELECT with ORDER BY and LIMIT, latestOfMany/oldestOfMany could be supported easily when the sub-query looks like:

(
    select
        \"cms_versions\".\"id\" as \"id_aggregate\",
        \"cms_versions\".\"versionable_id\",
        \"cms_versions\".\"versionable_type\"
    from \"cms_versions\"
    where \"cms_versions\".\"tenant_id\" = demo
    order by \"cms_versions\".\"versionable_id\", \"cms_versions\".\"versionable_type\"
    limit 1
) as \"latestOfMany\"

This query is also easier to optimize compared to the MAX/MIN and GROUP BY query.

[marius-ciclistu]

From my experience on big data sets max and min are slower than sub select limit 1 with order.