No idea how to write complex SQL query

Question

Hi

I have following db structure:

File, User, FileRevision (has foreign key to File, and many-2-many connection through intermediate table to User).

I want to fetch all FileRevision-s that:

1. are newest/freshest in their corresponding File-s,
2. have many-2-many link to User that performs search (permission checking).

I found out that I can do (1) by executing 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 have no clue how to simultaneously perform also m2m permission check

Answer 1

Just add this to your query:

UNION
SELECT created_on, file_id
FROM FileRevision fr
WHERE fr.user_id = ?

Replace ? with whatever value you want based on your permission checking.

Additionally, if you replace your query to:

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'll avoid the correlated (repeating) subquery.

Answer 2

To check permsissions you need to check that a record exists in ther other many-2-many permissions 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 the requestor has access to, use AND.

If you want Both the last Rev and the records the the requestor has access to, 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 = ?)

Answer 3

This is a variation of the "greatest-n-per-group" problem. Here's how I solve it with no subquery and no 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 the desired user id for the "?".