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!
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