0

MySQL 5.7

I have a table with filenames in and these need to be allocated to certain users depending on the most specific match from another table (basically to be used as ACLs for editing).

So for example: files:

|id|path            |
|--|----------------|
| 1|/a/file1.pdf    |
| 2|/b/file2.pdf    |
| 3|/a/c/file3.pdf  |
| 4|/a/d/e/file4.pdf|
| 5|/d/a/file5.pdf  |

users:

|uid|sub |user |
|---|----|-----|
|  1|a   |user1|
|  2|b   |user2|
|  3|a/d |user3|
|  4|d   |user1|

i.e. everything in the 'a' directory and subdirectories should be owned by user1, unless it is in the a/d subdirectory. user1 also owns the 'd' directory.

I want a query to return only:

|id| user|
|--|-----|
| 1|user1|
| 2|user2|
| 3|user1|
| 4|user3|
| 5|user1|

I tried

SELECT files.id, users.user, LENGTH(users.sub) AS len 
FROM files 
JOIN users ON LEFT(files.path, 2+LENGTH(users.sub)) = CONCAT('/',users.sub,'/')

I only want the record with maximum len, not the less specific match of, say, /a matching /a/d/e.

This is not a delimited list as described in Is storing a delimited list in a database column really that bad? and none of the problems with delimited list described in the main answer there apply here.

EDIT Here's a working solution which puts the query above into a subquery that orders by specificity within an id (which is just descending alphanumeric ordering as a/d is above a in that case), which is then coupled with a grouping row counter to just show the first, i.e. most specific, hit.

SET @row_number:=0;
SET @id:=0;
SELECT (@row_number:=CASE
    WHEN @id = id
        THEN @row_number + 1
    ELSE 1
END) AS num, @id:=id id, user FROM (SELECT files.id, users.user
    FROM files 
    JOIN users 
    ON LEFT(files.path, 2+LENGTH(users.sub)) = CONCAT('/',users.sub,'/')
    ORDER BY id, sub DESC) AS c HAVING num=1
Stephen B
  • 1
  • 1
  • please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Mar 04 '21 at 18:41
  • Why do you do `CONCAT('/',users.sub,'/')` ? the columns files.path and users.sub alread have some (=enough) '/' – Luuk Mar 04 '21 at 18:44
  • The leading / in user.sub was an error in the example data I provided here, this isn't a problem in the live data. Example now fixed. – Stephen B Mar 05 '21 at 13:53

0 Answers0