One result from Count Distinct on multiple tables

BTW this is my full query at the moment

 $query = Listing::query();

return $query
        ->leftJoin('users', 'user_id', '=', 'users.id')
        ->leftJoin('tracks', 'tracks.listing_id', '=', 'listings.id')
        ->leftJoin('votes', 'votes.listing_id', '=', 'listings.id')
        ->selectRaw('listings.*,
                    users.name,
                    count(DISTINCT tracks.id) as tracks,
                    count(DISTINCT votes.id) as votes,
                    count(DISTINCT tracks.user_id + votes.user_id) as contributors
                    ')
        ->groupBy('listings.id')
        ->orderBy($sortBy,$descending)
        ->paginate( $limit );

It gives me sortable tracks and votes count but a wrong contributors count as described above

It looks like this is a quite complex SQL issue. Take a look at this similar question: https://stackoverflow.com/q/29986466/4848587

Hehe, yeah, I only ask complex issues. I can do the simple once or find a solution for those ;-) I usually take a couple of hours of searching before I post a question.

Thanks for the link. I noticed before it might have something to do with UNION but I'm still unable to incorporate it on the flow that I have.

Not the most important part of my project. I'll come back to it later.

This monstrosity works for me:

select *,
  (
    select count(*)
    from (
      select listing_id, user_id from tracks group by listing_id, user_id
      union
      select listing_id, user_id from votes group by listing_id, user_id
    ) alias
    where listing_id = listings.id
  ) contributors
from listings

Thanks! Really appreciate your time and effort but I'm unable to incorporate that into what I already have. Or even make it work on it's own in my controller

What database are you using? I tested it on MySQL: https://www.db-fiddle.com/f/5Ys2UtdtJ4rtrKURMm5tyj/0

MySQL but the problem is that I don't know how to incorporate your code in the Laravel/Eloquent code I already have working (see a couple of messages above).

BTW: I can also not see the DB-Fiddle

Does the query work in your database?

Is the fiddle empty?

I'm sorry, I didn't realise I could test the query in PHPMyAdmin. Still learning every day. When I do that it seems to work indeed.

The link to the fiddle redirects to an empty fiddle: https://www.db-fiddle.com/

What does your query code look like at the moment? What exactly isn't working?

I posted it a couple of messages above but here it is again:

$query = Listing::query();

return $query
        ->leftJoin('users', 'user_id', '=', 'users.id')
        ->leftJoin('tracks', 'tracks.listing_id', '=', 'listings.id')
        ->leftJoin('votes', 'votes.listing_id', '=', 'listings.id')
        ->selectRaw('listings.*,
                    users.name,
                    count(DISTINCT tracks.id) as tracks,
                    count(DISTINCT votes.id) as votes,
                    count(DISTINCT tracks.user_id + votes.user_id) as contributors
                    ')
        ->groupBy('listings.id')
        ->orderBy($sortBy,$descending)
        ->paginate( $limit );

The line for "contributors" is the one that doesn't work. I expanded the "selectRaw" code for some more results with a couple of similar counts but nothing relevant for this issue.

What I meant: What does your approach to integrating my query look like?

It works for me if I replace count(DISTINCT tracks.user_id + votes.user_id) as contributors with (select count(*) from [...] where listing_id = listings.id) as contributors

I tried some combinations which resulted in MySQL errors but not the one you've suggested. Indeed this seems to work. Eventually it was a matter of brackets

Honestly, I kind-of gave on on it myself. Your patience is super natural. Thanks you so much.