Query M:N contains

Question

I am trying to filter a set of tables that includes an M:N junction table in Android Room (SQLite).

An image can have many subjects. I'd like to allow filtering by a subject, so that I get a row with complete image information (including all subjects). So if an image had (National Park, Yosemite) filtering for either would result in one row with both keywords. Unless I messed something up, a typical join will result in multiple rows such that matching Yosemite would get the right image, but you'd be lacking National Park. I came up with this:

SELECT *,  
  (SELECT GROUP_CONCAT(name) 
    FROM meta_subject_junction
    JOIN subject 
      ON subject.id = meta_subject_junction.subjectId
      WHERE meta_subject_junction.metaId = meta.id) AS keywords, 
  (SELECT documentUri
    FROM image_parent
    WHERE meta.parentId = image_parent.id ) AS parentUri
FROM meta

Now this gets me the complete rows, but I think at this point I'd need to:

WHERE keywords LIKE(%YOSEMITE%)

and I think the LIKE is less than ideal, not to mention an imprecise match. Is there a better way to accomplish this? Thanks, this is bending my novice SQL brain.

Further details

meta
+----+----------+--+
| id |   name   |  |
+----+----------+--+
|  1 | yosemite |  |
|  2 | bryce    |  |
|  3 | flowers  |  |
+----+----------+--+
subject
+----+---------------+--+
| id |     name      |  |
+----+---------------+--+
|  1 | National Park |  |
|  2 | Yosemite      |  |
|  3 | Tulip         |  |
+----+---------------+--+
junction
+--------+-----------+
| metaId | subjectId |
+--------+-----------+
|      1 |         1 |
|      1 |         2 |
|      2 |         1 |
|      3 |         3 |
+--------+-----------+

Although I may have done something wrong, as far as I can tell Android Room doesn't like:

+----+-----------+---------------+
| id |   name    |    subject    |
+----+-----------+---------------+
|  1 | yosemite  | National Park |
|  1 | yosemite  | Yosemite      |
+----+-----------+---------------+

so I'm trying to reduce the rows:

+----+-----------+-------------------------+
| id |   name    |         subject         |
+----+-----------+-------------------------+
|  1 | yosemite  | National Park, Yosemite |
+----+-----------+-------------------------+

which the above query does. However, I also want to query for a subject. So that National Park filter will yield:

+----+-----------+-------------------------+
| id |   name    |         subject         |
+----+-----------+-------------------------+
|  1 | yosemite  | National Park, Yosemite |
|  2 | bryce     | National Park           |
+----+-----------+-------------------------+

I'd like to be more precise/efficient than LIKE with the already 'concat' subject. Most of my attempts end up with no results in Room (multi-row) or reducing the subject to only the filter keyword.

Update

Here's a test I've been using to compare the actual SQL results from a query to what Android Room ends up with:

http://sqlfiddle.com/#!7/0ac11/10/0

That join query is interpreted as four objects in Android Room, so I'm trying to reduce the rows, but retain the full subject results while filtering for any image containing the subject keyword.

Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) — CL., Mar 07 '18 at 17:39

score 0 · Answer 1 · answered Mar 07 '18 at 03:38

0

If you want multiple keywords, then where and group by and having can be used:

select image_id
from image_subject
where subject_id in ('a', 'b', 'c')  -- whatever
group by image-id
having count(distinct subject_id) = 3;  -- same count as in `where`

answered Mar 07 '18 at 03:38

Gordon Linoff

1,242,037
58
646
786

Sorry I wasn't clear. I think that's looking for matching multiple (all or none) subjects. If I'm misreading that sorry. I'm matching any image that has any subject I'm filtering for (doesn't need to have all of them). The issue (i think) I'm having is that Android Room (ORM) seems to need the object represented by a single row, so I need a match to `Yosemite` to result in a row with "National Park, Yosemite, etc.". Or maybe group by is doing that magic there and some sort of having > 1 would work? – Anthony Mar 07 '18 at 15:52
So playing with that query a bit are you implying I would use that query to create a `where meta.id IN ([your query result])` in a second query since that's given me an id result set? In other words, is this going to be a two transaction process? – Anthony Mar 07 '18 at 18:21

score 0 · Answer 2 · answered Mar 08 '18 at 18:02

This gets the result I need, though I'd love to hear a better option if this is particularly inefficient.

SELECT meta.*, 
  (SELECT GROUP_CONCAT(name)
    FROM junction
    JOIN subject 
      ON subject.id = junction.subjectId
      WHERE junction.metaId = meta.id) AS keywords,
  junction.subjectId
FROM meta
LEFT JOIN junction ON junction.metaId = meta.id
WHERE subjectId IN (1,2)
GROUP BY meta.id

+----+----------+------------------------+-----------+
| id |   name   |        keywords        | subjectId |
+----+----------+------------------------+-----------+
|  1 | yosemite | National Park,Yosemite |         2 |
|  2 | bryce    | National Park          |         1 |
+----+----------+------------------------+-----------+

http://sqlfiddle.com/#!7/86a76/13

Query M:N contains

Further details

Update

2 Answers2