Customize the available sql operators

[sergant210]

What does it do?

It allows to manage the list of sql operators. To do this you need to add two system settings - allowed_sql_operators and denied_sql_operators.

Why is it needed?

This PR give me the ability to do like that

$q = $modx->newQuery('modResource');
$q->where('FIND_IN_SET("' . $tag . '",`tags`)'); // FIND_IN_SET is not included in the allowed sql operator list by default

All I need is to set the allowed_sql_operators system setting in JSON format: ["FIND_IN_SET", "ANOTHER_SQL_OPERATOR"]

P.S. It also simplifies the usage of the pdoTools library:

[[!pdoResources?
    &where=`{ "parent":2, "0":"FIND_IN_SET({$tag}, `tags`)" }`
]]

Related issue(s)/PR(s)

?

[wshawn]

Not sure there is any significance to this, where an IN or NOT IN can be used in the current xPDO.

[sergant210]

@wshawn Do you really understand how the FIND_IN_SET operator works?

I showed only one example. There are many SQL operators that are not allowed in MODX by default.

[wshawn]

It is functionally similar to IN without the casting IN performs. If memory serves, FIND_IN_SET is also much heavier at the query level, doesn't use indexes, and easily breaks if commas are used. Lastly it is limited in MySQL to 64 items in a set.

This is off the top of my head.

[Kossin7]

Hello! mySql have add cool function works with JSON https://dev.mysql.com/doc/refman/5.7/en/json.html

But in xpdo don't have this functions https://github.com/modxcms/xpdo/blob/3.x/src/xPDO/Om/xPDOQuery.php#L45-L67

This very need function example in pdoTools: in table site_content (object modResource) have colum "parms", he is JSON type {"price": "6300", "discount": "2", "show_main": "1"} in pdoResources where we may write SQL

{'!pdoResources ' | snippet :[
    'tpl' => 'product',
    'parents' =>2,
    'where' => "parms->\"$.show_main\" = '1'",
]}
// is call pdoResources on Fenom parser

This is call not work, because xpdo do not support work with JSON.

In this theme have PR. This code works fine with it

But if you do not want to accept PR, then add work with JSON fields

P.S. PR is good :)

[wshawn]

@Kossin7 Currently, we use xPDO::toJSON and xPDO::fromJSON to save the JSON to a text field. I understand MODX3 has a minimum MySQL version of 2.6, I do not know if xPDO has a minimum established, but if it is 2.6 then 2.7 functions may not be possible at this time.

[Kossin7]

@wshawn , it's so bad... MODX3 maybe we will get in 2090 year, but functions need now.... or next version MODX 2.6.2