Please help me figure out how to implement sorting by popularity (reviews) from two tables. Moreover, that all records from the first table are displayed.

For example, there is a table me_cars . During output, you can change the sorting (asc, desc):

$order == "desc") { $this->db->order_by('me_cars.item_title', 'desc'); }

In the example above, everything works as it should, all records (5pcs) are displayed and sorted by title.

The question itself is how can you sort by reviews that are in another table me_cars_reviews while showing all the records (5pcs) from the first table, but considering the sorting from the second table?

Me_cars table

 | item_id | item_title | +---------+-------------+ | 1 | Title item | +---------+-------------+ | 2 | Title item | +---------+-------------+ | 3 | Title item | +---------+-------------+ | 4 | Title item | +---------+-------------+ | 5 | Title item | +---------+-------------+

item_id - vehicle ID

item_title - car name

Me_cars_reviews table

 | review_id | review_itemid | review_overall | +-----------+---------------+----------------+ | 1 | 4 | 8 | +-----------+---------------+----------------+ | 2 | 4 | 6 | +-----------+---------------+----------------+ | 3 | 1 | 7 | +-----------+---------------+----------------+ | 4 | 4 | 3 | +-----------+---------------+----------------+ | 5 | 4 | 1 | +-----------+---------------+----------------+

review_id - review ID

review_itemid - car ID from the first table

review_overall - assessment

I tried to do this:

 $order == "popular") { $this->db->select('me_cars.item_id,me_cars_reviews.review_overall,pt_reviews.review_itemid'); $this->db->select_avg('pt_reviews.review_overall', 'overall'); $this->db->order_by('overall', 'desc'); $this->db->group_by('me_cars.item_id'); $this->db->join('me_cars_reviews', 'me_cars.item_id = me_cars_reviews.review_itemid'); }

With this implementation, everything works (sorts and displays), but displays only those records that are present in the second table me_cars_reviews, i.e. displays only 2 entries (where review_itemid = 1 and 4). Example:

1 record (4 item_id) (by most estimates)

2 records (1 item_id)

doesn't show anything else

It is necessary to display all 5 records (>) from the first table, but taking into account the second, i.e. the output should be like this:

1 record (4 item_id)

2 records (1 item_id)

3 entry - the rest without reviews

4 entry - the rest without reviews

5 entry - the rest without reviews

etc. (if there are more records in the me_cars table)

ps 5 entries just for example. The table can be 100, 200, etc.

Thank.

    2 answers 2

    You can like this :

     SELECT c.`item_title` AS Title, COALESCE(SUM(r.`review_overall`),0) AS Overall FROM me_cars AS c LEFT OUTER JOIN me_cars_reviews AS r ON c.`item_id` = r.`review_itemid` GROUP BY c.`item_id` ORDER BY Overall DESC

    If the “points” need not be summed up, but simply count the number of ratings - in the third line of the SUM query, change to COUNT .

    To understand, read the memo on associations - once did for yourself.

    • Thank. Please tell me how can I convert this into a CI3 request? I can not understand what needs to be replaced in my code. - kate
    • Alas, I do not know this framework. Look at examples, for example here - stackoverflow.com/questions/11821687/… - Majestio
    • In your code, the explicitly field me_cars_reviews.review_overall specified in the me_cars_reviews.review_overall . This does not have to be correct, because on it should go group operations. - Majestio
     SELECT item_title FROM me_cars mc LEFT JOIN me_cars_reviews mcr ON mc.item_id = mcr.review_itemid ORDER BY mcr.review_overall DESC

    skillz.ru/dev/php/article-Obyasnenie_SQL_obedinenii_JOIN_INNER_OUTER.html - a very clear article about join-ah