Find how many shares of a horse a user has? (Postgresql query)

Question

I have four tables: users, orders, shares, and horses.

One user has many orders, one order has many shares, and one horse has many shares.

I want to find how many shares of each horse does a specific user owns (including when a user owns zero shares in a particular horse).

I have tried the following, however I cannot retrieve the cases when a user owns zero shares for a particular horse.

$userHorses = DB::table('orders')->join('shares', 'orders.id', '=', 'shares.order_id')
          ->join('horses', 'horses.id', '=', 'shares.horse_id')
          ->where('orders.user_id',$user_id)
          ->select('horses.name','horses.id','horses.slug',DB::raw('count(*) as totalShares'))
          ->groupBy('horses.name','horses.id','horses.slug')
          ->get();

return $userHorses;

Here is your problem visualized: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — online Thomas, Aug 18 '20 at 14:35

M Khalid Junaid · Answer 1 · 2020-08-18T14:57:20.250

0

To retrieve all horses with user share count you will need left join with conditional aggregation like

$userHorses = DB::table('horses h')
          ->leftJoin('shares s', 'h.id', '=', 's.horse_id')
          ->leftJoin('orders o', 'o.id', '=', 's.order_id')
          ->select('h.name',
            'h.id',
            'h.slug',
            DB::raw('sum(case when o.user_id = :user_id then 1 else 0 end) as totalShares', ['user_id' => $user_id])
          )
          ->groupBy('h.name','h.id','h.slug')
          ->get();

edited Aug 18 '20 at 14:57

answered Aug 18 '20 at 14:25

M Khalid Junaid

63,861
10
90
118

This approach for some reason is not taking into consideration the where clause for a specific user. It is returning the total shares of all users. – Petro Bianka Aug 18 '20 at 14:40
Yes you were correct about forgetting the use keyword, but still I am not getting the desired results. – Petro Bianka Aug 18 '20 at 14:42
only for a specific user – Petro Bianka Aug 18 '20 at 14:42
@PetroBianka I have updated my answer it should give you desired results – M Khalid Junaid Aug 18 '20 at 14:48
No, it still retrieves only the records where the user has shares. It does not return the records where the user does not have any share for a horse. – Petro Bianka Aug 18 '20 at 14:56
@PetroBianka see the updated version with conditional aggregation, hope it works this time – M Khalid Junaid Aug 18 '20 at 14:57
That seems to be the right query because I am getting the desired result. The only thing that is missing is for some reasons I cannot pass the variable user_id inside the DB:raw. I am getting the following error : "bind message supplies 0 parameters". – Petro Bianka Aug 18 '20 at 15:21
I came with a work around. I just passed the variable between two single quotes. – Petro Bianka Aug 18 '20 at 15:37
Direct variable manipulation in query is a bad paractice, If you can find a way around binding parameters it would be good – M Khalid Junaid Aug 18 '20 at 15:56

Find how many shares of a horse a user has? (Postgresql query)

1 Answers1