I'm making a custom DMS (Laravel 8 and MySQL 8.0.27) and have two tables - documents with a few basic bits of information (id, confidentiality, document type etc.) and versions which contains everything else (title, filetype, original filename, various classification codes etc.) with a one-to-many relationship (one document has one or more versions). I'm working with datatables.net on the front end and need to get a table that has the info from the documents table, and just the latest version of that document. The way I've done it so far was like this:
SELECT d.id, d.confidentiality, v.id as version_id,
v.title, v.system_code, v.system_code,
v.version, v.updated_at, v.created_at, v.filename_orig
FROM documents d
INNER JOIN
(SELECT * FROM versions ORDER BY versions.created_at DESC) v
ON d.id = v.document_id
GROUP BY d.id
This works fine in phpmyadmin but in my app I get the error that boils down to "this is incompatible with sql_mode=only_full_group_by" which forces MySQL to act the way most DBs have so far - requiring that everything I SELECT appears in GROUP BY. The problem is that if I do this I no longer get a table with distinct document.id fields - I get every version associated with that document instead of just the latest one. From what I've seen disabling only_full_group_by in my config would work but it doesn't seem to be recommended. I'd appreciate any ideas for a neater solution to this problem...
Edit: I think P.Salmon had the right idea. Read through the link and this seems to be working for me:
WITH latest AS (
SELECT v.*, ROW_NUMBER() OVER (PARTITION BY v.document_id ORDER BY v.created_at DESC) AS rn
FROM versions v
)
SELECT * FROM LATEST l
JOIN documents d ON l.document_id = d.id
WHERE l.rn = 1
