php - MySQL INNER 加入 RIGHT 加入和多条件 WHERE 子句与 FULL TEXT

Question

我有三个表:

Meta
Posters
Tags

Posters 在 id 和 Meta 上有一个多对一的关系。 Tags 在 poster_id 上与 Posters 具有多对一关系。

Meta <-- many-to-one Posters <-- many-to-one Tags

所以一个 Meta item 可以有很多海报，一个海报可以有很多标签。

我正在尝试创建一个可以接受条件过滤器(包括特定 Tag 类型)的 SINGLE 查询。

function get_filter_posters($filters, $start) {
      $query = "SELECT Meta.id, Meta.year, Posters.id AS poster_id, Posters.filename, Meta.imp_title
                FROM Meta INNER JOIN Posters ON Meta.id = Posters.movie_id
                RIGHT JOIN Tags ON Posters.id = Tags.poster_id
                WHERE Meta.year >= :lower
                AND Meta.year <= :upper ";

      foreach ($filters["genre"] as $key => $value) {
        if($value){ $query .= " AND Meta.genre = '".$key."' "; }
      }

      foreach ($filters["style"] as $key => $value) {
        if($value){ $query .= " AND Tags.tag = '".$key."' "; }
      }

      $query .= "ORDER BY Meta.year DESC
                 LIMIT :start,:limit";

      $statement = $this->db->prepare($query);
      $statement->bindParam(':limit', $this->limit, PDO::PARAM_INT);
      $statement->bindParam(':start', $start, PDO::PARAM_INT);
      $statement->bindParam(':lower', $filters["year_range"][0], PDO::PARAM_INT);
      $statement->bindParam(':upper', $filters["year_range"][1], PDO::PARAM_INT);
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);
 }

这仅在 SINGLE 样式元素为真时有效。如果发现不止一个样式元素为真，则添加一个额外的 where 子句:

      foreach ($filters["style"] as $key => $value) {
        if($value){ $query .= " AND Tags.tag = '".$key."' "; }
      }

然后当实际上有一个带有多个标签的海报时，查询总是返回一个空集。

如果在编写查询时仅将单个样式元素设置为 true，则查询会返回正确的海报。多于一个，总是返回空，实际情况并非如此。

这也将通过文本搜索进行扩展。这个根本不能按预期工作。

$query = "SELECT Meta.id, Meta.year, Posters.id AS poster_id, Posters.filename, Meta.imp_title,
              MATCH(".$search_type.") AGAINST($term) AS Relevance
              FROM Meta INNER JOIN Posters ON Meta.id = Posters.movie_id
              RIGHT JOIN Tags ON Posters.id = Tags.poster_id
              WHERE MATCH(".$search_type.")
              AGAINST($term IN BOOLEAN MODE)";

      foreach ($filters["genre"] as $key => $value) {
        if($value){ $query .= " AND Meta.genre = '".$key."' "; }
      }

      foreach ($filters["style"] as $key => $value) {
        if($value){ $query .= " AND Tags.tag = '".$key."' "; }
      }

      $query .= "AND Meta.year >= ".$filters["year_range"][0]."
                 AND Meta.year <= ".$filters["year_range"][1]."
                 ORDER BY Relevance + Meta.year DESC
                 LIMIT :start,:limit";

      $statement = $this->db->prepare($query);
      $statement->bindParam(':start', $start, PDO::PARAM_INT);
      $statement->bindParam(':limit', $this->limit, PDO::PARAM_INT);
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);

忽略我没有 bindParam 过滤器的事实 - 它会完成

也许我处理这个问题的方式不正确？如果是这样，任何建议都会有很大帮助

谢谢!

Answer 1

右连接被 where 子句 meta.year 语句否定。因此，如果一个标签存在，但没有海报或元数据，它就会被排除(正确的连接本质上被视为内部连接)。要更正，您需要在海报级别或标签右连接处对连接的元部分设置 where 子句限制...

也许

  $query = "SELECT Meta.id, Meta.year, Posters.id AS poster_id, Posters.filename, Meta.imp_title
            FROM Meta 
            INNER JOIN Posters ON Meta.id = Posters.movie_id
            RIGHT JOIN Tags ON Posters.id = Tags.poster_id
             ON Meta.year >= :lower
            AND Meta.year <= :upper ";

或

  $query = "SELECT Meta.id, Meta.year, Posters.id AS poster_id, Posters.filename, Meta.imp_title
            FROM Meta INNER JOIN Posters 
              ON Meta.id = Posters.movie_id
             AND Meta.year >= :lower
             AND Meta.year <= :upper
            RIGHT JOIN Tags ON Posters.id = Tags.poster_id";