[dbarros]

I was wondering if there is a way to change/add an additional parameter to the setRelation method in order to pick the join type.  

setRelation(string $fieldName , string $relatedTable, string $relatedTitleField, string $joinType)

I found myself having to face challenges while creating views with the default "Left Join" option, I understand that there is a way to handle more elaborated queries with custom models, however this takes away some of the benefits of the default functionality from GCE. 

 

 

Thanks in advance for any help. 

[web-johnny]

Hello @dbarros,

 

I am not sure that I understand what you are asking for. Instead you can also use the method fieldType and specifically use the field type dropdown_search . For more you can also check: https://www.grocerycrud.com/enterprise/api-and-function-list/fieldType

 

As an example for your needs you can have something like this:

$this->db->join(...)
$rows = $this->db->get('customers')->result();

$customers = [];
foreach ($rows as $row) {
	$customers[$row->id] = $row->customer_name;
}

$crud->fieldType('customer', 'dropdown_search', $customers);

If you are looking for extra field names from the setRelation you can also do this:
 

$crud->setRelation('officeCode','offices','{city} - {phone}');

Also setRelation has a where parameter in case you would like to filter your join even more. For example:

 

$crud->setRelation('country', 'countries', '{country_code}: {name}', ['countries.name' => 'UNITED STATES']);

I hope it helped.

 

Other than that you can also use custom models. What default functionality are you missing with custom models? Maybe I can help.

 

Regards

Johnny

[dbarros]

Johnny, 

 

I will try explain better what I'm looking for.  Basically all relations created from setRelation() use by default "Left Join", I was wondering if there was a way to set the JOIN type (INNER, RIGHT, etc) instead of having the default one, I could hack Core Code but don't want to get trapped on that bad practice, which becomes a mess when a new update comes up.    This is the reason I was suggesting an additional parameter on setRelation(  … string joinType)

 

 

 

Here is another approach, Is there a way to access zend-db join()  from the crud object and modify the functionality. I’m not really familiar with that framework but ready to research if needed. 

 

 

From Zend documentation 

$select->join(
     'foo', // table name
     'id = bar.id', // expression to join on (will be quoted by platform object before insertion),
     array('bar', 'baz'), // (optional) list of columns, same requirements as columns() above
     $select::JOIN_OUTER // (optional), one of inner, outer, left, right also represented by constants in the API
);

 

 

Regarding losing functionality, I was working with custom model on a query with multiple joins and the rendering of the application was really weird the relation()search fields were messed up, I think the example that you posted above would help with the search fields.    

 

Once again thank you for such a great tool.

 

Additional Info I'm using Laravel 5.6.

[web-johnny]

I think then I will update the documentation for custom model to also include custom models with search: https://www.grocerycrud.com/enterprise/examples-3/create-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);

...

[dlaynes]

Sorry for hijacking this thread. I believe the 4th parameter of the setRelation() method should be documented in the Enterprise version

https://www.grocerycrud.com/enterprise/api-and-function-list/setRelation

 

Thanks!

 

Edit: nevermind. I had to create a custom model with the update() and validateOne() methods edited, in order to prevent SQL errors related to repeated columns, so I guess this part is not finished yet. Sorry!

[dbarros]

 

I think then I will update the documentation for custom model to also include custom models with search: https://www.grocerycrud.com/enterprise/examples-3/create-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.   

[web-johnny]

I am glad that I could help  :)