CODEWITHSUNDEEP
phpmysqllistlaravelgroup-by
MurDaD
MurDaD

Reputation: 362

How to get a list of similar items

I have 4 tables:

items

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

attributes

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

item_attribute

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

city

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

Items and attributes have relations many-to-many.

Item is located only in one city one-to-many

Question:

I'm using php (Laravel). How can I get Items list (with LIMIT) for one Item with similar attributes in one city? Attribute list is never equals for 2 items.

Is it possible to do with MySQL query?

Example:

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

I want to choose similar items for Alpha, they will be: Gamma, Epsilon because they have similar attributes.

Delta won't be chosen, because it's located in another city.

Upvotes: 4

Views: 275

Answers (2)

Arth
Arth

Reputation: 13110

If you have both the item_id and the city_id to pass in:

   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

If you just want to pass the example item id: (A little bit sloppy, but should work)

   SELECT i.name,
          GROUP_CONCAT(a.name) attributes,
          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

** UPDATE **

Ordering:

... 
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

Upvotes: 1

Kamaldeep singh Bhatia
Kamaldeep singh Bhatia

Reputation: 732

You can perform INNER JOINS in all

SELECT I.name,I_A.name,city.name FROM attributes as A
INNER JOIN item_attribute as I_A ON I_A.attribute_id = A.id
INNER JOIN city ON I_A.id = city.id
INNER JOIN items as I ON I.id = I_A.item_id
WHERE <Your condition>

To get comma separated values you can refer here Let me know if I am not getting your point.

Upvotes: 0

Related Questions