Jump to content
giwrgos

How to add an extra column in Orders table?

Recommended Posts

Hello i would like to add an extra column in Orders table in the back office. I want to add next to the customer the column of the product name. how i can do it? thanks a lot

Edited by giwrgos (see edit history)

Share this post


Link to post
Share on other sites

Hello,

 

I'm looking for the same solution.

 

Regarding vekia question could it "nest"  all the items in the order row ?

Something like in the attatchment might be possible?

 

Cheers.

 

 

post-633708-0-81671200-1380531823_thumb.jpg

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

)

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

yea something like that :) now you can do everything you want ;D

  • Like 1

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

hello

 

thank you for information, 

im going to test it, 

 

so i have to create order with 8 products inside cart ?

Share this post


Link to post
Share on other sites

Well no need to be 8, but in this case were two different products, and one of them had a quantity of 2.

 

If you place an order with 8 different, probably will end up with 8 dupped orders :3

 

Thanks!

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

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

So, I need to add both ?  or, just the bottom code?

 


It is such a pain when we try to determine what a particular customer ordered without opening each order each time

 

Thank you very much again

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

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

Edited by karthiiiiiiiiiik (see edit history)

Share this post


Link to post
Share on other sites

Hello i would like to add an extra column in Orders table in the back office. I want to add next to the customer the column of the email and carrier express . how i can do it? thanks a lot

Share this post


Link to post
Share on other sites

Hi there,

 

Im trying to get the State listed in my Orders list in Back-end.

Can anyone help me with what to add to my AdminOrdersController.php override?

 

Thanks in advance

nik

Share this post


Link to post
Share on other sites

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?

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 1

Share this post


Link to post
Share on other sites

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

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

Hello,

 

me i want to add a column with the total of ecotax of the order and i didn't succeed for now

Someone has an idea please ?

Share this post


Link to post
Share on other sites
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

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

We want to add new column in order table like "Product name" of prestashop backoffice.

We have tried the above code. But it showing the "500 server error".

Can you any one share the code for this problem.

Thank you,

Regards,

Sundaraganapathi M.

 

Share this post


Link to post
Share on other sites
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
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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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