How can I increase performance of querying in a loop using Laravel's query builder?

Question

Currently, I have 3 tables:

A customer can have a thousand invoices, and an invoice can have thousand of payments too.

My current query looks like this:

$invoices = DB::table('invoices')
        ->where('customer_id', $customer_id)
        ->get();

foreach ($invoices as $invoice) {
    // attach payments result to $invoice->all_payments
    $invoice->all_payments = DB::table('payments')
                                ->where('invoice_id', $invoice->id)
                                ->get();
}

My query works, though it takes a lot of time. I am looking for an alternative way to do this, performance wise.

My previous query using join:

    $data = DB::table('invoices')
                ->join('payments', 'invoices.id', '=', 'payments.invoice_id')
                ->where('customer_id', $customer_id)
                ->get();

Is there a better way of doing this?

use relationships for that. https://laravel.com/docs/5.7/eloquent-relationships. although using raw sql, you will probably have to use joins. — Wreigh, Sep 19 '18 at 05:16
@Wreigh I did use joins before, problem is I can't attach an array of payments. Instead, I am getting 1 invoice = 1 payment — wobsoriano, Sep 19 '18 at 05:21
if you haven't done indexing I'd suggest you do https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql — NoOorZ24, Sep 19 '18 at 06:02
@NoOorZ24 adding an index to the invoice_id made the query fast. Thanks. — wobsoriano, Sep 19 '18 at 06:09

score 0 · Answer 1 · answered Sep 19 '18 at 05:57

You should try this:

 $data = DB::table('invoices')
                ->join('payments', 'invoices.id', '=', 'payments.invoice_id')
                ->leftJoin('customers', 'invoices.customer_id', '=', 'customers.id')
                ->where('customer_id', $customer_id)
                ->get();

azisuazusa · Answer 2 · 2018-09-19T06:28:37.130

0

How about using eloquent?

Invoice::with('payments')->where('customer_id', $customerId)->get();

You just need to add relationship method in your Invoice model like this:

public function payments()
{
    return $this->hasMany('App\Model\Payment');
}

edited Sep 19 '18 at 06:28

answered Sep 19 '18 at 06:07

azisuazusa

317
1
12

You will get 'payments' field that contains an array of payments. It's more faster than using loop – azisuazusa Sep 19 '18 at 06:17

How can I increase performance of querying in a loop using Laravel's query builder?

2 Answers2