List tables to make recommendations to the customer?

Asked

Viewed 38 times

1

I have a table "likes", "commented" and "visited", each time a user likes, comments or visits a particular book on my page a record is inserted in the database for such:

Table likes: 

+----------------+-------------+
| id_livro       | usuario     |
+----------------+-------------+
|     1          | user_id     |
+----------------+-------------+
|     4          | user_id     |
+----------------+-------------+
|     1          | user_id     |
+----------------+-------------+

Table commented: 

+----------------+-------------+
| id_livro       | comentario  |
+----------------+-------------+
|     1          | coment 1    |
+----------------+-------------+
|     7          | coment 2    |
+----------------+-------------+
|     1          | coment 3    |
+----------------+-------------+

Table visited: 

+----------------+-------------+
| id_livro       | usuario     |
+----------------+-------------+
|     1          | user_id     |
+----------------+-------------+
|     6          | user_id     |
+----------------+-------------+
|     4          | user_id     |
+----------------+-------------+

In the example above we see that the book with id 1 is repeated in the 3 tables and the book with id 4 is repeated in two of them, these in case would be 2 books that I would recommend to customers on a list because apparently are two "popular" books, as I could organize a SELECT to return the books to me so that I can organize them on the page "recommended"

mysql sql select

  • What criteria would you use to show the popular ones? By the diversity of activities? By the amount of activities? Or would limit for example to 2 recommendations?

  • If a book repeats itself in at least 2 tables it is a good book to be recommended the diversity of activities related to it is a good criterion, would be 3 books to be recommended on the page

  • Why 3? In your example I only see 2 meeting the criterion

  • I used as example only 2 that meet the criteria for the question not getting too extensive but I want to recommend 3 books that meet the criteria

1 answer

2


You can add 1 to a column if you find the record in another table and use the sum of the 3 to sort the results:

SELECT l.id_livro,
       IFNULL((SELECT 1
                 FROM curtidos c
                WHERE c.id_livro = l.id_livro
                LIMIT 1), 0) as curtido, -- Coloca 1 se o livro foi curtido ou 0 se não foi
       IFNULL((SELECT 1
                 FROM comentados c
                WHERE c.id_livro = l.id_livro
                LIMIT 1), 0) as comentado, -- Coloca 1 se o livro foi comentado ou 0 se não foi
       IFNULL((SELECT 1
                 FROM visitados v
                WHERE v.id_livro = v.id_livro
                LIMIT 1), 0) as visitado -- Coloca 1 se o livro foi visitado ou 0 se não foi
  FROM livros l
HAVING (curtido + comentado + visitado) > 2 -- Verifica se aparece em pelo menos 2 tabelas
 ORDER BY (curtido + comentado + visitado) DESC -- Ordena pelos que aparecem em mais tabelas
 LIMIT 3 -- Limita em 3 o número de resultados

  • A little bit complete but I was able to understand and apply to my real use, it worked perfectly well. Only one question queries of this type delay page loading or make no difference?

  • Depending on the amount of records can get heavy yes. But with few records will not affect

  • 1

    The book table will have approximately 400 books, already the likes, comments, and visited will depend on the amount of users, but such logs will be cleaned once every 3 months, but I will test the use of this and see if it influences much.

  • Sorack, if I add an ORDER BY in the SELECTS by date, it will favor the most recent books in such tables, right? If not, how can I leave this research a little more dynamic, because I’m testing here and always have the same 3 books.

  • @Leoletto, you can use the ORDER BY which was indicated and after that put the date field: ORDER BY (curtido + comentado + visitado) DESC, data

  • When I do this the search returns no results

  • 1

    @Leoletto actually think you need to open another question with the modifications you need, by the comments will get confused and reopen this question would be wrong

Show 2 more comments

Browser other questions tagged mysql sql select

You are not signed in. Login or sign up in order to post.