setRelation WHERE clause issues

[iradave]

Hi @Johnny,

Working with setRelation this doc page shows the availability of a fourth parameter for a WHERE clause.

$crud->setRelation('officeCode', 'offices', 'city', ['is_deleted' => 'no'])

However two issues I have encountered:

1. using the [ and ] does not work - it throws a ParseError: syntax error, unexpected token “=”, expecting “]”

For example when using this code:

$crud->setRelation('ParentCompanyID','company','{Name} (ID:{CompanyID} IsParent:{IsParent})',[IsParent = '1']);

While this code with double quotes (since single quotes inside) instead of [ ] does work as desired:

$crud->setRelation('ParentCompanyID','company','{Name} (ID:{CompanyID} IsParent:{IsParent})',"IsParent = '1'"); 

And for what it is worth, I don’t think the => is a valid MySQL conditional operator.

2. I cannot use an OR in the WHERE without adding parentheses…

$crud->setRelation('ParentCompanyID','company','{Name} (ID:{CompanyID} IsParent:{IsParent})',"IsParent = '1' OR ParentID IS NULL");	

Results in " Something went wrong! Either the user doesn’t have access to this row, either the row doesn’t exist."

While this appears to work fine

$crud->setRelation('ParentCompanyID','company','{Name} (ID:{CompanyID} IsParent:{IsParent})',"(IsParent = '1' OR ParentID IS NULL)");	

This is a more minor issue but worth noting.

What is the difference between setRelation and setRelationDynamic? Does setRelation only do the lookup the first time the CRUD is loaded while the dynamic version does it on every record? Or something like this? Any advice when to use one versus the other?

Lastly, I would add my “vote” to have the ability to set ORDER again in v3. I do note in this forum item you said it causes a performance hit. How much of a hit?

Thanks,

Ira

[bgeneto]

Hi @iradave !

1. using the [ and ] does not work - it throws a ParseError: syntax error, unexpected token “=”, expecting “]”

$crud->setRelation('ParentCompanyID','company','{Name} (ID:{CompanyID} IsParent:{IsParent})',[IsParent = '1']);

This is not a GCE issue, it is an invalid PHP syntax for an array. The 4th parameter in setRelation() should be a valid associative array (or a string). The valid array syntax in our case would be:

['IsParent' => '1']

Keep in mind that we are programming in PHP, so:

And for what it is worth, I don’t think the => is a valid MySQL conditional operator.

But it is valid PHP that can be converted to a valid SQL query syntax.

While this code with double quotes (since single quotes inside) instead of [ ] does work as desired:

Using double quotes like in "IsParent = '1'" works because it is a valid PHP string (not array).

2. I cannot use an OR in the WHERE without adding parentheses…

Could not reproduce this problem. For me not using the parenthesis works, but it makes search filter behaves randomly.

What is the difference between setRelation and setRelationDynamic?

I have no idea… only @johnny could answer that.

Lastly, I would add my “vote” to have the ability to set ORDER again in v3.

Are you using the latest v3 version? Have you set the option 'optimize_sql_queries' => false ?

Regards.

[iradave]

Hi @bgeneto,

Thank you for the reply.

As to using associative arrays for the WHERE clause, I was unable to find documentation on this via a quick web search. All I found about using arrays was using them as a WHERE IN clause. (One sample.)

For example, using an array it isn’t clear how to build a clause with other conditionals like OR, AND, IS NULL, etc. But your answer in the simple case of => being an array operator makes sense. Any pointers on good documentation of how to build the WHERE clause with an array?

Hopefully @Johnny will answer the setRelationDynamic question for all of us.

And hopefully he can also explain optimize_sql_queries further from the v2 to v3 migrate page:

• PHP Configuration optimize_sql_queries is now defaulting to false. As currently this is used for relational queries, it is more important to have functionalities like ordering to work as expected and to set it to true exceptionally when you have big relational data. Also, since we are now providing the new function setRelationDynamic we are able to optimize the queries for relational data anyway, so we can safely assume that this configuration is needed only on exceptional cases.

(I didn’t find any other documentation on this setting…)
Does this mean if we use the Dynamic version than we can ignore it? Does it mean if is set to TRUE, then there will be problems with ordering? This entire paragraph raises more questions to me than is answers unfortunately.

Thanks,

Ira

[johnny]

Hello @iradave ,

Yes, using the optimize_sql_queries to true will have issue with the ordering. I will explain what optimize_sql_queries and I can also explain what setRelationDynamic is doing to solve this problem.

So let’s say we have the below tables:

Authors Table

AuthorID (Primary Key)	Name	Country
1	John Smith	United States
2	Jane Doe	Canada
3	Bob Johnson	United Kingdom

Books Table

BookID (Primary Key)	Title	AuthorID (Foreign Key)
101	Book A	1
102	Book B	1
103	Book C	2
104	Book D	3
105	Book E	3

When you use the optimize_sql_queries = false then this will JOIN this TABLE like this:

SELECT Books.BookID, Books.Title, Authors.Name
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

Although if you have the optimize_sql_queries = true then you simply have this:

SELECT * FROM Books;

So the AuthorID is returned as id and then everything else is happening in the frontend. So in short, if you are using setRelation then all the data of the Books are loaded at the first call. So if you have 1000 books that will not be noticable. But if you have more than 10.000 ones this will delay the initial grid to load.

Here comes the setRelationDynamic to solve this issue. All the data (no matter your optimize_sql_queries is true or false) is loaded like this:

SELECT * FROM Books;

And everything within the grid is loaded dynamically. In order to understand it better see the below screenshot:

In the above example when I am searching for “Pa” then a request is going through the server to bring the list. Although in the setRelation the list is already there from the very beginning:

I have also created a table to help you more:

Use Case	optimize_sql_queries	Datagrid Ordering	Fast initial load
SetRelation - Big Relational Database	False	Yes	No
SetRelation - Big Relational Database	True	No	No (only the datagrid will load fast but it will take time to load all the data with SetRelation)
SetRelation - Small Relational Database	False	Yes	Yes
SetRelation - Small Relational Database	True	No	Yes
SetRelationDynamic	False	Yes	Yes
SetRelationDynamic	True	No	Yes

I hope now it is a bit more clear.

Regards
Johnny

[johnny]

So in short if you use SetRelationDynamic to big relational tables and optimize_sql_queries to false you can have everything you need. The only slow part will be the ordering of a relational column but this is acceptable anyway since the only way I know that you can do that is with JOIN tables. If this is the case try to INDEX correctly your tables and it will be fast enough

[iradave]

Hi @Johnny,

Thanks! That explanation is very helpful. As my data set is < 10K records, I think I’m all good with Dynamic and optimize_sql_queries set to false.

Do you have a good pointer on passing the WHERE clause as an associative array as mentioned by @bgeneto that may answer my follow on questions above?

Thanks again for all the great support!

Ira

[bgeneto]

There where clause syntax is described here with examples like that:

$crud->where([
    'customers.country > ?' => 'USA'
]);
$crud->where([
    'customers.country >= ?' => 'USA'
]);
$crud->where([
    'customers.country < ?' => 'USA'
]);
$crud->where([
    'customers.country <= ?' => 'USA'
]);
$crud->where([
    'customers.country LIKE ?' => '%USA%'
]);

The above translates to the following array syntax to be used as the 4th parameter to setRelation():

[ 'customers.country > ?' => 'USA']
['customers.country >= ?' => 'USA']
...
['customers.country LIKE ?' => '%USA%']

If you need more advanced usage then you can use the Laminas DB syntax.

Regards.