The minimum price in last 30 days is not working if price was set before 30 days

  • Resolved piotrczapla2jb

    (@piotrczapla2jb)


    1 year, 11 months ago

    If I’m reading your code correctly, the date is set to the price modification date. So the first time when the product was available at that price. While the sql query expect that the ‘date’ column represent the last time when the price was valid.

    To give you an example when this fails:

    Let say a product gets discount to 100 EUR for 60 days, then after that 60 days price changed to 110 EUR, then your plugin will say the ‘best price in 30 days is 110EUR’. While it should say 100 EUR.

    I’ve discovered the issue while adding all the historic prices to the history_table. I was expecting the plugin to show the prices I’ve added but It did not.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter piotrczapla2jb

    (@piotrczapla2jb)

    I’ve fixed this as well as the previous issue by changing the findLowestHistoryPriceIn30Days to
    $ext_hist = "SELECT *, IFNULL(LAG(date, 1) over w, now()) as end_date, MAX(date) OVER w as last_promo FROM " . HistoryPrice::getPrefixedTable() ." WHERE product_id = %s WINDOW w as (PARTITION BY product_id ORDER BY date DESC)";

    $qry = "WITH ext_hist as ($ext_hist)\n";
    $qry .= "SELECT *, date_add(last_promo, interval -30 day) as horizont FROM ext_hist WHERE end_date >= date_add(last_promo, interval -30 day) ORDER BY price LIMIT 1";

    $params = $wpdb->get_row($wpdb->prepare($qry, $productId), ARRAY_A);

    I’m not sure how performant this query is but I’m selecting small portion of data at the begining of the query so it should be fast enough.

    Plugin Author Perfectly Project

    (@perfectlyproject)

    @piotrczapla2jb Hello,

    Correct implementation of Directive and also fix for issue what you noticed is ready on my github.

    Your query could slow sites so I decided to add “start” and “end” date in history table.

    Thank you for commitment to create this plugin!

    Unfortunately new version of the plugin will be published in the next week, so if you would like to add the functionality faster you can copy code from this commit.

    https://github.com/perfectlyproject/perfectwp-wc-omnibus/commit/f9672e66160936889d788855d52e05871adabcba

    Thread Starter piotrczapla2jb

    (@piotrczapla2jb)

    I’m happy to help. Adding end date is much cleaner, thank you! There is a small bug still: https://github.com/perfectlyproject/perfectwp-wc-omnibus/issues/1

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘The minimum price in last 30 days is not working if price was set before 30 days’ is closed to new replies.