4
\$\begingroup\$

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_platinum
They 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.0
The PHP version is 5.5

currently the code executes in 0.15600085258484 seconds. 156 milliseconds. which is really slow.

database setup

<?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);

    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ I'm a little confused: are you seeking a code review of the PHP code you've included above, or asking for a SQL-driven solution instead of your PHP code? It feels like the latter to me, which I think might be a better fit for StackOverflow than CodeReview? \$\endgroup\$ – Stuart J Cuthbertson Mar 24 '16 at 13:47
1
\$\begingroup\$

From what I'm understanding with your question, your biggest performance hit is coming from comparing the dates on every entry in the database. Have you thought about splitting this up into a few queries rather than one large one? Execute one that grabs low prices in the table, then a second to compare the dates on the remaining duplicate values.

I may be completely missing the question, but that's what I'm getting from what your asking.

create view as priceView as select 'content' from 'table' where price=min(price);
create view as priceReducedView select 'content' from priceView where date_field between 'date1' and 'date2';
select 'content' from priceReducedView where date_field = max(date_field);

Something like that. Would this solve your problem and reduce the overhead that your seeing? For the sake of showing the example I used views but you could probably come up with a more viable solution using subqueries.

| improve this answer | |
\$\endgroup\$
-1
\$\begingroup\$

I am not sure if I fully understand what you are trying to do but if you're requirement is as follows:

Get the lowest price per category for a specified date range

then this query might work:

please note that MSSQL is not my forte so if there is a syntax error then let me know

    select
        min(price) as price
        ,'bronze' as level
    from
        pricelist_bronze
    where
        date_field between 'date1' and 'date2'
union
    select
        min(price) as price
        ,'silver' as level
    from
        pricelist_silver
    where
        date_field between 'date1' and 'date2'
union
    select
        min(price) as price
        ,'gold' as level
    from
        pricelist_gold
    where
        date_field between 'date1' and 'date2'
union
    select
        min(price) as price
        ,'platinum' as level
    from
        pricelist_platinum
    where
        date_field between 'date1' and 'date2'
order by
    price asc
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ No, its not that simple. This wouldnt account for the overlapping periods and choosing the lowest price within a range by the creation date. \$\endgroup\$ – Tschallacka Dec 16 '15 at 19:47
  • \$\begingroup\$ @MichaelDibbets I see, thanks. I'll try to take a look at this again later tonight. In the meantime, would you be able to reduce the explanation of your needs into a concise sentence or paragraph? I am pretty sure that what you need is achievable in raw SQL. \$\endgroup\$ – MonkeyZeus Dec 16 '15 at 20:21
  • \$\begingroup\$ Look to the paragraph with the highlighted A and B in it. That one is the best i can do to explain it \$\endgroup\$ – Tschallacka Dec 16 '15 at 20:39
  • \$\begingroup\$ @MichaelDibbets I read that paragraph at least 10 times. Do you think you would be able to show a dataset screenshot of 10 rows per price rule and highlight the rows which you would want the query to return? \$\endgroup\$ – MonkeyZeus Dec 16 '15 at 20:50
  • \$\begingroup\$ Ill see what i can come up with tomorrow when I'm back at work. Thanks :-) i fully realize that this isn't easy to grasp. It took me 2 full days to get this far \$\endgroup\$ – Tschallacka Dec 16 '15 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.