Jump to content

How to add an extra column in Orders table?


giwrgos
 Share

Recommended Posts

  • 2 weeks later...

You need to define new field in AdminOrdersController.php

 

in fieldlist array add:

'products' => array(
    'title'=> $this->l('products')

)

then, above in $this->_join define LEFT JOIN (three sseparate joins: ps_cart, ps_cart_product, ps_product_lang)

 

and above in $this->_select define select for ps_product_lang.name

  • Like 1

Share this post


Link to post
Share on other sites

Hey there,

 

thank you in advance, I'm trying it now, but I'm stuck in the "define the LEFT JOIN" part, would have to look somethin like this:

 

LEFT JOIN '._DB_PREFIX_.'cart

LEFT JOIN '._DB_PREFIX_.'cart_product

LEFT JOIN '._DB_PREFIX_.'product_lang

 

I'm asking cause others have a parenthesis later, something like:

LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = a.`current_state`)

 

cheers.

Edited by Minkiu (see edit history)

Share this post


Link to post
Share on other sites

ok i analysed code.

 

it will be much better to add order_detail table

 

here is the code:

$this->_select = '
        od.product_name AS products,
		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';

		$this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order` = a.`id_order`)
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)
		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';
  • Like 2

Share this post


Link to post
Share on other sites

THANK YOU VERY MUCH :___D

 

And now to loop the loop, what if I wanted to add a Supplier column? :D

 

 

_select='

howeversupplierasarecalled AS supplier,

 

//

 

'LEFT JOIN `'._DB_PREFIX_.'product_supplier` ps ON (a.`id_supplier` = ps.`id_supplier`)';

 

//

 

'supplier' => array(
    'title'=> $this->l('Supplier')

)

 

??

 

Thank you again, you are a life saver! :D

 

Cheers.

Share this post


Link to post
Share on other sites

Last query :E

i'm a bit lost about the word  in the _select part,

 

you used od.product , I guess it's becaouse of od stands for order? You choosed this deliveratedly? Cause i tried adr.adress1 with no luck

 

Cause now i'm trying to fetch the adress of the customer, which I don't know why its not in the customer table itself, and I'm going mad.

 

LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)

 

with this i get the id, but how to tell him to fetch the addrs1 column content?

 

something like addr.adress1 AS address should do?

 

Does all this stuff is explained in the documentation (I found something on the left join but it doesn't clarify nothing :/)? Where, cause I can't find a proper explanation; or maybe its more SQL related stuff?

 

Thank you again,

 

So sorry to be bothering again.

Edited by Minkiu (see edit history)

Share this post


Link to post
Share on other sites

it isn't described in documentation because this is pure PHP & sql

 

okay so you want to display customer address?

 

here is an example for phone:

$this->_select = '
        CONCAT(addr.phone, addr.phone_mobile) AS `phone`,
        od.product_name AS products,
	a.id_currency,
        ...

and join:

LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)

and array field:

'phone' => array(
'title' => $this->l('Phone')
),

So just use different fields in select's CONCAT command that i pasted above and give it name "address" (instead of phone)

then somewhere in field list array add 

'address' => array(
'title' => $this->l('Addres')
),
 
voila ;)
  • Like 2

Share this post


Link to post
Share on other sites

Hello again... this is embarrasing :3

 

Than k you for explanation, i't been very useful :3

 

Regarding the "show products column" code, Something weird happened, when a person ordered two different items, it duplicated the order 8with same reference, and stuff) but it appears one order for each :/

 

There must be a "for" or something like that to just keep adding them in the column instead of adding a new order?

 

Thank you again! :)

 

I shall buy you a beer TT

Share this post


Link to post
Share on other sites

  • 1 month later...

 

ok i analysed code.

 

it will be much better to add order_detail table

 

here is the code:

$this->_select = '
        od.product_name AS products,
		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';

		$this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order` = a.`id_order`)
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)
		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';

Can I simply copy and paste this into AdminOrdersController.php or, is there something more that I need to do?  I am real new at this

 

Any help would be greatly appreciated

 

Thank you much

Share this post


Link to post
Share on other sites

You need to define new field in AdminOrdersController.php

 

in fieldlist array add:

'products' => array(

    'title'=> $this->l('products')

 

)

 

 

 

+

 

here is the code:

$this->_select = '

od.product_name AS products,

        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';

 

        $this->_join = '

LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order` = a.`id_order`)

        LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)

LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)

        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';

 

Share this post


Link to post
Share on other sites

yes, open AdminOrdersController.php and follow instrucitons that i provided in this topic (remember about backup of this file).

you have to:

 

in fieldlist array add:

'products' => array(
    'title'=> $this->l('products')

)

 

and replace code with queries:

 

 

$this->_select = '
od.product_name AS products,
        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';

        $this->_join = '
LEFT JOIN `'
._DB_PREFIX_.'order_detail` od ON (od.`id_order` = a.`id_order`)
        LEFT JOIN `'
._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
LEFT JOIN `'
._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)
        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';
  • Like 1

Share this post


Link to post
Share on other sites

  • 8 months later...
  • 1 year later...
  • 3 months later...
  • 4 months later...

I have done the following:

 

Added:

$this->_select = '
addr.address1 AS `address`,

and lower down:

'state' => array( 
          'title' => $this->l('State')
       ),
then later:
 
$this->_join = '
LEFT JOIN `'._DB_PREFIX_.'address` go ON (a.`id_address_delivery` = go.`id_address`)

Im really not good at understanding MYSQL and php, and I just cant figure out how to Show the state name rather than just the ID. Any help?

  • Like 1

Share this post


Link to post
Share on other sites

OK I got it working for adding a state name.

It's actually posted elsewhere, but I will put my explanation here for others who are searching:

Im doing the same thing but I got it working.in PS 1.6.1.4

In the AdminOrdersController.php (override it is better!)
 
$this->_select = '
css.name AS `state`,
then lower down
 
    $this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'address` ca ON (a.`id_address_delivery` = ca.`id_address`)
        LEFT JOIN `'._DB_PREFIX_.'state` css ON (ca.`id_state` = css.`id_state`)
finally calling it:
 
    'state' => array(
                 'title' => $this->l('State')
             ),
 
If this doesn't make sense, have a look at the AdminOrdersController.php file and make an over-ride that includes. this.
 
Cheers, nik
  • Like 2

Share this post


Link to post
Share on other sites

  • 1 month later...
  • 3 months later...

Vekia you are a genius man.

 

I followed all steps and successed to add the products column, but (there is always a but) what if the product is a personnalized product (with text field). How to show the personnalized text in the products column ?

 

If someone can give me the solution it will be greatly appreciated... ^^

 

merci.

Edited by Hopakus (see edit history)

Share this post


Link to post
Share on other sites

  • 5 weeks later...
  • 2 weeks later...

Hello Vekia @Vekia,

 

I have managed a long time ago to have another column in orders page that showed a bubble when there were messages on a specific order. Now I am trying to show the actual messages as "alt" text when hovering that bubble.

 

I have managed to get those messages for every order in the Select part of the sql, but cant put them in the 'alt' part of the vector so they could show up when hovering

 

Any idea ?

Share this post


Link to post
Share on other sites

  • 3 weeks later...
  • 3 weeks later...
  • 1 year later...
On 13/08/2014 at 2:09 PM, karthiiiiiiiiiik said:

hi how can i do it for the suppliers,

 

i have followed by adding the supplier in array list i am getting exception

 

[PrestaShopDatabaseException]

Unknown column 'supplier' in 'field list'

 

kindly post the full code for adding supplier it would be useful

 

Hello ! did you find out how to display supplier name ??

Share this post


Link to post
Share on other sites

  • 4 months later...

Hello

 

I did manage to insert on list info such as phone ou phone mobile

 

But when i try to put "company" the result is ambiguos

 

I don´t want to enable B2B, because field "company" is beeing used for other info

Share this post


Link to post
Share on other sites

  • 8 months later...
On 12/12/2013 at 4:40 PM, vekia said:

yes, open AdminOrdersController.php and follow instrucitons that i provided in this topic (remember about backup of this file).

you have to:

 

and replace code with queries:

Hi Vekia,

could you help me, please?

I have used your code explained in this topic, but in Product column appears only one product. 

I need to views all the products for each order. 

 

Thanks a lot.



NOTHING AHAHAHAHA I have a mistake. :-D
 The code is correct, make a new row for each product in the order.

Edited by elwood85 (see edit history)

Share this post


Link to post
Share on other sites

  • 5 months later...
  • 4 weeks later...
En 12/7/2019 a las 6:59 PM, franky.martin dijo:

Hi, I would like to add costumer email to order list page. i am trying as @vekia explain in his site;

https://mypresta.eu/en/art/developer/tracking-number-orders-list.html

Any help please? I work in PS 1.7.4.4

I did it with the next code in AdminOrdersController.ph;

$this->fields_list = array(
            'id_order' => array(
                'title' => $this->trans('ID', array(), 'Admin.Global'),
                'align' => 'text-center',
                'class' => 'fixed-width-xs'
            ),
            'reference' => array(
                'title' => $this->trans('Reference', array(), 'Admin.Global')
            ),
            'new' => array(
                'title' => $this->trans('New client', array(), 'Admin.Orderscustomers.Feature'),
                'align' => 'text-center',
                'type' => 'bool',
                'tmpTableFilter' => true,
                'orderby' => false,
            ),

/* new code */

         'email' => array(
                'title' => $this->trans('Email address', array(), 'Admin.Global')
            ),
         
            'customer' => array(
                'title' => $this->trans('Customer', array(), 'Admin.Global'),
                'havingFilter' => true,
            ),
            
            
            'products' => array(
                'title'=> $this->l('products')
            )
        );

 

Share this post


Link to post
Share on other sites

  • 1 month later...
  • 1 month later...
On 10/1/2013 at 10:34 AM, vekia said:

ok i analysed code.

 

it will be much better to add order_detail table

 

here is the code:

$this->_select = '
        od.product_name AS products,
		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';

		$this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order` = a.`id_order`)
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'address` addr ON (a.`id_address_delivery` = addr.`id_address`)
		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';

Hello,

there is a probleme with thos solution.

Multi rows for a same order appears with multipe produts.

Have you an idea to make a list of products for one row order please ?

Thank you!

Share this post


Link to post
Share on other sites

  • 2 weeks later...
On 10/27/2019 at 12:42 PM, xaxa said:

Hello,

there is a probleme with thos solution.

Multi rows for a same order appears with multipe produts.

Hyou can ave you an idea to make a list of products for one row order please ?

Thank you!

To archive that, besides the solution above just

add this under select>

x.products,

and this under joints>

LEFT JOIN (SELECT s.id_order, GROUP_CONCAT(s.product_name) AS products FROM `'._DB_PREFIX_.'order_detail` s GROUP BY s.id_order) x ON x.id_order = a.id_order

 

Share this post


Link to post
Share on other sites

  • 2 months later...
  • 6 months later...
On 5/7/2016 at 1:19 AM, nikmagnus said:

this work perfect for me thanks !!!

I have done the following:

 

Added:

$this->_select = '
addr.address1 AS `address`,

and lower down:

'state' => array( 
          'title' => $this->l('State')
       ),
then later:
 
$this->_join = '
LEFT JOIN `'._DB_PREFIX_.'address` go ON (a.`id_address_delivery` = go.`id_address`)

 

 

 

 

Edited by Jose Lorenzini (see edit history)

Share this post


Link to post
Share on other sites

On 5/7/2016 at 1:36 AM, nikmagnus said:

hi guys this work perfect for me.

 

OK I got it working for adding a state name.

It's actually posted elsewhere, but I will put my explanation here for others who are searching:

Im doing the same thing but I got it working.in PS 1.6.1.4

In the AdminOrdersController.php (override it is better!)
 
$this->_select = '
css.name AS `state`,
then lower down
 
    $this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'address` ca ON (a.`id_address_delivery` = ca.`id_address`)
        LEFT JOIN `'._DB_PREFIX_.'state` css ON (ca.`id_state` = css.`id_state`)
finally calling it:
 
    'state' => array(
                 'title' => $this->l('State')
             ),
 
If this doesn't make sense, have a look at the AdminOrdersController.php file and make an over-ride that includes. this.
 
Cheers, nik

 

Share this post


Link to post
Share on other sites

  • 4 months later...
On 11/7/2019 at 9:26 PM, uruvaping said:

To archive that, besides the solution above just

add this under select>

x.products,

and this under joints>

LEFT JOIN (SELECT s.id_order, GROUP_CONCAT(s.product_name) AS products FROM `'._DB_PREFIX_.'order_detail` s GROUP BY s.id_order) x ON x.id_order = a.id_order

 

How can I do the same for suppliers? 

I know the way to add the supplier column is this one. 

 $this->_select = '

     sup.name AS supplier

 

$this->_join = '

     LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order = a.`id_order`)

     LEFT JOIN `'. _DB_PREFIX_.'product_supplier` ps ON (ps.`id_product` = od.`product_id`)

     LEFT JOIN `'._DB_PREFIX_.'supplier` sup ON (sup.`id_supplier` = ps.`id_supplier`)

 

$this->fields_list = array(

            'supplier' => array(

                'title' => $this->l('Supplier')

            ),

 

But I don't know how to create a GROUP.

 

Thank You

Edited by jotas torres (see edit history)

Share this post


Link to post
Share on other sites

On 12/29/2020 at 8:54 PM, jotas torres said:

How can I do the same for suppliers? 

I know the way to add the supplier column is this one. 

 $this->_select = '

     sup.name AS supplier

 

$this->_join = '

     LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (od.`id_order = a.`id_order`)

     LEFT JOIN `'. _DB_PREFIX_.'product_supplier` ps ON (ps.`id_product` = od.`product_id`)

     LEFT JOIN `'._DB_PREFIX_.'supplier` sup ON (sup.`id_supplier` = ps.`id_supplier`)

 

$this->fields_list = array(

            'supplier' => array(

                'title' => $this->l('Supplier')

            ),

 

But I don't know how to create a GROUP.

 

Thank You

Finally, it's this one

 $this->_join = '

       LEFT JOIN (SELECT od.id_order, GROUP_CONCAT(sup.name) AS supplier, CONCAT( GROUP_CONCAT(od.product_name) AS products

                        FROM `'._DB_PREFIX_.'order_detail` od 

                        LEFT JOIN `'. _DB_PREFIX_.'product_supplier` ps ON (ps.`id_product` = od.`product_id` AND ps.`id_product_attribute` = od.`product_attribute_id`)  

                        LEFT JOIN `'._DB_PREFIX_.'supplier` sup ON (sup.`id_supplier` = ps.`id_supplier`)

                        GROUP BY od.id_order) x ON x.id_order = a.id_order

$this->fields_list = array(

            'products' => array(

                'title' => $this->trans('Products', array(), 'Admin.Global'),

            ),

            'supplier' => array(

                'title' => $this->trans('Supplier', array(), 'Admin.Global'),

            ),

);

Edited by jotas torres (see edit history)

Share this post


Link to post
Share on other sites

On 12/12/2013 at 4:40 PM, vekia said:

yes, open AdminOrdersController.php and follow instrucitons that i provided in this topic (remember about backup of this file).

you have to:

 

and replace code with queries:

Hello Vekia, good tutorial and it is vorking with PS 1.7.6.9 Orders list and tracking number prestashop back office (mypresta.eu)

I actually wanted to insert a Carier instead of a tracking number. I just tried to change the "tracking_number" with "carier" but then I got a blank page in orders.

Changed AdminOrdersController.php php with your tutorial is attached.

Can you maybe help to get the Carier column in orders page?

PS it would be great to have tracking number and Carier...

Regards

 

AdminOrdersController.php

Edited by goldies (see edit history)

Share this post


Link to post
Share on other sites

  • 1 month later...

Hi,

Is any modyfication was made in Presta 1.7.7.0?

I'm trying to use this code with no success... :(

I want to add delivery/courier name (show delivery type by for example small delivery logo) such as courier/post/self pickup 

Share this post


Link to post
Share on other sites

  • 1 month later...
  • 2 months later...
1 hour ago, Janett said:

I found a module example for OrderGrid here: https://github.com/Matt75/displayordercarrier

Thank you.

I've also found something and after little modification see it attached.

Go to admin panel modules manager and install this module.

After installation on back office catalogue > products you'll see Hello world! hellooo 

This module has minimal amount of files modified little bit from here
https://github.com/PrestaShop/PrestaShop/issues/12437

image.png

mymodule.zip

Share this post


Link to post
Share on other sites

  • 10 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More