How to get a list of similar items

Question

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.

can you please show your data and your desired result data.That will be easy to look up your requirements. — Ankit Agrawal, Feb 12 '16 at 11:20
If you are using Laravel, have you created Eloquent models for these entities and defined their respective `hasOne`, `belongsTo`, `hasMany` etc... relationships? Then you can load the data you need without any query. If you haven't, read up on this: https://laravel.com/docs/5.1/eloquent — Latheesan, Feb 12 '16 at 11:21
@Latheesan yes, Eloquent models are added, but. Please read the task carefully. Laravel doesn't support the feature I need. Or if it does, please tell me in answer — MurDaD, Feb 12 '16 at 11:29
Gonna need a definition of 'similar'.. Do you want all the attributes for all the items for a particular city? — Arth, Feb 12 '16 at 11:30
@Arth please, see the example. Yes, it will be a list of items with at least one equal attribute — MurDaD, Feb 12 '16 at 11:31
@MurDaD Ah ok, so all the items with their attributes for the same city as an example item as long as each item has at least one attribute that appears in the example.. got it — Arth, Feb 12 '16 at 11:34

Arth · Accepted Answer · 2016-02-15T10:28:41.200

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

Thanks, that work. but an error in EXIST(SELECT 1 FROM item_attribute ia1 JOIN item_attribute ia2 ON ia2.attribute_id = ia1.attribute_id AND ia2.item_id = ia1.item_id WHERE ia1.item_id = :item_id /* Pass in item id variable */) — MurDaD, Feb 12 '16 at 12:14
Well what's the error? The comment (My phpmyadmin doesn't like them)? Did you write EXIST/EXISTS? — Arth, Feb 12 '16 at 13:06
I mean, "AND ia2.item_id = ia.id" mus be replace with "AND ia2.item_id = ia1.item_id" — MurDaD, Feb 12 '16 at 14:51
@MurDaD That doesn't sound right, it has to related to the outer record otherwise it'll always return true — Arth, Feb 12 '16 at 15:08
than, it's error in this line, cause it says "ia.id - column not found", but it's there. Definetly. But I dont understand, why do we need id of this table? It's service table — MurDaD, Feb 12 '16 at 17:42
can you help me to sort this list? The more similar skills item has, the higher position it owns — MurDaD, Feb 13 '16 at 14:55

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

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.

edited May 23 '17 at 12:17

Community

1
1

answered Feb 12 '16 at 11:47

Kamaldeep singh Bhatia

732
6
18

How to get a list of similar items

2 Answers2