Sorting MySQL table rows by a favorites table

Question

I have an advanced mysql query already. Filtering the results by user favorited uploads, search keywords and date range.

Now I need to add another ordering ontop of this. I want to order this whole thing by most favorites. Most favorites are calculated by counting the occurence of the upload_ids in the favorites table. It should be ordered by most favorites, last_update.

I have looked into the COUNT() element for mysql query, but it's hard to understand how I get it in this already advanced statement construct... Also because the most favorite thing goes on in another table.

I have three mysql tables: + user (id,date,user,pass) + uploads (id,user_id,date,last_update,description,tags,online_state) + favorites (id,date,user_id,upload_id)

$query = 'SELECT * FROM uploads WHERE online_state = 1';//select all online uploads



//FILTER FAVORITES //$favorites_ids are the favorites of the logged in user, being queried in another statement

if  ($upload_display_sort == 'favorites')

$query .= ' AND id IN ('.implode(',', $favorites_ids).')';//returns 1,2,3,4,5



//FILTER SEARCH

if  (   isset($_GET['tags'])    )

$query .= ' AND MATCH (description,tags) AGAINST ("'.urldecode($_GET['tags']).'")';



//FILTER DATE

if  (   isset($_GET['timeframe'])

    and (   $_GET['timeframe'] == '3days'

        or  $_GET['timeframe'] == '2weeks'

        or  $_GET['timeframe'] == '3months')    )

{

    $end_date = time();//current time in unix format

    switch  (   $_GET['timeframe']  )

    {
        case '3days':   $start_date = strtotime('-3 days',$end_date);   break;
        case '2weeks':  $start_date = strtotime('-2 weeks',$end_date);  break;
        case '3months': $start_date = strtotime('-3 months',$end_date); break;
        default:        $start_date = strtotime('');    break;//1970-01-01 01:00:00
    }

    $end_date = date("Y-m-d H:i:s", $end_date);//current time in mysql format

    $start_date = date("Y-m-d H:i:s", $start_date);//end time in mysql format

    $query .= ' AND last_update BETWEEN "'.$start_date.'" AND "'.$end_date.'"';

}

Answer 1

If I understand correctly, you need to count the number of favorites per upload id. You can do this with an aggregation and join:

SELECT u.*
FROM uploads u left outer join
     (select upload_id, count(*) as num
      from favorites f
      group by upload_id
     ) f
     on u.id = f.upload_id
WHERE online_state = 1

Then, at the end of your query, add:

ORDER BY f.num desc

to get the ordering that you want. Note that the order of SQL statements is important, so the order by has to go after the where.