PS 1.6 BO AdminOrder ORDER products by reference, HOW?

[buhajus]

Good day,

anyone can help me with ordering products by products reference in AdminOrder in this section?

I found in classes/Order.php this function, but when i added ORDER BY... i got blank table of products.

    public function getProductsDetail()
    {
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'order_detail` od
		LEFT JOIN `'._DB_PREFIX_.'product` p ON (p.id_product = od.product_id)
		LEFT JOIN `'._DB_PREFIX_.'product_shop` ps ON (ps.id_product = p.id_product AND ps.id_shop = od.id_shop)
		WHERE od.`id_order` = '.(int)$this->id.'ORDER BY od.product_reference ASC');
    }

Any ideas ?

Edited April 9, 2018 by buhajus (see edit history)

[stupidcomputer]

public function getProductsDetail()
	{
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'order_detail` od
		LEFT JOIN `'._DB_PREFIX_.'product` p ON (p.id_product = od.product_id AND p.id_category_default)
		LEFT JOIN `'._DB_PREFIX_.'product_shop` ps ON (ps.id_product = p.id_product AND ps.id_shop = od.id_shop AND ps.id_category_default = p.id_category_default)
		WHERE `id_order` = '.(int)$this->id.'
		ORDER BY ps.`id_category_default` ASC');
	}

I did this to sort the products based on the category ID, in ascending order.
I think that to order them based on the reference code the code is more or less like this (sorry for my english):

public function getProductsDetail()
	{
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'order_detail` od
		LEFT JOIN `'._DB_PREFIX_.'product` p ON (p.id_product = od.product_id AND p.reference)
		LEFT JOIN `'._DB_PREFIX_.'product_shop` ps ON (ps.id_product = p.id_product AND ps.id_shop = od.id_shop)
		WHERE `id_order` = '.(int)$this->id.'
		ORDER BY p.`reference` ASC');
	}

 

[stupidcomputer]

public function getProductsDetail()
	{
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'order_detail` od
		LEFT JOIN `'._DB_PREFIX_.'product` p ON (p.id_product = od.product_id AND p.reference = od.product_reference)
		LEFT JOIN `'._DB_PREFIX_.'product_shop` ps ON (ps.id_product = p.id_product AND ps.id_shop = od.id_shop)
		WHERE `id_order` = '.(int)$this->id.'
		ORDER BY od.`product_reference` ASC');
	}

Sorry, I made a mistake, now it should be correct.

[Mattisa]

Hello,

Do you have a solution for Prestashop 1.7 ? I tried to do that, but it seems that it does not work for me.

Thank you