SQL select and order by (count of related records inside a second table) subtracted by (count of rel-CodePudding

I am trying to SELECT every record in a table one ORDERed BY the COUNT of records inside a second table that store the related primary key value and WHERE 'positive' is true, subtracted by the COUNT of records inside the second table that store the related primary key value and WHERE 'positive' is false.

Here is my database structure

Table 1

id	data
0	zero
1	one
2	two
3	three

Table 2

id	related_tableone_id	positive
0	1	0
1	2	1
2	2	0
3	2	1
4	3	1
5	3	1

Here is what I am trying to get

id	data	subtracted_counts (i dont need this but these values are what the records should be ordered by)
3	three	2
2	two	1
0	zero	0
1	one	-1

For better understanding on what i want to achieve:
This database structure can be compared with a voting system, where Table 1 are entities that can be voted up or voted down.
In this case, Table 2 would store the votes with positive=true for an upvote and positive=false for a downvote.
The goal is to get all entities ORDERed BY their summarized vote value.
(Within a single query)

My research

I found this post SQL - How To Order Using Count From Another Table, tho there is no subtraction logic

I tried this query

SELECT 
    tableone.*, 
    COUNT(related_tableone_id) - COUNT(negative_related_tableone_id) AS subtracted_count
FROM 
    tableone 
LEFT JOIN 
    (SELECT related_tableone_id 
     FROM tabletwo 
     WHERE positive = true) AS positives ON tableone.id = positives.related_tableone_id
LEFT JOIN 
    (SELECT related_tableone_id AS negative_related_tableone_id 
     FROM tabletwo 
     WHERE positive = false) AS negatives ON tableone.id = negatives.negative_related_tableone_id
GROUP BY 
    tableone.id
ORDER BY 
    subtracted_count DESC;

But it doesn't subtract the counts right for some reason and there is probably a more clear solution

CodePudding user response：

Use a LEFT join of Table1 to Table2 and conditional aggregation in the ORDER BY clause:

SELECT t1.id, t1.data       
FROM Table1 t1 LEFT JOIN Table2 t2
ON t2.related_tableone_id = t1.id
GROUP BY t1.id
ORDER BY SUM(CASE t2.positive WHEN true THEN 1 WHEN false THEN -1 ELSE 0 END) DESC;

or, a correlated subquery in the ORDER BY clause (which may perform better):

SELECT t1.*
FROM Table1 t1
ORDER BY (
  SELECT COALESCE(SUM(CASE t2.positive WHEN true THEN 1 WHEN false THEN -1 END) , 0)
  FROM Table2 t2 
  WHERE t2.related_tableone_id = t1.id
) DESC;

See the demo (works in MySql, Postgresql and SQLite).

CodePudding user response：

A single subquery can count both upvotes and downvotes, using conditional aggregation. I would use a lateral join to do the computation:

select t1.*, t2.*
from tableone t1
cross join lateral (
    select 
        sum(case when t2.positive = true  then 1 else 0 end) upvotes,
        sum(case when t2.positive = false then 1 else 0 end) downvotes
    from tabletwo t2
    where t2.related_tableone_id = t1.id
) t2
order by t2.upvotes - t2.downvotes desc, t1.id

Depending on your database, the lateral join might be introduced by cross applyinstead (eg in Oracle or SQL Server).