0

Apologies if the title is unclear, I'll try to explain it with an example. Let's say I have a file tagged with "trees" and "greenery" and I want to search for it, however when I enter the terms "trees" and "greenery" into the input field it gives me results that are tagged as either one or both of them.

Here is my database structure:

enter image description here

For the query I'm using the IN operator, and it looks something like this:

SELECT path FROM tagged_files INNER JOIN file_tags ON tagged_files.tag_id=file_tags.id INNER JOIN files ON tagged_files.file_id = files.id WHERE file_tags.tag IN ($inQuery) GROUP BY files.id ORDER BY files.id;

The $inQuery variable is defined as: $inQuery = implode(',', array_fill(0, count($tags), '?')); and $tags is a string from the search query, I later bind the values.

How do I get the query to select only files that match ALL of the tags I entered?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
RamenNoodle93
  • 52
  • 5
  • 1
    `when I enter the terms "trees" and "greenery" into the input field` what input field? Also, did you check what `$inQuery` actually is? Does it match what you expected? – Mike 'Pomax' Kamermans Jan 31 '23 at 18:13
  • You have to manually build an `AND` condition, `IN` matches the values with `OR`. – Code Spirit Jan 31 '23 at 18:18
  • For these I'm more partial to building a bunch of `AND` clauses, however there's [some tricks](https://stackoverflow.com/a/11636084/231316) you might be able to do with counting logic if you write your query correctly – Chris Haas Jan 31 '23 at 18:19
  • The input field on my website, I did check and it matches the results I get when I enter the query manually. When I have two tags in the IN operator I get more results than when I enter just one – RamenNoodle93 Jan 31 '23 at 18:19

1 Answers1

0

Okay, I believe I figured it out, adding HAVING COUNT(DISTINCT file_tags.id) = numberOfTags at the end of the query ensures all the terms are being accounted for.

The reason this works is if you select all files with the tags IN("trees","greenery") for example, you get two rows with those tags for each file if that makes sense.

RamenNoodle93
  • 52
  • 5