Create an Admin Controller for a custom SQL table (Part 1/2) [Prestashop 1.7]

Prestashop has developed a great admin interface: data table, filters, edit/create form, etc. But you know what? You can re-use this UI for your own SQL tables. This tutorial will walk through it.

If you want to edit an already existing Object such as your invoices, head over to this tutorial.

This is the part 1 of a Series to customize Prestashop in depth. Follow this link for the tutorial 2 about creating the Front Controller (Part 2/2).

Objective

We are going to be able to create, read, update and delete a customized SQL table. Let’s take some pasta as an example.

We will be using this SQL Dataset setup.sql (taken from https://www.dunnhumby.com/sourcefiles).

Let’s use the pasta database:

CREATE TABLE pasta (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sku` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `id_pasta_category` INT NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

insert into pasta (sku,name,description,id_pasta_category) values ('111112360', 'VINCENT S ORIG MARINARA S','ac facilisis facilisis, magna tellus',2);
insert into pasta (sku,name,description,id_pasta_category) values ('566300023', 'PINE MOUNTAIN SYRUP','at pede. Cras vulputate velit eu',4);
-- [...]
-- find all the SQL setup in https://github.com/floriancourgey/blog/blob/master/assets/prestashop/pasta_setup.sql

Step 1: Create PHP class

Create the ObjectModel class /override/classes/fc_pasta/Pasta.php:

<?php
class Pasta extends ObjectModel {
  public $id; // fields are mandatory for create/update
  public $sku;
  public $name;
  public $created;
  public $category;
  public $id_pasta_category;
  public static $definition = [
    'table' => 'pasta',
    'primary' => 'id',
    'fields' => [
      'sku' =>  ['type' => self::TYPE_STRING, 'validate' => 'isAnything', 'required'=>true],
      'name' =>  ['type' => self::TYPE_STRING, 'validate' => 'isAnything', 'required'=>true],
      'description' =>  ['type' => self::TYPE_HTML, 'validate' => 'isAnything',],
      'created' =>  ['type' => self::TYPE_DATE, 'validate' => 'isDateFormat'],
      'id_pasta_category' => ['type'=>self::TYPE_INT, 'validate'=>'isUnsignedInt','required'=>true,],
    ],
  ];
}

In details, folder does not matter, all properties must be public, class extends ObjectModel, and use public static $definition to define your SQL fields.

Step 2: Create Admin Controller

Create the module /modules/fc_pasta/fc_pasta.php:

<?php
if (!defined('_PS_VERSION_')) {exit;}
class Fc_Pasta extends Module {
  public function __construct() {
      $this->name = 'fc_pasta'; // must match folder & file name
      $this->tab = 'administration';
      $this->version = '1.0.0';
      $this->author = 'Florian Courgey';
      $this->bootstrap = true; // use Bootstrap CSS
      parent::__construct();
      $this->displayName = $this->l('PrestaShop Module by FC');
      $this->description = $this->l('Improve your store by [...]');
      $this->ps_versions_compliancy = ['min' => '1.7', 'max' => _PS_VERSION_];
      // install Tab to register AdminController in the database
      $tab = new Tab();
      $tab->class_name = 'AdminPasta';
      $tab->module = $this->name;
      $tab->id_parent = (int)Tab::getIdFromClassName('DEFAULT');
      $tab->icon = 'settings_applications';
      $languages = Language::getLanguages();
      foreach ($languages as $lang) {
          $tab->name[$lang['id_lang']] = $this->l('FC Pasta Admin controller');
      }
      $tab->save();
  }
}

Activate it through the PrestShop Backoffice by looking for pasta:

and then Create the Admincontroller in modules/fc_pasta/controllers/admin/AdminPastaController.php, which extends ModuleAdminController:

<?php
require_once _PS_ROOT_DIR_.'/override/classes/my_dir/Pasta.php';

class AdminPastaController extends ModuleAdminController {
  public function __construct(){
      parent::__construct();
      // Base
      $this->bootstrap = true; // use Bootstrap CSS
      $this->table = 'pasta'; // SQL table name, will be prefixed with _DB_PREFIX_
      $this->identifier = 'id'; // SQL column to be used as primary key
      $this->className = 'Pasta'; // PHP class name
      $this->allow_export = true; // allow export in CSV, XLS..
  }
}

Head to http://localhost/admin-dev/index.php?controller=AdminPasta and you should now have an empty AdminController:

If you get the error message Page not found - The controller AdminPasta is missing or invalid., make sure the tab is installed in ps_tab with:

SELECT * FROM `ps_tab` WHERE class_name = 'AdminPasta'

Step 3: Configure your Admin Controller

Now all the customization will be executed in the AdminController only. Let’s start by adding the list with $this->fields_list:

<?php
require_once _PS_ROOT_DIR_.'/override/classes/my_dir/Pasta.php';

class AdminPastaController extends ModuleAdminController {
  public function __construct(){
    parent::__construct();
    [...]
    // List records
    $this->_defaultOrderBy = 'a.sku'; // the table alias is always `a`
    $this->_defaultOrderWay = 'ASC';
    $this->_select = 'a.name as `pastaName`, cl.name as `categoryName`';
    $this->_join = '
      LEFT JOIN `'._DB_PREFIX_.'category` cat ON (cat.id_category=a.id_pasta_category)
      LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cat.id_category=cl.id_category and cat.id_shop_default=cl.id_shop)
    ';
    $this->fields_list = [
      'id' => ['title' => 'ID','class' => 'fixed-width-xs'],
      'sku' => ['title' => 'SKU'],
      'pastaName' => ['title' => 'Name', 'filter_key'=>'a!name'], // filter_key mandatory because "name" is ambiguous for SQL
      'categoryName' => ['title' => 'Category', 'filter_key'=>'cl!name'], // filter_key mandatory because JOIN
      'created' => ['title' => 'Created','type'=>'datetime'],
    ];
  }

  // As our SQL table doesn't use the _DB_PREFIX_, we have to remove it from the WHERE clause
  protected function getFromClause() {
      return str_replace(_DB_PREFIX_, '', parent::getFromClause());
  }
}

This gives us a wonderful table with export, filters, orders and pagination:

4. Create, edit and delete your Custom Object

But we still cannot create or edit our object. We have to add a new part in our controller with $this->fields_form:

<?php
require_once _PS_ROOT_DIR_.'/override/classes/my_dir/Pasta.php';

class AdminPastaController extends ModuleAdminController {
  public function __construct(){
    parent::__construct();
    [...]
    // Read & update record
    $this->addRowAction('details');
    $this->addRowAction('edit');
    $categories = Category::getCategories($this->context->language->id, $active=true, $order=false); // [0=>[id_category=>X,name=>Y]..]
    $this->fields_form = [
      'legend' => [
        'title' => 'Pasta',
        'icon' => 'icon-list-ul'
      ],
      'input' => [
        ['type'=>'html','html_content'=>'<div class="alert alert-info">Put here any info content</div>'],
        ['name'=>'sku','type'=>'text','label'=>'SKU','required'=>true,'suffix'=>'Item reference',],
        ['name'=>'name','type'=>'text','label'=>'Name','required'=>true],
        ['name'=>'description','type'=>'textarea','label'=>'Description',],
        ['name'=>'created','type'=>'datetime','label'=>'Created',],
        ['name'=>'id_pasta_category','label'=>'Category','type'=>'select','required'=>true,'class'=>'select2',
          'options'=>[ 'query'=>$categories,
            'id'=>'id_category', // use the key "id_category" as the <option> value
            'name'=> 'name', // use the key "name" as the <option> title
        ]],
      ],
      'submit' => [
        'title' => $this->trans('Save', [], 'Admin.Actions'),
      ]
    ];
  }

  // As our database doesn't use the _DB_PREFIX_, we have to remove it
  protected function getFromClause() {
      return str_replace(_DB_PREFIX_, '', parent::getFromClause());
  }
}

So we now have a nice form when we click on Create:

But it doesn’t work! You can also try to click on edit or on details, you’ll have an error:

http://localhost/admin-dev/index.php?controller=AdminPasta&id=768&updatepasta

This is the error thrown here https://github.com/PrestaShop/PrestaShop/blob/1.7.3.x/classes/controller/AdminController.php#L1655

This is a limitation of PrestaShop: if you want to edit/create/delete an object, its SQL table MUST use the _DB_PREFIX_.

So you have to rename your pasta table:

RENAME TABLE prestashop_pasta.pasta TO prestashop_pasta.ps_pasta; # adjust with your own _DB_PREFIX_

And remove the whole function getFromClause:

// protected function getFromClause() {
//     return str_replace(_DB_PREFIX_, '', parent::getFromClause());
// }

Refresh your page and you now have a fully working Form with a Custom Object:

Here we are!! There’s still much to do, as adding new fields in the Edit Form (tuto coming soon) and display those pasta in a nice Front Controller for the customers to be able to see them, you can head over here for the tutorial 2 about creating the Front Controller.

Hooray!

To display code in Disqus comment, use <pre><code> tags. For example:
<pre><code class="python">a = 'b';</code></pre>
See Disqus Syntax highlighting for details.

Create an Admin Controller for a custom SQL table (Part 1/2) [Prestashop 1.7]

Objective

Step 1: Create PHP class

Step 2: Create Admin Controller

Step 3: Configure your Admin Controller

4. Create, edit and delete your Custom Object

← Previous Project

Add variables from a Workflow in a Delivery in Adobe Campaign

Next Project →

Add a module manually with Prestashop 1.7