Jump to content


dbarros

Member Since 14 Apr 2017
Offline Last Active Sep 12 2019 01:11 PM
-----

#16327 Change default Join Type setRelation()

Posted dbarros on 13 June 2018 - 02:24 PM

 

I think then I will update the documentation for custom model to also include custom models with search: https://www.groceryc...te-custom-model :) . I will let you know once this is done.

 

Till then I will try to explain you some complicated parts of the setRelation (and that's why I can't get you a straight answer).

 

Grocery CRUD Enterprise has as a first priority (with comparison from community edition) two things:
1. Performance

2. Security

So in order to have a much better performance for setRelation we are NOT using any JOIN if that is not necessary (wait what?). Yes! That's true :) I will explain you what I mean with a real example: 

Let's say that we have 1,000,000 rows in our datagrid and we have a simple relation like this:
 

$crud->setRelation('country', 'countries', 'name');

Then the query in our to get the first 10 rows from the database is the below:
 

SELECT `customers`.`customer_name` AS `customer_name`, `customers`.`country` AS `country` FROM `customers` ORDER BY `country` ASC LIMIT 10 OFFSET 0

Query execution time: 0.0020740032196s (for 1M rows with relation!!)

 

Now as you can see there is no JOIN anywhere and the country ids are getting filled with JavaScript as we already know their values :)

Now on the other hand if we do the same query in community edition you will take something like this (I am not coyping the real one as the community doesn't have a profiler that I can export the queries):

SELECT `customers`.`customer_id` AS `customer_id`, `customers`.`customer_name` AS `customer_name`, `customers`.`country` AS `country` 
    FROM `customers` 
LEFT JOIN `countries` ON `customers`.`country` = `countries`.`iso` LIMIT 10 OFFSET 0

Query execution time: 102.174100876s  (without indexing of country) (50,000X slower in comparison with grocery CRUD Enterprise)

And if I change the indexing to be correct in the database the same query will take:

Query execution time: 78.6532018185s (with indexing on country field)

 

So the difference here is huge that's the main reason that I am trying to not use any JOIN if it is not necessary. I hope this gave you a bit more guidance about what you would like to do and the approach that you would like to follow.  If you would like to investigate to the queries you can add a custom model that extends the Model and see the queries that they are actually being made:

Step1. Create a custom model that will look like this:

 

<?php

use GroceryCrud\Core\Model;

class CustomModel extends Model {

}

Step2. Enable profiler with  a code like this:

 

$model = new CustomModel($database);

$model->setDefaultProfiler();

$crud = new GroceryCrud($config);
$crud->setModel($model);

...

Thanks Johnny  @web-johnny for the explanation. I will use your recommendations.   




#16281 Got Error Set custom Model

Posted dbarros on 15 May 2018 - 12:30 PM

I believe you are required to pass the database configuration when creating a new custom model. So, you shouldn't load it as a Codeigniter model, since it is an external class or library.

Agreed with @dlaynes  statement. 

 

Look at the code below.  This additional function has to be added on your controller. 

 private function _getDbData(){

    return [
        'adapter' => [
            'driver' => 'Pdo_Mysql',
            'database' => 'db_name',
            'username' => 'db_username',
            'password' => 'db_password',
            'charset' => 'utf8'
        ]
    ];

}


public function setProject()
{
    include(APPPATH . 'models/Cust_proj.php');
// $this->load->database();
// $this->load->model('cust_proj');
    $model = new \Cust_proj($this->_getDbData);
    $crud->setModel($model);
    $crud->setTable('tbl_data_project');
}