The octoberCMS(based on laravel) controller below is what I use to select the lowest price in 4 pricelists in the given daterange.Prices can vary and it's not guaranteed that the more expensive sounding categories will have higher prices.I have 4 relations in my model pricelist_bronze, pricelist_silver, pricelist_gold and pricelist_platinumThey are all added via a linking table like this in the model'pricerules_gold' =>            ['MDibbets\PriceManager\Models\PriceRule',                'table'=>'traveltour_to_pricerule_gold',                'key' => 'traveltour_id_gold',                'otherKey' => 'pricerule_id'            ],What i currently do is request all pricerules that match the periods.Then make a list of all dates in range, fill that list with the most recent item that matches that day, then when full iterate the list to find the lowest value, return that, then repeat for other pricelists, then sort that list, and return the lowest value.Clearly there is room for improvement, but I can't see how I can optimise this as I had a hard time already compiling this with the eloquent queries and the carbon objects.Important thing to keep in mind though, the database objects the newest one always wins on a given date.So if you have three dates 5,6,7 and a pricerule A(created 2015-12-20) that matches 1,2,3,4,5,6 with a later creation date than a pricerule B( created 2015-12-19) matching dates 5,6,7,8,9 then date 5 and 6 will match A because of the newer creation date and 7 will match B because there is nothing newer matching that date. I'm interested in learning how to manipulate dates better in combination with eloquent and MSSQL optimised queries to minimise the iterations needed.Prefably I have most of the work done by the mssql queries to return only one object with the lowest price within a given date range for a single pricelist, but I haven't been able to figure out how to get that working reliably.Raw queries are fine by me.This is the database info i'm queriying for this example:The database is SQL server 2012 for those who know a way to fiddle with RAW queries.The laravel version octoberCMS uses is 5.0The PHP version is 5.5  currently the code executes in 0.15600085258484 seconds. 156 milliseconds. which is really slow.<?php namespace MDibbets\TravelTourManager\Controllers;use BackendMenu;use Backend\Classes\Controller;use DB;use Carbon\Carbon;/** * Travel Tour Back-end Controller */class TravelTour extends Controller{    public $implement = [        'Backend.Behaviors.FormController',        'Backend.Behaviors.ListController',        'Backend.Behaviors.RelationController',    ];    public $formConfig = 'config_form.yaml';    public $listConfig = 'config_list.yaml';    public $relationConfig = 'config_relation.yaml';    public function __construct()    {        parent::__construct();        BackendMenu::setContext('MDibbets.TravelTourManager', 'travletourmanager', 'traveltour');    }    /**     * Gets the lowest price from the relation within supplied daterange     * @param $relation belongsTo relation with pricefule     * @param $startdate inclusive startdate to search in     * @param $enddate inclusive end date to search in     * @return \stdClass or relation object that has the price.     */    function getRelationPrice($relation,$startdate='',$enddate='') {        // supplied dates empty? return empty result        if(empty($startdate) || empty($enddate)) {            $ret = new \stdClass();            $ret->price = 'x,--';            return $ret;        }        /*         * Select all results that match our date ranges. Pricerules have date ranges in which they are active.         * We are testing for overlapping dateranges. Dates in database are without time component. Just YYYY-mm-dd MSSQL         */        $results = $relation->where('startdate','<=',$enddate)->where('enddate', '>=',$startdate)->where('price','>','0')->get();        /*         * No results? return large number for sorting         */        $ret = new \stdClass();        $ret->price = 999999999;        if(!$results->count()) {            return $ret;        }        /**         * List for each day within range, to hold matching objects.         */        $list = [];        /**         * Carbon instances for easy iteration         */        $start = Carbon::createFromFormat('Y-m-d', $startdate);        $end = Carbon::createFromFormat('Y-m-d', $enddate);        while($end->gte($start)) {            /** @var $index for making the array key in $list */            $index = $start->toDateString();            foreach ($results as $result) {                /**                 * I needed the second check otherwise it wouldn't match a pricerule that had a period of one day                 */                if(                    ($start->gte($result->startdate) && $start->lte($result->enddate))                                                    ||                    ($start->diffInDays($result->startdate)==0 && $start->diffInDays($result->enddate)==0)                ) {                    /** No list index yet? assume this is what we want */                    if(!isset($list[$index])) {                        $list[$index] = $result;                    }                    else {                        /**                         * Check if current item has a greater created date than the current item in the $list                         * If so, this one is more important as it is the newer date.                         */                        if ($result->created_at->gt($list[$index]->created_at)) {                                $list[$index] = $result;                            }                        }                    }                }            $start->addDay(1);        }        /**         * Iterate through list to get the lowest value         */        foreach($list as $value) {            if($value->price < $ret->price) {                $ret = $value;            }        }        return $ret;    }    /**     * Gets lowest price in all 4 pricelists     * @param $model That contains 4 pricerule sets, bronze, silver, gold and platinum     * @param $startdate inclusive start date of range to find lowest price in     * @param $enddate inclusive end date of range to find lowest price in     * @return array that contains the lowest price at parameter $['price']     */    public function getLowestPriceFor($model,$startdate,$enddate) {        $values = [];        $bronze = $this->getRelationPrice($model->pricerules_bronze(),$startdate,$enddate);        $values[] = $bronze?$bronze->price:null;        $silver = $this->getRelationPrice($model->pricerules_silver(),$startdate,$enddate);        $values[] = $silver?$silver->price:null;        $gold = $this->getRelationPrice($model->pricerules_gold(),$startdate,$enddate);        $values[] = $gold?$gold->price:null;        $platinum = $this->getRelationPrice($model->pricerules_platinum(),$startdate,$enddate);        $values[] = $platinum?$platinum->price:null;        sort($values,SORT_NUMERIC );        $values =array_filter($values);        return ['price'=>array_shift($values)?:'-.--'];    }    /**     * Gets all lowest prices from current item and returns it as ajax response     * @return array     */    public function onPreviewCalcLowestPriceInPeriod() {        $modelKey = post('xid');        $startdate = post('startdate');        $enddate = post('enddate');        $model = $this->formFindModelObject($modelKey);        return $this->getLowestPriceFor($model,$startdate,$enddate);    }}
4 database queries, multiple loops in loops, slow processing time
php;sql server;laravel;eloquent