php - 按收藏夹表对 MySQL 表行进行排序

Question

我已经有了一个高级 mysql 查询。按用户喜爱的上传内容、搜索关键字和日期范围过滤结果。

现在我需要在此基础上添加另一个排序。我想按最喜欢的顺序订购这整件事。大多数收藏夹是通过计算 favorites 表中 upload_ids 的出现次数来计算的。它应该按照最喜欢的顺序排序，last_update。

我已经研究了 mysql 查询的 COUNT() 元素，但很难理解如何在这个已经很高级的语句构造中获得它......也因为最喜欢的事情发生在另一个表中。

我有三个 mysql 表:+ 用户(ID、日期、用户、密码)+ 上传(id、user_id、日期、last_update、描述、标签、online_state)+ 收藏夹(id、日期、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

如果我理解正确的话，您需要计算每个上传 ID 的收藏夹数量。您可以通过聚合和连接来做到这一点:

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

然后，在查询末尾添加:

ORDER BY f.num desc

获得您想要的订单。请注意，SQL 语句的顺序很重要，因此 order by 必须位于 where 之后。