Add bought products to the Prestashop Order list in the back office

In today’s tutorial we will see how to show bought products in the back office order list, and also how to enable filtering by this parameter.

Introduction

Last year, we saw how to filter by country in the Prestashop order list, which is indeed useful when your customer base is spread around the world. Today, we will add another parameter to the orders table, showing us which products are part of the order, effectively saving us a lot of time in many situations when we only need to know what is to be shipped.

One override will be enough to achieve this result: AdminOrdersController.

Overriding the AdminOrdersController

To start off, create a new file in override/controllers/admin and call it AdminOrderController.php. We need to override the construct method; therefore start by adding the following inside php tags:

class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{

	}
}

If you don’t know anything about overrides, have a look at how to override Prestashop classes and controllers at Prestashop’s Doc.

Now let’s add some base parameter. Have a look at your own original AdminOrdersController file (controllers/admin) if you use a version different from 1.5.6.x.

class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{
		$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();
	}
}

We didn’t do anything special here, just setting up the module’s basics by copying the original method. Time to extend the SELECT statement!

Selecting products within the order query

Right after the object definition we added before, we need to extend the $this->_select variable in order to grab products as well. Here is how it looks in the original order controller:

	$this->_select = '
		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 COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new';

So, grab this, add it after the last lines we wrote, and change it to:

		$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
		(SELECT GROUP_CONCAT(odd.product_name SEPARATOR ", ") FROM `'._DB_PREFIX_.'order_detail` odd WHERE odd.id_order = a.id_order) as products,
		IF((SELECT COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new';

As you can see, we added: (SELECT GROUP_CONCAT(odd.product_name SEPARATOR “, “) FROM `’._DB_PREFIX_.’order_detail` odd WHERE odd.id_order = a.id_order) as products,. This is the subquery that will grab products from the order_detail table, given that the order id is the current one, of course! Using GROUP_CONCAT will ensure all products will br shown, separated by a comma and space. You can use any separator you prefer, of course.

Our override so far:

class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{
		$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 = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
		(SELECT GROUP_CONCAT(odd.product_name SEPARATOR ", ") FROM `'._DB_PREFIX_.'order_detail` odd WHERE odd.id_order = a.id_order) as products,
		IF((SELECT COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new';

}

We don’t need extra stuff for the query, so right after the select variable, add:

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		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';

		$statuses_array = array();
		$statuses = OrderState::getOrderStates((int)$this->context->language->id);

		foreach ($statuses as $status)
			$statuses_array[$status['id_order_state']] = $status['name'];

In any casy, copy everything after your original controller’s select variable, down to before $this->fields_list.

Let’s now more to showing products!

Displaying the selected products

To add new fields in the orders table, we need to extend $this->fields_list. Therefore, grab the definition from the original controller, and paste it after the last foreach we added. On 1.5.6.x, it looks like this:

		$this->fields_list = array(
		'id_order' => array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'width' => 25
		),
		'reference' => array(
			'title' => $this->l('Reference'),
			'align' => 'center',
			'width' => 65
		),
		'new' => array(
			'title' => $this->l('New'),
			'width' => 25,
			'align' => 'center',
			'type' => 'bool',
			'tmpTableFilter' => true,
			'icon' => array(
				0 => 'blank.gif',
				1 => array(
					'src' => 'note.png',
					'alt' => $this->l('First customer order'),
				)
			),
			'orderby' => false
		),
		'customer' => array(
			'title' => $this->l('Customer'),
			'havingFilter' => true,
		),
		'total_paid_tax_incl' => array(
			'title' => $this->l('Total'),
			'width' => 70,
			'align' => 'right',
			'prefix' => '<b>',
			'suffix' => '</b>',
			'type' => 'price',
			'currency' => true
		),
		'payment' => array(
			'title' => $this->l('Payment: '),
			'width' => 100
		),
		'osname' => array(
			'title' => $this->l('Status'),
			'color' => 'color',
			'width' => 280,
			'type' => 'select',
			'list' => $statuses_array,
			'filter_key' => 'os!id_order_state',
			'filter_type' => 'int',
			'order_key' => 'osname'
		),
		'date_add' => array(
			'title' => $this->l('Date'),
			'width' => 130,
			'align' => 'right',
			'type' => 'datetime',
			'filter_key' => 'a!date_add'
		),
		'id_pdf' => array(
			'title' => $this->l('PDF'),
			'width' => 35,
			'align' => 'center',
			'callback' => 'printPDFIcons',
			'orderby' => false,
			'search' => false,
			'remove_onclick' => true)
		);

Where to add the product list? I chose to add it right after the customer name. We have to add the new field as an array, like this:

		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),

In the order, we have the column title (of little importance, it’s the one shown to the viewer); the possibility to filter by those parameters; the name of the filter key (caution! it must be the same as the name of the parameter we grabbed in the select statement!); the column width in pixels.

At this point, add it to the array:

		$this->fields_list = array(
		'id_order' => array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'width' => 25
		),
		'reference' => array(
			'title' => $this->l('Reference'),
			'align' => 'center',
			'width' => 65
		),
		'new' => array(
			'title' => $this->l('New'),
			'width' => 25,
			'align' => 'center',
			'type' => 'bool',
			'tmpTableFilter' => true,
			'icon' => array(
				0 => 'blank.gif',
				1 => array(
					'src' => 'note.png',
					'alt' => $this->l('First customer order'),
				)
			),
			'orderby' => false
		),
		'customer' => array(
			'title' => $this->l('Customer'),
			'havingFilter' => true,
		),
		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),	
		'total_paid_tax_incl' => array(
			'title' => $this->l('Total'),
			'width' => 70,
			'align' => 'right',
			'prefix' => '<b>',
			'suffix' => '</b>',
			'type' => 'price',
			'currency' => true
		),
		'payment' => array(
			'title' => $this->l('Payment: '),
			'width' => 100
		),
		'osname' => array(
			'title' => $this->l('Status'),
			'color' => 'color',
			'width' => 280,
			'type' => 'select',
			'list' => $statuses_array,
			'filter_key' => 'os!id_order_state',
			'filter_type' => 'int',
			'order_key' => 'osname'
		),
		'date_add' => array(
			'title' => $this->l('Date'),
			'width' => 130,
			'align' => 'right',
			'type' => 'datetime',
			'filter_key' => 'a!date_add'
		),
		'id_pdf' => array(
			'title' => $this->l('PDF'),
			'width' => 35,
			'align' => 'center',
			'callback' => 'printPDFIcons',
			'orderby' => false,
			'search' => false,
			'remove_onclick' => true)
		);

We are almost done!

Finishing off the override

The rest of the override is basically identical to the original file, so from that one, grab everything you find after the fields list in the construct method, and paste it into the override:


		$this->shopLinkType = 'shop';
		$this->shopShareDatas = Shop::SHARE_ORDER;

		if (Tools::isSubmit('id_order'))
		{
			// Save context (in order to apply cart rule)
			$order = new Order((int)Tools::getValue('id_order'));
			if (!Validate::isLoadedObject($order))
				throw new PrestaShopException('Cannot load Order object');
			$this->context->cart = new Cart($order->id_cart);
			$this->context->customer = new Customer($order->id_customer);
		}

		parent::__construct();

Lastly, since we don’t want our new fields list to be overridden by the original one, change parent::__construct(); to AdminController::__construct.

Save, go to cache/ and delete the class_index.php file so that our override can take place. We are done!

The final override



class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{
		$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 = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
		(SELECT GROUP_CONCAT(odd.product_name SEPARATOR ", ") FROM `'._DB_PREFIX_.'order_detail` odd WHERE odd.id_order = a.id_order) as products,
		IF((SELECT COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new';


		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		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';

		$statuses_array = array();
		$statuses = OrderState::getOrderStates((int)$this->context->language->id);

		foreach ($statuses as $status)
			$statuses_array[$status['id_order_state']] = $status['name'];



		$this->fields_list = array(
		'id_order' => array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'width' => 25
		),
		'reference' => array(
			'title' => $this->l('Reference'),
			'align' => 'center',
			'width' => 65
		),
		'new' => array(
			'title' => $this->l('New'),
			'width' => 25,
			'align' => 'center',
			'type' => 'bool',
			'tmpTableFilter' => true,
			'icon' => array(
				0 => 'blank.gif',
				1 => array(
					'src' => 'note.png',
					'alt' => $this->l('First customer order'),
				)
			),
			'orderby' => false
		),
		'customer' => array(
			'title' => $this->l('Customer'),
			'havingFilter' => true,
		),
		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),	
		'total_paid_tax_incl' => array(
			'title' => $this->l('Total'),
			'width' => 70,
			'align' => 'right',
			'prefix' => '<b>',
			'suffix' => '</b>',
			'type' => 'price',
			'currency' => true
		),
		'payment' => array(
			'title' => $this->l('Payment: '),
			'width' => 100
		),
		'osname' => array(
			'title' => $this->l('Status'),
			'color' => 'color',
			'width' => 280,
			'type' => 'select',
			'list' => $statuses_array,
			'filter_key' => 'os!id_order_state',
			'filter_type' => 'int',
			'order_key' => 'osname'
		),
		'date_add' => array(
			'title' => $this->l('Date'),
			'width' => 130,
			'align' => 'right',
			'type' => 'datetime',
			'filter_key' => 'a!date_add'
		),
		'id_pdf' => array(
			'title' => $this->l('PDF'),
			'width' => 35,
			'align' => 'center',
			'callback' => 'printPDFIcons',
			'orderby' => false,
			'search' => false,
			'remove_onclick' => true)
		);

		$this->shopLinkType = 'shop';
		$this->shopShareDatas = Shop::SHARE_ORDER;

		if (Tools::isSubmit('id_order'))
		{
			// Save context (in order to apply cart rule)
			$order = new Order((int)Tools::getValue('id_order'));
			if (!Validate::isLoadedObject($order))
				throw new PrestaShopException('Cannot load Order object');
			$this->context->cart = new Cart($order->id_cart);
			$this->context->customer = new Customer($order->id_customer);
		}

		AdminController::__construct();
	}
}

Additional Resources

How to filter by country in the Prestashop order list
How to override Prestashop classes and controllers at Prestashop’s Doc.

Pravda

I want to add that if you copy and paste the final code, at least for 1.7 you have to add:
parent::__construct();
before $this->table = ‘order';

if you dont do it, you will get a 500 server error. I was fighting several hours checking the code, looking for the error.

have a nice weekend
Kas Blks

https://uploads.disquscdn.com/images/fdbc57276ec117db7170cc9cdfcedfbd86da18c498e11323d0d2f5a01bfc2dfb.png Thank you for your tutorial.
I did that on PS 1.7 and but after adding the product reference SQL request the order reference is not shown in the table any more and instead I have a double dash “–” .
- unamarca
  
  Hi Kas, please can you help me ? which PS version are you used ? I can’t do it
  
  Thanks in advance !
thodoris84

HI, very nice work!

But i have a problem, if i override the adminordercontroller.php filter doesn’t work and i get a blank page.
This is happening even if i create i blank adminordercontroller.php in override folder.
Any suggestion?
SupuS

Hi,
we use your mod for a long time without issue on PS 1.5.6.2. Now we found that order with many ordered products has truncated list of products to 961 characters include whitespaces. Truncation is in the middle of last viewed product name. Have you some idea how to fix it?
- NemoPS
  
  Hi SupuS,
  There might be a limit in the output, did you check the page source to see if the string is truncated, or just partially hidden?
Horia Rudan

It also works for 1.6, but you need to add this row right above “$this->table = ‘order';”
$this->bootstrap = true;
Otherwise, your layout will be broken.
Fred.C

Great tuto, just a question.
What if the product is a customized product with a text field (since that prestashop allow this personnalisation) ?
How to show the custom text in the products column ?
Eryk Wróbel

This is just great! I have querstion – how to change separator from , into I tried with almost everything and I cannot get it working.
Céline

Hi ! Working perfectly even on 1.6. Is there a way to modify the product list so the attributes name won’t show up ? Ex : “iPod blue” instead of “iPod – color : blue” (or at least just “iPod”) ? thanks !
usman

Thank you very much !!
you made my day
Sandra Lili Yin

Hi, can you help for PS1.6?
Suzanne Bradley

It looks just like what I would like out of my PrestaShop right now. To easily see what customers have bought. Well beyond my php skills though.
Sérgio Martins

Can you do a tutorial to show sent messages related with the order on orders page /index.php?controller=AdminOrders&id_order=xxxxxx
- NemoPS
  
  Thanks for the suggestion! I wrote it down, I’ll see what I can do!
AppleEater

Dear Nemo,
Great ttorial. The only thing I’m missing a screenshot showing the results of this modification.

Add bought products to the Prestashop Order list in the back office

Introduction

Overriding the AdminOrdersController

Selecting products within the order query

Displaying the selected products

Finishing off the override

Additional Resources

Prestashop: export products without...

How to add new fields to the custom...

Grid display for Prestashop 1.5 def...

Creating Multilanguage attachments ...

Removing out of stock products from...

Blocking spam emails in PrestaShop

Introduction

Overriding the AdminOrdersController

Selecting products within the order query

Displaying the selected products

Finishing off the override

Additional Resources

Related Articles