Create a custom model

There are many cases that we need to use Grocery CRUD with a custom query. The most common scenario is when we have complicated queries with multiple joins in the database. With the functionality of setModel you are able to create your own custom model for custom queries.

By using custom models we can have the below benefits:

  1. Control all of your queries. By simply putting it, any new request (new queries… e.t.c.) that you are getting from your business is achievable.
  2. Organising your code better. Sometimes it is better to create a file for a custom query, just in case at the future the query will get more complicated. With that good practice in mind you can easily have the freedom to create some custom queries, without having in mind the complexity of the query.
  3. It is more understandable from new users that this is the model that is used for your CRUD.

With the above benefits in mind, let’s see first of all how we can create a custom model. All the documentation for custom model, can also be found here: set Model documentation

A full example in Codeigniter Framework

The below example is with Codeigniter framework but of course as different frameworks has different queries, you can use your own one. So first of all we are creating a file with name: CustomersModel.php. We did create the code based on this generic model that you can copy in order to start with.

Please notice:
As in Codeigniter we are not using any namespaces, we did simplify the process and added the model into the folder: application/models/. But as Codeigniter doesn’t have autoload either, the simplest way to call the CustomersModel.php is by using our good old include function. So for example if your controller is looking like that:

class Admin extends CI_Controller { 
        ...

	public function customers() {
             
        }

        ...
}

You should do something like that:

class Admin extends CI_Controller { 
        ...

	public function customers() {
            $crud = $this->_getGroceryCrudEnterprise();

            $crud->setTable('customers');

            // Please notice that the APPPATH is used only in Codeigniter Framework
            include(APPPATH . 'models/CustomersModel.php');
            $db = $this->_getDbData();
            // If we are not using namespaces, we need to make sure that we are starting with "\" as
            // this way we can guarantee that it will not fail in case we will use namespaces at the future
            $model = new \CustomersModel($db);
            $crud->setModel($model);

            $crud->setSubject('Customer', 'Customers');
            $crud->columns(['customerName','contactLastName','phone','city','country','salesRepEmployeeNumber','creditLimit']);
            $crud->displayAs('salesRepEmployeeNumber','from Employeer')
                 ->displayAs('customerName','Name')
                 ->displayAs('contactLastName','Last Name');
             $crud->setRelation('salesRepEmployeeNumber','employees','lastName');

             $output = $crud->render();

             $this->_example_output($output);
        }

        ...
}

As you can also see from the code we did add two extra columns:

  1. fullname that it is the concatenation with the first/last name
  2. count_orders that the result is coming from a join of the table orders

To implement the above file, then you will simply need the below 2 lines of code:

$model = new \CustomersModel($db);
$crud->setModel($model);

And that’s basically it! No more complicated than that. You can see a full example of a custom query below:

$crud->setTable('customers');

$model = new \CustomersModel($db);
$crud->setModel($model);

$crud->unsetAdd();
$crud->setSubject('Customer', 'Customers');
$crud->columns(['fullname', 'phone', 'country', 'count_orders']);
$crud->fields(['customerName', 'contactLastName', 'phone', 'country', 'count_orders']);
$crud->readOnlyFields(['count_orders']);
$crud->displayAs('count_orders','Orders');

$output = $crud->render();

Ooooops, something went wrong! If you can see this message, this is probably a misconfiguration in Grocery CRUD Enterprise!