CODEWITHSUNDEEP
mysqlsql
Zach S
Zach S

Reputation: 23

MySQL Query show results based on multiple filters/tags

This has been asked in different ways before, but I can't seem to get something that works for what I need exactly.

The goal here is to make a search query that returns Photos based on tags that are selected. Many tags can be applied to the filter simultaneously, which would need to make it so that the query only returns photos that have ALL of the tags selected. Think of any major web shop where you are narrowing down results after performing a basic keyword search.

Table1: Photos ID|Title|Description|URL|Created

Table2: PhotosTagsXref ID|PhotoId|TagId

Table3: PhotosTags ID|Title|Category

What I have:

SELECT p.* FROM `PhotosTagsXref` AS pt
LEFT JOIN `Photos` AS p ON p.`ID` = pt.`PhotoId`
LEFT JOIN `PhotosTags` AS t ON pt.`TagId` = t.`ID`
WHERE p.`Description` LIKE "%test%" AND 
????
GROUP BY p.`ID`
ORDER BY p.`Created` DESC LIMIT 20

The ???? is where I've tried a bunch of things, but stumped. Problem is I can easily find a result set that contains photos with one tag or another, but if applying 2, 3, or 4 tags we'd need to only return photos that have entries for all of those tags in the database. I think this will involve combining result sets but not 100% sure.

Example: Photo 1 Tags: Blue, White, Red Photo 2 Tags: Blue

Searching for a photo with tags of 'blue' returns both photos, searching for a photo with tags of 'blue' and 'white' returns only Photo 1.

Upvotes: 2

Views: 2100

Answers (3)

Twelfth
Twelfth

Reputation: 7190

SELECT p.* FROM `PhotosTagsXref` AS pt
LEFT JOIN `Photos` AS p ON p.`ID` = pt.`PhotoId`
LEFT JOIN `PhotosTags` AS t ON pt.`TagId` = t.`ID`
inner join (select PhotoId from PhotosTagsXref 
             LEFT JOIN `PhotosTags` AS t 
             ON pt.`TagId` = t.`ID`
             where (t.title = 'cond 1' or t.title = 'cond 2' ...)
             --where t.title in (list condition)    **this works as well**
             having count(1) = (count of conditions) ) filter
  on filter.photoID = pt.PhotoID
WHERE p.`Description` LIKE "%test%"
GROUP BY p.`ID`
ORDER BY p.`Created` DESC LIMIT 20

That should work, I made some assumptions on what column to use for the filter and joins, you may need to retool...the inner join functions as a filter and should pull out only records that have the number of matches equal to the total of the number of matches submitted. Now you just need a language to plug in those conditions and condition count values.

Upvotes: 0

geomagas
geomagas

Reputation: 3280

Supposing the requested set of tags is (red,blue) you can do:

SELECT * FROM `Photos` 
WHERE `Description` LIKE "%test%" 
AND `ID` IN (
  SELECT pt.`PhotoId` FROM `PhotosTagsXref` AS pt
  JOIN `PhotosTags` AS t ON pt.`TagId` = t.`ID`
  WHERE t.Title in ('red','blue') /* your set here */
  GROUP BY pt.`PhotoId` HAVING COUNT(DISTINCT t.`TagId`)=2 /* # of tags */
  )
ORDER BY `Created` DESC LIMIT 20

Apparently, the tag set needs to be created dynamically, as well as its count.

Note: I'm counting DISTINCT TagIDs because I don't know your table's constraints. If PhotosTagsXRef had a PK/UNIQUE (PhotoId,TagId) and PhotosTags had a PK/UNIQUE (TagId), then COUNT(*) would suffice.

Upvotes: 1

asantaballa
asantaballa

Reputation: 4048

Admittedly a bit ugly. But assuming that PhotosTags.Category has the 'Blue', 'White', etc, try something along this line.

SELECT p.* 
From `Photos` AS p
WHERE p.`Description` LIKE "%test%" AND 
  AND Exists
      ( Select 1 FROM `PhotosTagsXref` AS pt
        Inner JOIN `PhotosTags` AS t ON pt.`TagId` = t.`ID`
        Where pt.`PhotoId` = p.`ID`
          And t.Category = 'FirstCatToSearch'
      )   
  AND Exists
      ( Select 1 FROM `PhotosTagsXref` AS pt
        Inner JOIN `PhotosTags` AS t ON pt.`TagId` = t.`ID`
        Where pt.`PhotoId` = p.`ID`
          And t.Category = 'SecondCatToSearch'
      )   
  AND Exists
      ( ...
      )   
      ...

Upvotes: 0

Related Questions