Don't know how to write a complex SQL query
I have the following db structure:
File, User, FileRevision (has a foreign key for the file and many-two-many connections through the intermediate table for the user).
I want to get all the FileRevision files that are:
- are the newest / freshest in their respective files,
- there are many-2-many links to the user doing the search (permission check).
I found out I can do (1) by doing something like:
SELECT created_on, file_id FROM FileRevision
WHERE created_on = (SELECT MAX(created_on) FROM FileRevision
WHERE filed_id = file_id)
but i dont know how to do m2m permission check at the same time
source to share
This is a variation of the "largest n-per-group" problem. This is how I solve it without subquery and not GROUP BY
:
SELECT f1.*
FROM Permissions p -- this is the many-to-many table
JOIN FileRevision f1
ON (f1.file_id = p.file_id)
LEFT OUTER JOIN FileRevision f2
ON (f2.file_id = p.file_id AND f1.created_on < f2.created_on)
WHERE p.user_id = ? AND f2.file_id IS NULL;
Substitute your desired user ID for " ?
".
source to share
Just add this to your request:
UNION
SELECT created_on, file_id
FROM FileRevision fr
WHERE fr.user_id = ?
Replace? with whatever value you want based on checking your permission.
Also, if you replace your query with:
SELECT created_on, file_id
FROM FileRevision fr
JOIN
(
SELECT file_id, MAX(created_on) as latestDate
FROM FileRevision
GROUP BY file_id
) latest ON latest.file_id = fr.file_id
AND latest.latestDate = fr.created_on
You will avoid a correlated (duplicate) subquery.
source to share
To check permissions, you need to check if an entry exists in another many-2-many permission table for the user requesting the files. So add an And / OR Exists ... clause ... If you want (as I suspect) only the last revision that is being requested, use AND.
If you want both the last one and the records that the query is accessing use OR.
SELECT created_on, file_id
FROM FileRevision r
WHERE created_on =
(SELECT MAX(created_on)
FROM FileRevision
WHERE file_id = r.file_id)
And Exists -- Change 'And' to 'Or if you want both
(Select * From M2MIntermediatePermissionsTable
Where File_Id = r.File_Id
And userId = ?)
source to share