AdminOrdersController Limit results on load

[ukbaz]

Hi

I've edited the initial select query in AdminOrdersContoller.php so that the Orders list includes transaction ID from Stripe payments next to the Prestashop Order ID. So I added a JOIN to ps_order_payment to pull the Stripe transaction ID

All works fine and this allows both see in the transaction ID in list view and exporting it in the csv file export.

However the default load of ALL orders when opening the Admin Orders page is now taking ages.

If I type in a date range of about a months worth of orders then the resulting query does not take that long -- so is there a way to limit results by date range initially? Say initially load last 6 weeks worth of orders and limit query time? 

My modified code for AdminOrdersController is pasted below:

class AdminOrdersControllerCore extends AdminController
{
    public $toolbar_title;

    protected $statuses_array = array();

    public function __construct()
    {
        $this->bootstrap = true;
        $this->table = 'order';
        $this->className = 'Order';
        $this->lang = false;
        $this->addRowAction('view');
        $this->explicitSelect = true;
        $this->allow_export = true;
        $this->deleted = false;
        $this->context = Context::getContext();

        $this->_select = '
op.transaction_id AS stripeid,
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
		IF((SELECT so.id_order FROM `'._DB_PREFIX_.'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';

        $this->_join = '
LEFT JOIN ps_order_payment op ON op.order_reference = a.id_order
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		LEFT JOIN `'._DB_PREFIX_.'address` address ON address.id_address = a.id_address_delivery
		LEFT JOIN `'._DB_PREFIX_.'country` country ON address.id_country = country.id_country
		LEFT JOIN `'._DB_PREFIX_.'country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = '.(int)$this->context->language->id.')
		LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = a.`current_state`)
		LEFT JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)$this->context->language->id.')';
        $this->_orderBy = 'id_order';
        $this->_orderWay = 'DESC';
        $this->_use_found_rows = true;

        $statuses = OrderState::getOrderStates((int)$this->context->language->id);
        foreach ($statuses as $status) {
            $this->statuses_array[$status['id_order_state']] = $status['name'];
        }

        $this->fields_list = array(
            'id_order' => array(
                'title' => $this->l('ID'),
                'align' => 'text-center',
                'class' => 'fixed-width-xs'
            ),
'stripeid' => array(
'title' => $this->l('StripeID'),
),
            'reference' => array(
                'title' => $this->l('Reference')
            ),
            'new' => array(
                'title' => $this->l('New client'),
                'align' => 'text-center',
                'type' => 'bool',
                'tmpTableFilter' => true,
                'orderby' => false,
                'callback' => 'printNewCustomer'
            ),
            'customer' => array(
                'title' => $this->l('Customer'),
                'havingFilter' => true,
            ),
        );

 

[tomerg3]

Do you mean that without the modification you made the query was going fast, and now it's slow?

If so, the issue is with your query.

I think not getting all the results will mess up your pagination.

Your best bet is to try and figure out why is the new query slow, but running it with "explain" in MYSQL.

Also, it looks like you have an error there in "LEFT JOIN ps_order_payment op ON op.order_reference = a.id_order"
order_reference is a string, and id_order is an int