Optimizing Laravel Query to get required data

Posted 2 years ago by gmehtaster

I have the following models in my Laravel project

Merchant
Deals
Deal Votes
Deal Clicks
Deal Deal Types
Merchant Rating

This is how my Merchants Model looks like

class Merchants extends Model
{
    //
    public function deals()
    {
        return $this->hasMany('App\Deals', 'merchant_id', 'merchant_id');
    }
}

Deals Model

class Deals extends Model
{
//
    public function clicks()
    {
        return $this->hasMany('App\Clicks', 'deal_id', 'deal_id');
    }
    public function merchants()
        {
            return $this->hasOne('App\Merchants', 'deal_id', 'deal_id');
        }
        public function votes()
        {
            return $this->hasMany('App\Deal_votes', 'deal_id', 'deal_id');
        }
        public function deal_dealtypes()
        {
            return $this->hasMany('App\Deal_dealtypes', 'deal_id', 'deal_id');
        }
    }

Merchant Rating Model

    public function merchants()
        {
            return $this->belongsTo('App\Merchants', 'merchant_id', 'merchant_id');
        }

For a specific Merchant Landing page, I am trying to get some Merchant info, deals tied to the merchant, count of votes and clicks for each Deal. This is what I have done so far

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

This however is returning Merchant Info, Deal Info, All the Votes records tied to the Deal and all the clicks tied to each deal.

Attached sample data response I am getting

{
merchant_id: 6605,
master_merchant_id: null,
skimlinks_id: null,
merchant_name: "Groupon",
network: "cj",
network_id: "",
network_notes: null,
dual_network: null,
related_merchants: null,
affiliate_link: "http://api.coupilia.com/merchants/?aid=1423&mid=6605",
skimlinks_url: null,
fmtcurl: null,
merchant_homepage_url: "www.groupon.com?z=dealpage",
status: null,
selected_status: null,
relationship_status: null,
primary_country: "US",
apofpo: null,
primary_category: null,
subcategories: null,
special_payment_options: null,
mobile_certified: null,
logos: "",
custom_logo: "https://res-5.cloudinary.com/dhhntbusx/image/upload/t_media_lib_thumb/v1475206550/Groupon-coupons_mzjdm8.jpg",
custom_description: null,
added: null,
lastupdated: null,
merchant_url_text: "groupon.com",
about: "",
primary_category_id: "43",
parent_merchant_id: null,
skimlinks_ids: null,
custom_merchant_name: null,
ship_to_countries: "",
created_at: "2016-10-07 08:04:12",
updated_at: "2016-10-07 08:04:12",
categories: {
id: 43,
slug: "social",
category_name: "Social",
category_url_name: "social",
category_parent_id: null,
category_grandparent_id: null,
created_at: "2016-10-07 08:04:03",
updated_at: "2016-10-07 08:04:03"
},
deals: [
{
deal_id: 875438,
merchant_name: "Groupon",
merchant_id: 6605,
status: null,
deal_text: "Save Up To 90% Off Local Restaurants, Shops, Events And More",
restrictions: "",
coupon_code: "",
deal_start_date: "1/31/2016",
deal_end_date: "11/6/2016",
discount_type: null,
deal_type: "deal",
affiliate_url: "",
final_sale_price: null,
was_price: null,
discount: null,
percent_off: null,
dollars_off: null,
network: null,
image: null,
skimlinks_url: null,
fmtcurl: null,
created: null,
lastupdated: null,
threshold: null,
rank: "3",
starred: null,
master_merchant_id: null,
brands: null,
local: null,
link_id: null,
direct_url: null,
pixel_html: null,
created_at: "2016-10-07 08:05:26",
updated_at: "2016-10-07 08:05:26",
votes: [
{
id: 2,
deal_id: 875438,
vote: 1,
user_id: 0,
uuid: "ad63d5a0-2582-11e6-9a1c-49da886e8d6c",
created_at: "2016-10-12 05:23:57",
updated_at: "2016-10-12 05:23:57"
},
{
id: 5,
deal_id: 875438,
vote: 1,
user_id: 0,
uuid: "bde41ea0-903c-11e6-b93b-51bc5393a427",
created_at: "2016-10-12 05:28:50",
updated_at: "2016-10-12 05:28:50"
}
],
clicks: [
{
id: 1,
tracking_id: null,
merchant_id: null,
deal_id: "875438",
user_id: null,
user_tracking_id: null,
referring_url: "http://trystin.app:8000/deals-coupons/groupon.com",
referring_domain: null,
page_type: null,
page_url: null,
utm_source: null,
utm_medium: null,
utm_term: null,
utm_campaign_id: null,
os: "OS X",
ip_address: "10.0.2.2",
browser: "Chrome",
initial_referring_domain: null,
referring_keyword: null,
deal_position: null,
click_type: null,
created_at: "2016-10-12 02:19:59",
updated_at: "2016-10-12 02:19:59"
},
{
id: 12,
tracking_id: null,
merchant_id: null,
deal_id: "875438",
user_id: null,
user_tracking_id: null,
referring_url: "http://trystin.app:8000/deals-coupons/groupon.com",
referring_domain: null,
page_type: null,
page_url: null,
utm_source: null,
utm_medium: null,
utm_term: null,
utm_campaign_id: null,
os: "OS X",
ip_address: "10.0.2.2",
browser: "Chrome",
initial_referring_domain: null,
referring_keyword: null,
deal_position: null,
click_type: null,
created_at: "2016-10-21 03:41:11",
updated_at: "2016-10-21 03:41:11"
},
{
id: 15,
tracking_id: null,
merchant_id: null,
deal_id: "875438",
user_id: null,
user_tracking_id: null,
referring_url: "http://trystin.app:8000/deals-coupons/groupon.com",
referring_domain: null,
page_type: null,
page_url: null,
utm_source: null,
utm_medium: null,
utm_term: null,
utm_campaign_id: null,
os: "OS X",
ip_address: "10.0.2.2",
browser: "Chrome",
initial_referring_domain: null,
referring_keyword: null,
deal_position: null,
click_type: null,
created_at: "2016-10-21 03:55:33",
updated_at: "2016-10-21 03:55:33"
},
{
id: 16,
tracking_id: null,
merchant_id: null,
deal_id: "875438",
user_id: null,
user_tracking_id: null,
referring_url: "http://trystin.app:8000/deals-coupons/groupon.com",
referring_domain: null,
page_type: null,
page_url: null,
utm_source: null,
utm_medium: null,
utm_term: null,
utm_campaign_id: null,
os: "OS X",
ip_address: "10.0.2.2",
browser: "Chrome",
initial_referring_domain: null,
referring_keyword: null,
deal_position: null,
click_type: null,
created_at: "2016-10-21 04:06:08",
updated_at: "2016-10-21 04:06:08"
}
],
deal_dealtypes: [
{
id: 1888,
deal_id: "875438",
dealtype_id: "3",
created_at: "2016-10-07 08:05:26",
updated_at: "2016-10-07 08:05:26"
}
]
},
}

I need some help with the following

  1. Get limited fields and not all fields tied to Merchant
  2. Get limited fields from deals object
  3. Get Count of Votes and Clicks for each deal in the response

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.