withCount() - get difference of 2 counts

Question

I have a table that has a column is_up_vote = true|false

I am trying to write a query using withCount() so it returns me the sum of true values and false values.

select COUNT(is_up_vote) from comment_votes WHERE is_up_vote = true returns 17

select COUNT(is_up_vote) from comment_votes WHERE is_up_vote = false returns 15

However I couldn't figure out how to get different of them, so the total returns 2.

What I tried is:

Model::withCount(['votes' => function($q) {
 $q->selectRaw(
    '(SUM (COUNT(is_up_vote) WHERE is_up_vote = true) - (COUNT(is_up_vote) WHERE is_up_vote = false) )'
 );
}]);

But this returns 17+15 = 32

without SUM(), it's also returning 32.

$q->selectRaw(
   '( (COUNT(is_up_vote) WHERE is_up_vote = true) - (COUNT(is_up_vote) WHERE is_up_vote = false) )'
);

What am I doing wrong?

Edit:

If I try one side, it ignores the where and still returns 32, so the where is not getting called (where it does in sql)

return $query->withCount(['votes' => function($q) {
   $q->selectRaw('(COUNT(is_up_vote) WHERE is_up_vote = true)');
}]);

maunal sequel query returns 17:

select COUNT(is_up_vote) from comment_votes WHERE is_up_vote = true

---

Edit 2:

$query->withCount([
    'votes as up_votes_count' => function($q) {
        $q->where('is_up_vote', true);
    },
    'votes as down_votes_count' => function($q) {
        $q->where('is_up_vote', false);
    },
]);

The only thing that I could make work is this, but it'd need extra step for getting the total, so I didn't really like this approach. I'm sure someone more proficient with queries can come up with something with direct query.

Answer 1

Try this.

select 
sum( if(is_up_vote= true, 1, 0) ) as uptrue, 
sum( if(is_up_vote= false, 1, 0) ) as upfalse,
sum( if(is_up_vote= true, 1, -1) ) as updiff
from comment_votes

Edit: 1

Instead of checking if it is true or false, try this.

select 
sum( if(is_up_vote, 1, 0) ) as uptrue, 
sum( if(is_up_vote, 0, 1) ) as upfalse,
sum( if(is_up_vote, 1, -1) ) as updiff
from comment_votes

Answer 2

create table comment_votes
(
    is_up_vote tinyint(1) default 1 null
);

insert into comment_votes values (true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true),(true);
insert into comment_votes values (false),(false),(false),(false),(false),(false),(false),(false),(false),(false),(false),(false),(false),(false),(false);

select COUNT(is_up_vote) from comment_votes WHERE is_up_vote = true;
returns 17

select COUNT(is_up_vote) from comment_votes WHERE is_up_vote = false;
returns 15

select (abs(2 * sum(is_up_vote) - count(*))) from comment_votes;
returns 2

Explain:

trueValues + falseValues = total
falseValues = total - trueValues
trueValues - falseValues = x
trueValues - (total - trueValues) = x
2 * trueValues - total = x

trueValues = sum(is_up_vote) [only trues stored as 1 are counted]
total = count(*)

Abs function is to convert the result to positive in case of negative

Isn't it correct?

Answer 3

According to the instructions commented by @Jefferson Pessanha, just using Model::selectRaw('(abs(2 * sum(is_up_vote) - count(*)))')->get(); would be enough

Answer 4

withCount may not be able to handle this case. what about using addSelect to calculate the votes count.

return Model::addSelect(['votes_count' => Vote::selectRaw('( (COUNT(is_up_vote) WHERE is_up_vote = true) - (COUNT(is_up_vote) WHERE is_up_vote = false) )')
    ->whereColumn('parent_id', 'parents.id')
])->get();

I don't know whether the sub-query is gonna work. But you know the idea.