cascade after master

Hi,
select with 'cascade after master' works fine when using constant field elements, but in sql mode I don't got it to run.
Got no response to my previous question, so I post it here again.

...and started digging deeper in the code.

The result is: 'cascade after master' function using SQL mode does not work with MySQL!
Does anyone has experience with another SQL database in this context?

In detail:
There are column alias used when building the WHERE clause. That's allowed in most SQL environments, but NOT when using MySQL.

Syntax MySQL "A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses.... It is NOT ALLOWED to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed."

There is a workaround for that problem:
For expressions given an alias to in the SELECT list, you can also use the same expression in the WHERE clause. So the folowing first sample does'nt work, but the 2nd works!

1) select
value as value,
text as text,
concat(index,of,cascade,master) as valgrp
from YourTable
where valgrp in ('xxx')

2) select
value as value,
text as text,
concat(index,of,cascade,master) as valgrp
from YourTable
where concat(index,of,cascade,master) in ('xxx')

So when you change the construction of the $and_clause using the expression of the valgrp column in a variable instead of "AND valgroup IN" it works with MySQL. Something like this: $and_clause = $expression_of_valgrp ' IN ('.implode(',', $_valgrps).')'

Voala.
Wolfgang

Hello

i don't understand, writting a valid SYNTAX query is your work,

i mean what should FLEXIcontent do about it ??

- please explain what needs to be fixed


it is not directly related to your question, but configuration was made easier:
please see v3.0.15-rc:
github.com/FLEXIcontent/flexicontent-cck/releases

Hi,
thanks for your respond.

In your sample you use "SQL query NO", and I'm talking about option "SQL query YES"

The problem is, that you're running into an error if you use the given syntax (expression as valgrp and _valgrp_in_). In flexicontent.fields.php, checkbox.php, checkboximage.php, radio.php, radioimage.php, select.php, selectmultiple.php you are constructing the SQL query string with a variable $and_clause. Its build by substituting the constant '_valgrp_in_' from the fields definition into ' AND valgroup IN' (hard coded). Even if the "expression as valgrp" is changed to "expression as valgroup" to match the condition in the where clause, it cannot work. The reason is: MySQL doesnt allow at all to use an alias in a where clause. So I dont see a way get this working in "SQL mode" without hacking those php files.

Hello

you can propose a fix / improvement in github via pull request
github.com/FLEXIcontent/flexicontent-cck

e.g. propose to change this file / lines
github.com/FLEXIcontent/flexicontent-cck...ent.fields.php#L1584

(just use Github UI to edit a file and then propose you file change as Pull Request)

See my previous message

if you can not use github for some reason , then write here clearly which lines to change into what exactly

Ok ggppdk,
not used github for a while but will try to do this next weekend along with a country/district sample.