FROM Date insert on back office orders page to load faster.

[MJ_C]

Hello,

not sure if anyone can help?

We use PS 1.7.6.7 on a shared server.

When we log into the back office and go to the orders page it takes around 1 minute to load the page.

Once it's loaded I then change orders FROM date column to a few days prior to today and leave the TO date column blank and click SEARCH.

This returns the same page again in seconds.

 

My question is, is there a way to pre fill the FROM date on the orders page so that I don't have to wait a minute for the orders page to load.

I have searched the forums and cannot find an answer.  I don't appear to be able to add anything to the URL to accomplish this.

Any ideas please??

 

[musicmaster]

Please switch on Profiling to see which query is taking so long.

https://dh42.com/blog/prestashop-debug-profiling/

 

[MJ_C]

Thanks Musicmaster ( I am a Paid member of The Fantastic Prestools BTW)

I saw all the information that it shows but it really means nothing to me.

I have attached a screenshot of the top part and of course it appears to be telling me that what I running PS on is like a 30 year old dog.

There is loads of information below that but way above what I understand!!

 

 

[musicmaster]

You have to scroll further down. There are the queries - and if you scroll to the right you will see how long they take. The first query should be the one that takes longest.

[MJ_C]

Thankyou.

I hadn't seen the scroll bar and information to the right.

Is it safe to paste the query ?

 

 

[MJ_C]

I think I am understaning.

The rows it's referring to are the DB rows of all the orders?

And... being on a shared server, it's not going to be fast.

The trouble is that initially when I load the orders page, it's looking at ALL orders, I assume, hence me asking about loading just a date FROM, if that's possible, in a simple way.

 

[musicmaster]

Please show the query. It is safe.

You have here a problem: the code is from a Prestashop file. So that part is safe. But it is the kind of meta-code that has been called from elsewhere. So you need to find out from where. Probably the best way to get it is make some deliberate error in the code in that file. With debug mode enabled you will then see the calling tree. However, this code may be called from other places too, so you should first check for that (suggestion: add some echo code).

[MJ_C]

Thanks again.

I am afraid I am not really understanding your next steps and I think probably too advanced for me B-)

I think I will have to leave it, unless you can see something from the code that's blindingly obvious, but I really appreciate your time.

 

 

 

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS a.`id_order`, a.`reference` AS `reference`, a.`total_paid_tax_incl` AS `total_paid_tax_incl`, a.`payment` AS `payment`, a.`date_add` AS `date_add`, cs_mp_order_alias.`mp_order_id` AS `mp_order_id`
, 
a.id_currency,
a.id_order AS id_pdf,
CONCAT(c.`firstname`, ' ', c.`lastname`, ' - ZIP -  ', `postcode`, ' - email -  ', c.`email`, ' - Tracking -  ', `shipping_number`) AS `customer`,
osl.`name` AS `osname`,
os.`color`,
IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new,
country_lang.name as cname,
IF(a.valid, 1, 0) badge_success, `a`.`id_order`, GROUP_CONCAT(
DISTINCT `message`.`message`
SEPARATOR '; ') AS 'messagemessage', GROUP_CONCAT(
DISTINCT `order_detail`.`product_reference`
SEPARATOR '; ') AS 'order_detailproduct_reference', GROUP_CONCAT(
DISTINCT `order_detail`.`product_name`
SEPARATOR '; ') AS 'order_detailproduct_name', GROUP_CONCAT(
DISTINCT `fastbay1_order_detail`.`ebay_buyer`
SEPARATOR '; ') AS 'fastbay1_order_detailebay_buyer', GROUP_CONCAT(
DISTINCT `order_detail`.`product_quantity`
SEPARATOR '; ') AS 'order_detailproduct_quantity', GROUP_CONCAT(
DISTINCT `fastbay1_order_detail`.`ebay_order_id`
SEPARATOR '; ') AS 'fastbay1_order_detailebay_order_id', `cs_mp_order_alias`.`mp_order_id` AS `mp_order_id`, shop.name as shop_name 
FROM `ps_orders` a 
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery
INNER JOIN `ps_country` country ON address.id_country = country.id_country
INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 2)
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 2) LEFT JOIN `ps_order_detail` `order_detail` ON `a`.`id_order` = `order_detail`.`id_order` LEFT JOIN `ps_product` `product` ON `order_detail`.`product_id` = `product`.`id_product` LEFT JOIN `ps_order_carrier` `order_carrier` ON `a`.`id_order` = `order_carrier`.`id_order` LEFT JOIN `ps_customer_group` `customer_group` ON `c`.`id_customer` = `customer_group`.`id_customer` LEFT JOIN `ps_message` `message` ON `message`.`id_order` =
`a`.`id_order` LEFT JOIN `ps_fastbay1_order_detail` `fastbay1_order_detail` ON `fastbay1_order_detail`.`id_order` =
`a`.`id_order` LEFT JOIN `ps_marketplace_orders` AS `cs_mp_order_alias` ON (a.`id_order` = cs_mp_order_alias.`id_order`) 
LEFT JOIN `ps_shop` shop
ON a.`id_shop` = shop.`id_shop` WHERE 1  AND a.id_shop IN (1) 
GROUP BY `a`.`id_order` 
ORDER BY a.id_order DESC  LIMIT 0, 50

 

[musicmaster]

In that case the best you can do is experimenting with disabling modules. Quite likely one of them causes this.

[MJ_C]

Thank you for your time, it is greatly appreciated.

 

[MJ_C]

I tried disabling modules but nothing seems to help and I I just came across this

https://github.com/PrestaShop/PrestaShop/issues/10822

and assume it's uses the API and returns xml which is way out of my understanding, but is there a way to incorporate the date_add into the normal orders page url?

 http://presta1760/api/orders/?display=full&filter[date_add]=[2019-05-24,2019-05-28]&date=1

It probably isn't possible, but thought I would ask.

My shared server sometimes times out loading the orders page now, but as soon as I enter a date in the from field, it's very quick.  Again, I believe it's trying to load thousands of orders when it's not necessary.

Many thanks

[MJ_C]

After another look I found AdminOrdersController.php

line 129

            'date_add' => array(
                'title' => $this->trans('Date', array(), 'Admin.Global'),
                'align' => 'text-right',
                'type' => 'datetime',
                'filter_key' => 'a!date_add',
            ),

Changing the 'a!date_add' allows the page to load instantly, but with no further content.

I have tried bodging and manually adding a date to replace  a!date_add but I don;t know what I am doing and didn't work.

If there is a way to make it take a manual date, then I can use some php to get a date from one moth ago, which should solve my problem B-)

[MJ_C]

Well, I hate to say but Music Master is correct, AGAIN!

I thought I would check any modules that I have running to do with orders and I have one that customises the columns on the orders page.

I removed all the custom columns and the order page loaded very quickly.  The I started to re-enter them, one by one and all was fine until it got to ebay columns for order number and user id.  It appears that it was those that were slowing down the orders page, BUT if there was some way of getting the date populated then this would still solve my issue.  BUT for now I am without those handy columns on the orders page, but at least the page loads!!

Thanks again MusicMaster.