Back-office product list

Add column to the Prestashop 1.5.4 Product List

20 November, 2013

The main idea is to extend the functionality of the product list to display a new column with a selected feature. We’ll show a text field stored as product feature. These are the steps to follow:

  • Selecting the item to show: prestashop > ps_feature_lang > id_feature. In the example shown here id_feature = 7 = shopname.

phpMyAdmin

  • Making the expanded query to the database: The MySQL query on the current Prestashop 1.5.4.1 version is:
SELECT SQL_CALC_FOUND_ROWS
a.`id_product`,b.name as name,`reference`,a.price as price,a.active as active
, MAX(i.id_image) id_image,cl.name `name_category` , a.`price`, 0 AS price_final, sav.`quantity` as sav_quantity, a.`active`
FROM `ps_product` a
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product` AND i.cover=1)
LEFT JOIN `ps_category_lang` cl ON (a.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 1)
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 )
WHERE 1
GROUP BY a.id_product
ORDER BY a.id_product ASC LIMIT 0,50

Using MySQL Workbech we see the following results:

default query

The next thing to do is to introduce three new lines (fp.`id_feature` = 7):

SELECT SQL_CALC_FOUND_ROWS
a.`id_product`,b.name as name,`reference`,a.price as price,a.active as active
/* new */
, fl.value `feature`
, MAX(i.id_image) id_image,cl.name `name_category` , a.`price`, 0 AS price_final, sav.`quantity` as sav_quantity, a.`active`
FROM `ps_product` a
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product` AND i.cover=1)
/* new */
LEFT JOIN `ps_feature_product` fp ON (fp.`id_product` = a.`id_product` AND fp.`id_feature` = 7)
LEFT JOIN `ps_feature_value_lang` fl ON (fp.`id_feature_value` = fl.`id_feature_value` AND b.`id_lang` = fl.`id_lang`)
LEFT JOIN `ps_category_lang` cl ON (a.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 1)
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 )
WHERE 1
GROUP BY a.id_product
ORDER BY a.id_product ASC LIMIT 0,50

extended query

  1. 1.Defining column where you will see results.
  2. 2.We select the category IDs 7 and export their values ​​in the language selected by the user.
  3. 3.Result shown.
  • Enter the query in Prestashop PHP: The file that controls how is displayed the product list (Catalog> Products) is in:

prestashop/controllers/admin/AdminProductsController.php

Edit the file to add lines 164-172 and 233-239 in which we see:

/* START: add column to product list: new feature */
/* configuration parameters */
define('_ID_FEATURE', 7);
define('_ID_FEATURE_WIDTH', 'auto');
define('_ID_FEATURE_TITLE', 'Features:');
/* database query */
$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'feature_product` fp ON (fp.`id_product` = a.`id_product` AND fp.`id_feature` = '._ID_FEATURE.') ';
$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'feature_value_lang` fl ON (fp.`id_feature_value` = fl.`id_feature_value` AND b.`id_lang` = fl.`id_lang`) ';
/* END: add column to product list: new feature */

In the first block (configuration parameters) we introduce the characteristic variables we want to show, ID, column size and title of it. Then put the two displayed LEFT JOIN queries shown before.

/* START: add column to product list: new feature */
$this->fields_list['feature'] = array(
'title' => $this->l(_ID_FEATURE_TITLE),
'width' => _ID_FEATURE_WIDTH,
'filter_key' => 'fl!value',
);
/* END: add column to product list: new feature */

The array fields_list includes items to show. We add to the array the above lines to include the new information.

We can go now to the Prestashop back-office and see the end result: Catalog> Products

Back-office product list

We can see a new column with the feature value that each product has. We changed Prestashop core code so take care when updating its code as these updates may overwrite the file and changes will be lost.

Files: snippets.zip

Regards.

    Tags: , ,

    7 Comments

    1. Quentin says:

      Hi,

      Thanks for this guide.
      Do you know how to show all the features, not just one (with the ID we provide). Separated with “-” for exemple.
      Best regards,
      uentin

    2. varan says:

      Como comentan arriba, alguna formade mostrar más de una o todas?

        • ES: Siguiendo mis instrucciones se muestra 1 columna. De la misma forma se pueden mostrar otras columnas; solo hay que buscar los campos necesarios de las tablas de la base de datos. Solo recordar que cuantas más columnas añadamos, mas scroll horizontal tendremos (algunas veces molesto).
        • EN: Following my instructions 1 column is shown. Likewise other columns can be displayed; you just have to find the necessary fields of the tables in the database. Just remember that the more columns you add, the more horizontal scroll you have (sometimes annoying).
    3. Atiqah says:

      Hi,
      I am trying to add a new column to my product catalog for wholesale price. So, I am following your way. The column is added, but when I’m trying to save edited product details, the screen go blank. It seems that my AdminProductsController.php cannot be edited. Is there any other way to add a new column? Thanks for help.

    4. Name *Calvin says:

      thank you for the tutorial, it still works in 1.6.1.8, however i would like to add more than one features column, yet when i add one more line

      LEFT JOIN `psxs_feature_product` fp ON (fp.`id_product` = a.`id_product` AND fp.`id_feature` = 6)
      LEFT JOIN `psxs_feature_product` fp ON (fp.`id_product` = a.`id_product` AND fp.`id_feature` = 6)

      it will return error not unique table/alias: ‘fp’

      can you help me please?

    Leave a Comment