Get Data from Multiple Tables Laravel

Question

I am trying to build a coupon site in Laravel. Each merchant has their own deals/coupons. I have been able to print deals/coupons for a merchant on their specific pages.

Here's my query

$deals = DB::table('deals')
    -> join ('merchants', 'deals.merchant_id', '=', 'merchants.merchant_id')
    -> where ('merchant_url_text', $merchant_url_text)
    -> get();

So far so good.

Now this is where it starts getting complex.

Each deal has 2 more pieces associated with it. Click counts and Votes associated with deals.

The click counts are in a table called clicks which records each click on the website. The click record will have a click id associated it. So I would need to get a count of clicks each deal gets.

The second piece is votes. The votes around a deal are stored in a deal_votes table. The deal_votes table has deal_id, vote (1 or 0)

How do I combine click counts and deal votes to return in the same query so that I can display the info in my view?

Answer 1

Do you have models and relationships set up for merchants, deals, coupons, and clicks? This is trivial if you use Eloquent models with relationships, for which the docs are here: https://laravel.com/docs/5.2/eloquent-relationships

This would look like:

$merchant = Merchant::where('merchant_url_text', $merchant_url_text)
  ->with('deals','deals.votes','deals.clicks')
  ->first();

The with() function adds all of the nested information, ie query joins, into a single query.

In your view:

@foreach($merchant->deals as $deal)

   Deal: {{$deal->name}}

   Clicks: {{count($deal->clicks)}}

   Votes: {{$deal->votes->sum('vote')}}

@endforeach