php – 如何获取类似项目的列表

发布时间：2020-12-13 16:03:48 所属栏目：PHP教程来源：网络整理

导读：我有4张桌子：项目 +----+------+---------+-----+| id | name | city_id | ... |+----+------+---------+-----+ 属性 +----+------+-----+| id | name | ... |+----+------+-----+ item_attribute +----+---------+--------------+| id | item_id | attrib

我有4张桌子：

项目

+----+------+---------+-----+
| id | name | city_id | ... |
+----+------+---------+-----+

属性

+----+------+-----+
| id | name | ... |
+----+------+-----+

item_attribute

+----+---------+--------------+
| id | item_id | attribute_id |
+----+---------+--------------+

市

+----+------+-----+
| id | name | ... |
+----+------+-----+

项目和属性具有多对多关系.

物品仅位于一对多的城市中

题：

我正在使用php(Laravel).如何在一个城市中为一个具有相似属性的项目获取项目列表(带LIMIT)？属性列表永远不等于2个项目.

是否可以使用MySQL查询？

例：

| ItemName | Attributes            | City |
+----------+-----------------------+------+
| Alpha    | one,two,three,four | NY   |
| Beta     | five,six,seven      | NY   |
| Gamma    | one,seven     | NY   |
| Delta    | one,eight       | CA   |
| Epsilon  | two,four      | NY   |
| Zeta     | ten,nine             | NY   |

我想为Alpha选择类似的项目,它们将是：Gamma,Epsilon,因为它们具有相似的属性.

Delta将不会被选中,因为它位于另一个城市.

解决方法

如果你同时传入了item_id和city_id：

SELECT i.name,GROUP_CONCAT(a.name) attributes,c.name
     FROM items i
     JOIN city c
       ON c.id = i.city_id
     JOIN item_attribute ia
       ON ia.item_id = i.id
      AND EXISTS (
       SELECT 1 
         FROM item_attribute ia1 
         JOIN item_attribute ia2
           ON ia2.attribute_id = ia1.attribute_id
          AND ia2.item_id = ia.item_id
        WHERE ia1.item_id = :item_id /* Pass in item id variable */
              )
     JOIN attributes a
       ON a.id = ia.attribute_id
    WHERE i.city_id = :city_id /* Pass in city id variable */
 GROUP BY i.name,c.name

如果你只是想传递示例项id :(有点草率,但应该工作)

SELECT i.name,c.name
     FROM items base
     JOIN items i
       ON i.city_id = base.city_id
     JOIN city c
       ON c.id = i.city_id
     JOIN item_attribute ia
       ON ia.item_id = i.id
      AND EXISTS (
       SELECT 1 
         FROM item_attribute ia1 
         JOIN item_attribute ia2
           ON ia2.attribute_id = ia1.attribute_id
          AND ia2.item_id = ia.item_id
        WHERE ia1.item_id = base.id
              )
     JOIN attributes a
       ON a.id = ia.attribute_id
    WHERE base.id = :item_id /* Pass in item id variable */
 GROUP BY i.name,c.name

**更新**

排序：

... 
JOIN (
       SELECT ia2.item_id,COUNT(*) count 
         FROM item_attribute ia1 
         JOIN item_attribute ia2
           ON ia2.attribute_id = ia1.attribute_id
          AND ia2.item_id = ia1.item_id
       /* AND ia2.id != ia1.id /* If you don't want the original item */
        WHERE ia1.item_id = base.id
     GROUP BY ia2.item_id
     ) similar
  ON similar.id = ia.item_id
 ...
ORDER BY similar.count DESC

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!