通过值的总和基于另一个表对表数据进行排序

Question

I was wondering if its possible to sort a table based on the sums of values in another table. The issue im facing is the following: im running this query on my database:

<?php
$sql = "SELECT thread_id, SUM(upvoted) AS upvoted FROM upvotes GROUP BY thread_id ORDER BY upvoted LIMIT :start, {$number_threads}";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':start', (int) $start, PDO::PARAM_INT);
$stmt->execute();

Thereafter im making this while loop

while($result = $stmt->fetch()) {
    // do something with the following variables
    $result['thread_id'];
    $result['thread_content'];
    $result['thread_ownerid'];                        
}

But the columns thread_id, thread_content and thread_ownerid do not exist inside the table upvotes. They only exist inside a table called threads. The most straightforward solution to this problem is joining. However, the problem with joining is that the values in the table upvotes are all non-unique. so a thread_id that is unique in threads can for instance be present in 5432 rows with corresponding -1 en 1 values in the column upvoted in the table upvotes.

My idea to go around this problem is: Make an array/dictionary with indices/keys that link the thread_id with the sum of all its upvotes and then join this array/dict with the table threads.

This is one theoretical way of accesing the desired variables, but any way that allows me to acces them will be greatly appreciated.

Thanks a lot in advance for investing time in my question!

Answer 1

Yes, you can do it, but you have to add this value as a new column in your select result like this:

select a.id,a.other,
        (select count(*) 
         FROM table2 AS B 
         WHERE b.id=a.id) AS c
FROM table1  AS a
ORDER BY c;

The C value would be the count (or sum) of some values in other table