I am trying to execute the following MySql statement through createCommand():
SELECT cmp_id, @begin:=(LOCATE('R', rights)+1) as begin
FROM rights_view
WHERE LOCATE('R', SUBSTR(rights, @begin, 100)) > 0
GROUP BY cmp_id
This statement runs as expected in Mysql, but returns an empty array in createCommand()->queryAll().
But, if i substitute the temporary variable @begin in the WHERE clause to some fixed integer, the statement runs in Yii too. Ex:
WHERE LOCATE('R', SUBSTR(rights, 5, 100)) > 0
So does Yii/PHP prevent creating of Mysql statements in this manner, where temporary variables from the SELECT list are referred in the WHERE clause?
The above statement is just a reference example, as the actual statement is quite long.
I know that we can create an additional SELECT field instead of the WHERE clause, and perform the checking at the application level, like:
SELECT cmp_id, @begin:=(LOCATE('R', rights)+1) as begin, LOCATE('R', SUBSTR(rights, @begin, 100)) as loc
FROM rights_view
GROUP BY cmp_id
But, in my case, with the actual statement, this would result in too many rows being received from Mysql, and would like to avoid it.