How to get all items that are tagged Tag1 AND Tag2 AND … for a 3-table tag solution

So I implemented a 3 table tag solution, als indicated in this classic post (so-called “Toxi” solution). Everthing works splendid, the only thing I struggle with is to get all items that are tagged with more than one tag.

My first solution used IN and worked fine but of course gave me all items that had any of the provided tags. But one I really want is all items that have each of the provided tags.

So I tried to :

    /**
     * GALLERY::filter_by_tags()
     * @param array $tags - an array of strings
     */
    public function filter_by_tags($tags){
        if (count($tags) > 1){
        //do the string building of the query 
        $query = "SELECT DISTINCT m.* FROM fm_maps m
                                INNER JOIN fm_map_tags mt ON mt.map_id = m.id
                            INNER JOIN fm_tags t
                            ON t.id = mt.tag_id WHERE t.tag = ";
        $current_tag_string = "";
        $sql_values = [];                                                                               
        $i = 1;                                                                                                                                         
        foreach ($tags as $tag){
            $current_tag_string = 'tag' . $i;                                                                                   
            $sql_values[$current_tag_string] = $tag;                                                        
            if ($i == 1) {
                $query .= ':' . $current_tag_string;                                                            
            }   else {
                $query .= ' INNER JOIN fm_map_tags mt ON mt.map_id = m.id INNER JOIN fm_tags t
                            ON t.id = mt.tag_id WHERE t.tag = :' . $current_tag_string;                         
            }
            $i++;                                                                                               
        }
        $stmt = $this->map_db_connector->conn->prepare($query);
        $stmt->execute($sql_values);
        $maps = $stmt->fetchAll(PDO::FETCH_CLASS, 'MAP');                                                       
        foreach ($maps as $current_map){
            $this->create_gallery_entry($current_map);
        }
    } else {
        //just use tags[0]
        $stmt = $this->map_db_connector->conn->prepare("SELECT DISTINCT m.* FROM fm_maps m
                                                         INNER JOIN fm_map_tags mt ON mt.map_id = m.id
                                                         INNER JOIN fm_tags t ON t.id = mt.tag_id WHERE t.tag IN (:tag)");
        $stmt->bindValue(':tag', $tags[0]);
        $stmt->execute();
        $maps = $stmt->fetchAll(PDO::FETCH_CLASS, 'MAP');                                                               
        foreach ($maps as $current_map){
            $this->create_gallery_entry($current_map);
        }
    }

That probably unecessarily complex but in principal the whole function works. Just the SQl statement in the first if clause itself is broken. Returns Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN fm_map_tags mt ON mt.map_id = m.id INNER JOIN fm_tags t ON' The else part works fine. I also tried

foreach ($tags as $tag){
            $current_tag_string = 'tag' . $i;                                                                                   
            $sql_values[$current_tag_string] = $tag;                                                        
            if ($i == 1) {
                $query .= ':' . $current_tag_string;                                                            
            }   else {
                $query .= ' AND t.tag = :' . $current_tag_string;                         
            }

which I thought was simpler, but that does not work either. I get no error, but also no results.

As an SQL and PHP beginner I’m stuck. Could anyone point me in the right direction?

Answer

This query will give you fm_maps which contains all the tags A, B and C:

SELECT * FROM fm_maps
WHERE EXISTS (
    SELECT 1 FROM fm_map_tags mt
    JOIN fm_tags t ON mt.tag_id = t.id AND t.tag IN ('A', 'B', 'C') 
    WHERE mt.map_id = fm_maps.id
    GROUP BY mt.map_id
    HAVING COUNT(*) = 3
)

The implementation of the filter_by_tags method with this query could be like this:

/**
* GALLERY::filter_by_tags()
* @param array $tags - an array of strings
*/
public function filter_by_tags($tags)
{
    if (empty($tags)) {
        // Do something if there are no tags

    } else {
        $placeholders = '?' . str_repeat(', ?', count($tags) - 1);
        $query = 
            "SELECT * FROM fm_maps WHERE EXISTS ("
            . "SELECT 1 FROM fm_map_tags mt "
            . "JOIN fm_tags t ON mt.tag_id = t.id AND t.tag IN ($placeholders) "
            . "WHERE mt.map_id = fm_maps.id GROUP BY mt.map_id HAVING COUNT(*) = ?)";

        $params = $tags;
        $params[] = count($tags);

        $stmt = $this->map_db_connector->conn->prepare($query);
        $stmt->execute($params);
        $maps = $stmt->fetchAll(PDO::FETCH_CLASS, 'MAP');
        foreach ($maps as $current_map){
            $this->create_gallery_entry($current_map);
        }
    }
}