Jun 5, 2019

Level 1

Query relationships in Laravel

Hi All, I'm building a voucher system.

Tables: Vouchers(id, sender_id, receiver_id, cart_id) Carts(id, brand_id, city_id) LineItems(id, cart_id, product_name, mrp).

class Voucher extends Model {
    public function cart() {
        return $this->belongsTo('App\Models\Entities\Cart');
    }
}

class Cart extends Model {  
    public function vouchers() {
        return $this->hasMany('App\Models\Entities\Voucher');
    }
    
    public function lineItems() {
        return $this->hasMany('App\Models\Entities\LineItem');
    }
}

class LineItem extends Model {
    public function cart() {
        return $this->hasOne('App\Models\Entities\Cart');
    }
}

I want to generate a report containing vouchers, carts and line items based on the city and brand.

I have achieved it by using the code like follows:

$query = Voucher::query()
    ->select($selects)
    ->leftJoin('carts', 'carts.ca_id', '=', 'vouchers.cartid')
    ->leftJoin('line_items', 'line_items.ca_id', '=', 'carts.ca_id')
    ->whereBetween('vouchers.vchlastmodmsec', [$fromDate, $toDate]);
        
$query->groupBy(['redeemed_date', 'order_date', 'order_id', 'settlement_status']);

if ($request->filled('brand') && $request->input('brand')) {
    $query->where('carts.ca_brandid', $request->input('brand'));
}
        
if ($request->filled('city') && $request->input('city')) {
    $query->where('carts.ca_redmcitycode', $request->input('city'));
}

$vouchers = $query->get();

Is this is the optimised way to achieve it? Or any other way to achieve it more "laravel-way"? something like:

$vouchers  = Voucher::with('cart')->with('LineItem')->get();

When I tried like this it shows the following error: Call to undefined relationship [LineItem] on model [App\Models\Entities\Voucher]

Regards,

Saneesh.

matttonks11

7 years ago

Best Answer

Level 7

Hi, you should try

$vouchers = Voucher::with('cart.lineItems');

Since the cart model is the one with the lineItems relationship and not the Voucher model. You can then chain the rest of the methods like so

$vouchers = Voucher::with('cart.lineItems')
->whereBetween('vouchers.vchlastmodmsec', [$fromDate, $toDate])
->groupBy(['redeemed_date', 'order_date', 'order_id', 'settlement_status'])

I'm pretty sure you can use ```when()`` instead of the IF statements also if you prefer

see more here https://laraveldaily.com/less-know-way-conditional-queries/

So it looks something like this...

$query = Voucher::with('cart.lineItems')
->whereBetween('vouchers.vchlastmodmsec', [$fromDate, $toDate])
->groupBy(['redeemed_date', 'order_date', 'order_id', 'settlement_status']);

$query->when($request->filled('brand') && $request->input('brand'), function ($query) use($request) {
    return $query->where('carts.ca_brandid', $request->input('brand'));
})

$query->when($request->filled('city') && $request->input('city'), function ($query) use($request) {
    return $query->where('carts.ca_redmcitycode', $request->input('city'));
});

$vouchers = $query->get();

2 likes

Saneesh

7 years ago

Level 1

Thank you so much for your reply!

Could you please explain what is the advantage of this approach over which I was implemented(using select and leftJoin) ? Will it cache the result or use pre-compiled queries or something like that?

Note: Or links which helps to understand these kind of things also fine.

Saneesh

7 years ago

Level 1

Hello @matttonks11,

When I checked by executing the code, there is no reference (JOINS) with cart and lineItem table. so the query is failing.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'carts.ca_numlineitems' in 'field list'

How can I fix this issue?

My whole code is like this:

$selects = [
    DB::raw('vouchers.vchlastmodmsec as redeemed_date'),
    DB::raw('DATE(vouchers.vchcreatemsec) as order_date'),
    DB::raw('vouchers.vchid as order_id'),
    DB::raw('MIN(vouchers.rcvrname) as customer'),
    DB::raw('MIN(carts.ca_numlineitems) as items'),
    DB::raw('SUM(line_items.ln_mrp)  as mrp'),
    DB::raw('SUM(line_items.ln_bp)  as brand_price'),
    DB::raw('SUM(line_items.ln_mrp - line_items.ln_bp) as g_dis'),
    DB::raw('SUM(line_items.ln_sp) as selling_price'),
    DB::raw("SUM(FORMAT(line_items.ln_sp - ((line_items.ln_mrp / 100) * $commission), 2)) as settlement_amount"),
    DB::raw('vouchers.settlement_status')
];
        
        
$query = Voucher::with('carts.lineItem')
    ->select($selects)
    ->whereBetween('vouchers.vchlastmodmsec', [$fromDate, $toDate])
    ->groupBy(['redeemed_date', 'order_date', 'order_id', 'settlement_status']);
        
$query->when($request->filled('brand') && $request->input('brand'), function ($query) use ($request) {
    return $query->where('carts.ca_brandid', $request->input('brand'));
});
        
$query->when($request->filled('city') && $request->input('city'), function ($query) use ($request) {
    return $query->where('carts.ca_redmcitycode', $request->input('city'));
});
        
$vouchers = $query->get();
        
dd($vouchers);

The query:

SELECT vouchers.vchlastmodmsec as redeemed_date, DATE(vouchers.vchcreatemsec) as order_date, vouchers.vchid as order_id, MIN(vouchers.rcvrname) as customer, MIN(carts.ca_numlineitems) as items, SUM(line_items.ln_mrp) as mrp, SUM(line_items.ln_bp) as brand_price, SUM(line_items.ln_mrp - line_items.ln_bp) as g_dis, SUM(line_items.ln_sp) as selling_price, SUM(FORMAT(((line_items.ln_mrp / 100) * 7), 2)) as g_com, SUM(FORMAT(line_items.ln_sp - ((line_items.ln_mrp / 100) * 7), 2)) as settlement_amount, vouchers.settlement_status 
FROM vouchers 
WHERE vouchers.vchlastmodmsec between '2019-04-01 00:00:00' and '2019-05-15 23:59:59' and carts.ca_brandid = 'SBUX' and carts.ca_redmcitycode = 'BGLR' 
GROUP BY redeemed_date, order_date, order_id, settlement_status

matttonks11

7 years ago

Level 7

Hi, are you sure everything is correct with spelling? Does the rest of the query work if you temporarily remove that line?

 DB::raw('MIN(carts.ca_numlineitems) as items')

matttonks11

7 years ago

Level 7

Also relationships are generally there for ease of use I believe, so things like readability, it also saves you writing out that join statement multiple times in your codebase.

Saneesh

7 years ago

Level 1

@MATTTONKS11 - Spelling wise everything is correct. If you see the query generated there is no reference to other tables like cart and line_item.

I think because of that the query is failing

Note: I will check and let you know by tomorrow

Saneesh

6 years ago

Level 1

@matttonks11

This is my final query.

$query = Voucher::with(['cart' => function ($query) use ($request, $commission) {
    if ($request->filled('brand') && $request->input('brand')) {
        $query->where('carts.ca_brandid', $request->input('brand'));
    }
    if ($request->filled('city') && $request->input('city')) {
        $query->where('carts.ca_redmcitycode', $request->input('city'));
    }
            
    return $query->select([
        DB::raw('MIN(carts.ca_numlineitems) as numitems')
    ])
    ->with(['lineItems' => function ($query) use ($commission) {
        return $query->select([
            DB::raw('SUM(line_items.ln_mrp) as mrp'),                   
            DB::raw('SUM(line_items.ln_bp) as brand_price'),
            DB::raw('SUM(line_items.ln_mrp - line_items.ln_bp) as g_dis'),
            DB::raw('SUM(line_items.ln_sp) as selling_price'),
            DB::raw("SUM(FORMAT(((line_items.ln_mrp / 100) * $commission), 2))  as g_com"),
            DB::raw("SUM(FORMAT(line_items.ln_sp - ((line_items.ln_mrp / 100) * $commission), 2)) as settlement_amount")
        ]);
    }]);
    }])
    ->select([
        DB::raw('vouchers.vchlastmodmsec as redeemed_date'),
        DB::raw('DATE(vouchers.vchcreatemsec) as order_date'),
        DB::raw('vouchers.vchid as order_id'),
        DB::raw('MIN(vouchers.rcvrname) as customer'),
        DB::raw('vouchers.settlement_status')
    ])
    ->whereBetween('vouchers.vchlastmodmsec', [$fromDate, $toDate])
    ->groupBy(['redeemed_date', 'order_date', 'order_id', 'settlement_status']);

    if ($request->filled('transactions')) {
        $query->whereIn('vouchers.vchid', $request->input('transactions'));
    }
        
    $vouchers = $query->get();
    dd($vouchers);

I can see the orders as collections.

In blade how can I access voucher's cart and cart's lineItems in the loop?

When I try like this:

$vouchers = $query->first();
dd($vouchers->toArray());

Result:

array:6 [▼
  "redeemed_date" => "2019-04-21 06:04:34"
  "order_date" => "2019-03-29"
  "order_id" => "112233"
  "customer" => "Test"
  "settlement_status" => "redeemed"
  "cart" => array:2 [▼
    "items" => null
    "line_items" => array:1 [▼
      0 => array:6 [▼
        "mrp" => null
        "brand_price" => null
        "giftiicon_discount" => null
        "selling_price" => null
        "giftiicon_commission" => null
        "settlement_amount" => null
      ]
    ]
  ]
]

Looks like the data from cart and lineItem tables are not loaded.

Please or to participate in this conversation.