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?

Jeff · Accepted Answer · 2016-03-10 00:01:51Z

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

answered Mar 10, 2016 at 0:01

Jeff

25.3k4 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gaurav Mehta Over a year ago

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?

Jeff Over a year ago

3 options - 1 is to send all that data to your view. 2 is to create a custom voteCount attribute like here: softonsofa.com/… . 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.

Gaurav Mehta Over a year ago

Awesome. Thank you for your response. It is really helpful.

Collectives™ on Stack Overflow

Get Data from Multiple Tables Laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related