CODEWITHSUNDEEP
sqlsqlite
Damian Grzanka
Damian Grzanka

Reputation: 305

Filter based on another table

I am building a tinder-like rating system for images.

I need to find an image that has the nearest rating to the provided image. And the pair of those images are not present in the comparasion table.

The first part is straightforward and looks like this

SELECT images.file_path, images.id
FROM images JOIN ratings ON images.id = ratings.image 
WHERE images.id != ? AND ratings.label = ? 
ORDER BY abs(? - ratings.mu) ASC

And then I have a table containing the following information and I need assure that result of the first query don't produce the pair that is contained in that table

img1: Integer - Image id
img2: Integer - Image id
outcome: Integer - Not relevant to question

Right now I am using SQLite for testing but solution that works on multiples DB's would be great for further development

Example Image table (file_path omitted)

id, label, mu
1, 1, 1.5
2, 1, 2.0
3, 1, 2.5
4, 1, 3.5

Comparison table (outcome ommited)

img1, img2
1, 2
2, 3
3, 4

Example outcome for image with id 1 = img with id 3. Because its rating is closest to 1.5 and can't be image 2 because this pair is inside the comparison table

Upvotes: 1

Views: 47

Answers (1)

LukStorms
LukStorms

Reputation: 29647

You can use a NOT EXISTS to filter out what shouldn't be there.

WITH img AS (
  SELECT id as image_id, label, mu
  FROM images
  WHERE id = 1
)
SELECT 
  images.file_path
, images.id as image_id
, ratings.mu as rating_mu
, ratings.label as rating_label
FROM images 
CROSS JOIN img
INNER JOIN ratings
  ON images.id = ratings.image 
 AND ratings.label = img.label 
WHERE NOT EXISTS (
  SELECT 1
  FROM comparison c
  WHERE images.id in (c.img1, c. img2) 
    AND img.image_id in (c.img1, c. img2)
)
ORDER BY abs(img.mu - ratings.mu) ASC
LIMIT 1;

file_path    | image_id | rating_mu | rating_label
:----------- | -------: | --------: | -----------:
/img/foo.png |        3 |       2.5 |            1

Test on db<>fiddle here

Upvotes: 1

Related Questions