The task in my opinion is non-trivial. There are three tables:

  1. user (it stores information about the builders);
  2. sfera (it stores information about the categories in which builders provide their services);
  3. favorites (it stores information about builders that users have added to their favorites, broken down into categories; that is, the same builder can provide services in three categories, but at the same time be added to favorites in only one category).

When we load a catalog of builders in a specific category, for example, “Interior Works”, we select them as follows:

 SELECT sfera.*, user.photo as photo, user.city as city, sfera.additional FROM sfera, user WHERE sfera.is_confirmed = '1' AND sfera.sfera_value = 'Внутренние работы' AND sfera.city_value = 'Москва' AND user.user_id = sfera.user_id ORDER BY rating DESC

Question :
How to add to the results of the request information about whether the builder has a mark of adding to favorites in this category (Internal work)?

The table structure favorites :

 id // id записи person_id // id пользователя, добавившего строителя в избранные user_id // id строителя, добавленного в избранные sferaValue // идентификатор конкретной категории, в которой строитель добавлен в избранные is_favorite // по умолчанию 1
  • Replace , in with a full join and add the left join subquery that receives id from the third id-table, group categories with group by for these fields, i.e. so that he would have no more than one entry per category user - Mike
  • I do not really understand how join requests work, could you reflect the changes on the example of the above request? Thank. - Newcomer
  • join from , no different, only the join condition from where is transferred to on this join - Mike
  • @Mike and from this the request plan does not change? - Roman Danilov
  • usually not. comma is generally a synonym for simple join :) - Mike

1 answer 1

You can simply add this field to the subquery:

 SELECT s.*, u.photo as photo, u.city as city, s.additional, ifnull((select distinct 1 from favourites f where f.sferaValue = s.sfera_value and f.user_id = u.user_id), 0) as is_favourite_flag FROM sfera s join user u on u.user_id = s.user_id WHERE s.is_confirmed = '1' AND s.sfera_value = 'Внутренние работы' AND s.city_value = 'Москва' AND u.user_id = s.user_id ORDER BY rating DESC