samedi 4 mars 2017

Generating a report in Laravel times out - make it more efficient

Lately I've been having to download the database and run this script locally in order to make it work. I could probably get into the server and allocate more memory but I'd rather improve the script if possible. There must be a more efficient way.

I have a table of invoices with orders which contain have items. I need to pull up all invoices in a certain date range that have orders which have items where tax was collected. The script below works, but takes forever and times out on the live server, only runs on my dev machine.

        $invoices = \App\Invoice::whereHas('orders', function($q)
        {
            $q->whereHas('items', function($q)
            {
                $q->where('tax_billed', '>', '0');
            });
        });             
        $invoices->whereBetween(session('report.datesearch'), [session('report.search_date_start'), session('report.search_date_end')]);
        $invoices = $invoices->get();

I thought maybe I can go the other way, and find all items where tax_billed > 0, but then how do I pare that down by the invoice date, which is stored two tables up the parental chain in the invoices table?

Relationships:

class Invoice extends Model {
    public function orders()
    {
        return $this->hasMany('App\Order', 'invoice_id') -> orderBy('created_at', 'asc');
    }
}

class Order extends Model {
    public function items() {
        return $this -> belongsToMany('App\Item', 'order_item', 'order_id', 'item_id')-> withPivot('id','variant_id','quantity','quantity_received','quantity_delivered','notes', 'createdby_user_id', 'weight', 'cost_billed', 'tax_billed', 'tax_rate', 'calc_by_weight', 'track_units')->orderBy('name');
    }
}

and the obvious reverse: ITEMS belongsTo ORDERS belongsTo INVOICES



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire