php - Mysql查询通过将多个表与映射表组合来获取搜索结果

Question

我有四个表，即company、company_branch、company_category_map 和company_category。 在这里，我可以将公司总部添加到companys表中，如果该公司在同一国家或不同国家有分支机构，则该分支机构将被添加到company_branches表中，并以公司总部id作为外键。由于总公司和分公司有多个类别，因此类别与每个主表各自的id映射到company_category_map表。

当我只使用公司表进行搜索而没有加入分支表时，一切正常。

搜索如下。1.仅用公司名称搜索2. 仅类别搜索3. 仅搜索国家4.使用以上3项的任意组合进行搜索

我需要的是

如果国家或类别属于任一表，并且公司名称不应重复，我应该能够显示公司表中的所有公司名称。

任何想法或帮助将不胜感激。恐怕我无法更改表结构，因为已经插入了数千条记录。但如果需要更改表格，我可以尝试一下而不会丢失数据。

我在不加入company_branches表的情况下尝试过的操作如下。

<?php 
function search_companies($limit,$start,$companyname='',$category_id='',$country_id='') 
    {
        if($category)
        {
            $qry = "SELECT `companies`.* FROM `companies` INNER JOIN `company_category_map` ON (`companies`.`id` = `company_category_map`.`company_id`) INNER JOIN `company_category` ON (`company_category_map`.`category_id` = `company_category`.`id`) WHERE `companies`.`status` =1";
            $where = " and `company_category_map`.`category_id` = $category";
            if($companyname) $where .= ' and `companies`.`company_name` like "%'.$companyname.'%"';
            if($country_id) $where = " and `companies`.`country_id` = $country_id";
        }
        else
        {
            $qry = "SELECT * FROM `companies` WHERE `status` = 1";
            if($companyname) $where .= " AND `heading` LIKE '%".$companyname."%'";
            if($country_id) $where .= " AND `country_id` = $country_id";
        }

        $qry = $qry.$where." limit  $start, $limit";

        $query = $this->db->query($qry)->result();

        if (count($query) > 0) {
            foreach ($query as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
   }
?>

Answer 1

在表company_category_map中，有一列type。这是否用于确定该行是否与公司记录与分支机构记录匹配？

如果是这样，company_category_map 上的连接需要稍作更改。

此 SQL 不处理其他搜索，但它们可以轻松添加到 where 子句中。重点是展示如何在总公司或分支机构上匹配类别。

更新:添加了对空类别的检查，以消除没有匹配类别的行。

更新:添加了对要加入company_category_map的公司类型(主要或分支)的测试。

SELECT 
    a.`id`,
    a.`country_id`,
    a.`company_name`,
    a.`logo`,
    a.`address`,
    a.`phone`
FROM `companies` a
LEFT JOIN `companies_branches` a1
ON a1.`company_id` = a.`id`
LEFT JOIN `company_category_map` b
ON  (a.`id` = b.`company_id` AND b.`type` = 'm') OR 
    (a1.`id` = b.`company_id` AND b.`type` = 'b') AND 
    `b.status` = 1
LEFT JOIN `company_category` c
ON b.`category_id` = c.`id`
WHERE c.`id` = @category_id AND
    (NOT c.`id` IS NULL)
GROUP BY a.`id`;

请务必将您的代码转换为使用带有 mysqli 或 PDO 的准备好的语句。