CODEWITHSUNDEEP
phpmysql
max
max

Reputation: 3716

getting similar images by tag , but filtering images with certain category

i have 3 tables

images

    id , name 
    1  , abc

image_tags 

    id , image_id , tag
    1  , 1345     , football
    2  , 1345     , sport
    3  , 1666     , football

image_categories

    id , image_id , cat

    1  , 1345     , 7
    2  , 1345     , 3
    3  , 1666     , 8

in my query , i have a array of tags and i want to get similar images

it's easy ( i just wrote this pleas don't mention the syntax errors ) 

select distinct(image_tags.image_id) , images.* from 
image_tags join images on image_tags.image_id = images.id 
where image_tags.tag IN (implode(',' , $tag_Array )) limit 10

but here's the tricky part i don't want the images with category 3 ! how can i filter images with category 3 in the same query ?

Upvotes: 0

Views: 33

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

JOIN that table image_categories then you can filter by category cat:

SELECT DISTINCT 
  it.image_id, 
  i.* 
FROM image_tags AS it
INNER JOIN images           AS i ON it.image_id = i.id 
INNER JOIN image_categories AS c ON c.image_id  = i.id
WHERE it.tag IN (implode(',' , $tag_Array )) 
  AND c.cat <> 3
LIMIT 10;

See it in action here:

Update

Use the NOT IN predicate or LEFT JOIN:

Select distinct 
  it.image_id,
  i.* 
from image_tags AS it
INNER JOIN images           AS i ON it.image_id = i.id 
WHERE it.tag IN (implode(',' , $tag_Array ))
  AND it.image_id NOT IN(SELECT image_id 
                         FROM image_categories
                         WHERE image_id <> 3
                           AND image_id IS NOT NULL)
  limit 10;

See it in action here:

Upvotes: 1

Related Questions