5

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?

Francesco de Guytenaere
  • 4,443
  • 2
  • 25
  • 37
Gaurav Mehta
  • 1,103
  • 4
  • 16
  • 27

1 Answers1

7

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
Jeff
  • 24,623
  • 4
  • 69
  • 78
  • Awesome. This solves the problem. However, wouldn't this approach involve sending a lot of data to the view especially in scenarios where say a deal has 10,000 votes. Would this have a performance impact if the votes and clicks are a huge number for each deal and assuming we have atleast 100 deals for the merchant? – Gaurav Mehta Mar 10 '16 at 03:21
  • 3 options - 1 is to send all that data to your view. 2 is to create a custom `voteCount` attribute like here: https://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ . This does result in 100 DB queries though in your situation. 3rd option is to run a separate query to get the vote count of all the deals (at once) and then send that data to your view separately. – Jeff Mar 10 '16 at 04:02
  • Awesome. Thank you for your response. It is really helpful. – Gaurav Mehta Mar 10 '16 at 05:19