How can i find a row that must have N > 1 associations as a condition avoiding N+1 queries

Given the following table:

id  category_id categorizable_id  categorizable_type
66  22          67                Image
72  22          75                Image
74  15          71                Image
104 15          67                Image
126 15          113               Image

Now i want to select those "Images" via a left join who's category_ids are 22 and 15 My sql currently looks like this:

SELECT images.id FROM images 
  LEFT OUTER JOIN category_links 
    ON images.id = category_links.categorizable_id AND category_links.categorizable_type = 'Image' 
    where 
      category_links.category_id = "22" AND
      ( 
        select count(*) from category_links 
          where category_links.categorizable_id = images.id AND 
          category_links.categorizable_type = 'Image' AND 
          category_links.category_id = "15"
      )

How can this be rewritten eliminating the need for a subquery ?

Answers


You won't be able to do this efficiently without a subquery, but the situation has room for improvement:

SELECT * FROM
    (SELECT images.id, COUNT(l.categorizable_id) AS occurrences
     FROM images i
     LEFT OUTER JOIN category_links l
     ON i.id = l.categorizable_id AND l.categorizable_type = 'Image'
     WHERE l.category_id IN (15, 22)
     GROUP BY l.categorizable_id) temp
WHERE occurrences = 2;

The main advantage here is that it's trivially easy to configure this query: you specify which categories you are searching for with IN(...) and how many of these categories have to match with the occurrences = N test.

The above query will thus return all images which are in both categories; if occurrences >= 1 it would return images in either category or both; etc.

You can lose the subquery if you use HAVING instead of WHERE for the occurrences check, but this is not usually a good idea because in essence it filters the results of the subquery (as written above) row-by-row and as a consequence performance suffers.


If you want distinct image ids then you can use distinct.

SELECT distinct images.id, 
COUNT(links.categorizable_id) AS occurrences
FROM images i
LEFT OUTER JOIN category_links links
ON i.id = links.categorizable_id AND links.categorizable_type = 'Image'
WHERE links.category_id IN (15, 22)
GROUP BY links.categorizable_id) temp
Having Count(links.categorizable_id) >= 2;

SELECT
    a.id
FROM
    images a
INNER JOIN
    category_links b ON a.id = b.categorizable_id
WHERE
    b.categorizable_type = 'Image'
    AND 
    b.category_id IN (22, 15)
GROUP BY
    a.id
HAVING
    COUNT(*) = 2

Need Your Help

pthreads processes not showing up in ps output

c++ pthreads

I'm trying to use pthreads to create two new processes which each use a file descriptor to either read or write from a pipe.

show the bean proprty value in jsp page

jsp

what is the way to shoe the value in jsp page on browser..