If you start to have a lot of customers in your application, and you want to display quickly their phone numbers for example, because you need to be able to search by it, or any other field, then you can create your own module and redefine the listing.
First, let’s create a new module. honestly, the Prestashop’s documentation about it is pretty clear: https://devdocs.prestashop.com/1.7/modules/creation/
Why a module ? just because we don’t want to touch the core code, and overrides are not easy to maintain.
Module structure
<?php
if (!defined('_PS_VERSION_')) {
exit;
}
class CustomGrid extends Module
{
public function __construct()
{
$this->name = 'customgrid';
$this->tab = 'administration';
$this->version = '1.0.0';
$this->author = 'applyss';
parent::__construct();
$this->displayName = $this->trans('Colonnes custom dans les listings');
$this->description = $this->trans('Modifie les colonnes du backoffice Prestashop');
}
//...Basic structure for a module, we just define the name and some params. Now, if we check the documentation, we can find some hooks which allow to work on any listing in the backoffice:
actionCustomerGridDefinitionModifier: allow to define columns, reorder… as you may see, just change « Customer » by the listing you want to change
actionCustomerGridQueryBuilderModifier: allow to redefine the query for the listing, same, replace « Customer » by the listing you want to edit
So, let’s add the code we need to these hooks in your module, first we need to install our module and register the hooks:
public function install()
{
return (
parent::install()
&& $this->registerHook('actionCustomerGridDefinitionModifier')
&& $this->registerHook('actionCustomerGridQueryBuilderModifier')
);
}Define the new column
Then, we will define our hook functions, first, let’s add the new column « Phone », we want here to display the phones of our customer:
public function hookActionCustomerGridDefinitionModifier(array $params)
{
$definition = $params['definition'];
// add a new column after the email column
$definition
->getColumns()
->addAfter(
'email',
(new DataColumn('phone'))
->setName($this->l('Phone'))
->setOptions([
'field' => 'phone',
])
);
// for search filter
$definition->getFilters()->add(
(new Filter('phone', TextType::class))
->setAssociatedColumn('phone')
);
}Here, we just set a new column, and we place it after the « email » column. You can place it where you want.
We add the new column to the search Filter too, this way, we will be able to search and order our new column.
Get the field from database
So ok, we have a new column, now we need to fill it.
Phones are stored in ps_address database, it means, a customer can have many addresses, and we just want to display the last one for example.
So we will need to join this table, and get the phone. Prestashop uses 2 fields for phone: « phone » & « phone_mobile ». Let’s display both if filled, or one or other. Here is the complete function to do it:
public function hookActionCustomerGridQueryBuilderModifier(array $params)
{
$searchQueryBuilder = $params['search_query_builder'];
$searchCriteria = $params['search_criteria'];
// test which phone is filled, or concatenate both
$searchQueryBuilder->addSelect(
'IF(LENGTH(pa.`phone_mobile`) = 0 OR pa.`phone_mobile` IS NULL,
(IF(LENGTH(pa.`phone`) = 0 OR pa.`phone` IS NULL, "", pa.`phone`)),
(IF(LENGTH(pa.`phone`) != 0, CONCAT(pa.`phone_mobile`, "\n", pa.`phone`), pa.`phone_mobile`)))
AS `phone`'
);
// join the address table to get the phones
$searchQueryBuilder->leftJoin(
'c',
'`' . pSQL(_DB_PREFIX_) . 'address`',
'pa',
'pa.`id_customer` = c.`id_customer`
AND pa.`id_address` = (
SELECT
pa2.`id_address`
FROM
`' . pSQL(_DB_PREFIX_) . 'address` pa2
WHERE pa2.`id_customer` = pa.`id_customer`
ORDER BY LENGTH(pa2.`phone`) DESC, LENGTH(pa2.`phone_mobile`) DESC
LIMIT 1
)'
);
// order by phone, if we detect phone as a filter
if ('phone' === $searchCriteria->getOrderBy()) {
$searchQueryBuilder->orderBy('pa.`phone`', $searchCriteria->getOrderWay());
}
// search on the new field "phone"
foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
if ('phone' === $filterName) {
$searchQueryBuilder->andWhere('`phone` LIKE :phone');
$searchQueryBuilder->setParameter('phone', '%' . $filterValue . '%');
if (!$filterValue) {
$searchQueryBuilder->orWhere('LENGTH(`phone`) = 0');
}
}
}
}And here it is ! we have a new column on our customer listing, we didn’t touch any core function, which is good, and mandatory !
You can find my module template here: download the module.
Don’t forget, you can change any listing in your dashboard using this way, we just focused on the customer and their phones. Just change the hooks and columns. But you got the main idea !
Comments: 2
Hi! I follow ur tuto, and i have error on clic customer
An exception occurred while executing 'SELECT a.`id_address`, a.`firstname`, a.`lastname`, a.`address1`, a.`address2`, a.`postcode`, a.`city`, a.`company`, a.`phone`, a.`phone_mobile`, cl.`name` as country_name, IF(LENGTH(pa.`phone_mobile`) = 0, (IF(LENGTH(pa.`phone`) = 0, 0, pa.`phone`)), (IF(LENGTH(pa.`phone`) != 0, CONCAT(pa.`phone_mobile`, "\n", pa.`phone`), pa.`phone_mobile`))) AS `phone` FROM yp3m_address a LEFT JOIN yp3m_country c ON a.`id_country` = c.`id_country` LEFT JOIN yp3m_customer customer ON a.`id_customer` = customer.`id_customer` LEFT JOIN yp3m_country_lang cl ON c.`id_country` = cl.`id_country` AND cl.`id_lang` = ? LEFT JOIN `yp3m_address` pa ON pa.`id_customer` = c.`id_customer` AND pa.`id_address` = ( SELECT pa2.`id_address` FROM `yp3m_address` pa2 WHERE pa2.`id_customer` = pa.`id_customer` ORDER BY LENGTH(pa2.`phone`) DESC, LENGTH(pa2.`phone_mobile`) DESC LIMIT 1 ) WHERE (a.`id_customer` != 0) AND (a.`deleted` = 0) AND (customer.id_shop IN (?)) AND (a.`id_customer` = ?) ORDER BY id_address asc LIMIT 50' with params [2, 1, "208"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.id_customer' in 'on clause'Hello ! I see the main query is about your « yp3m_address » table, which is not the customer table I described in my small tutorial.
If you are customizing an other listing, you need to adapt the query. My query works for customer listing, and works only with the customer table.