cascade after master

More
8 years 3 months ago #63090 by prathumwan
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

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago - 8 years 3 months ago #63091 by ggppdk
Replied by ggppdk on topic cascade after master
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










-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...
Attachments:
Last edit: 8 years 3 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #63092 by prathumwan
Replied by prathumwan on topic cascade after master
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.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago - 8 years 3 months ago #63093 by ggppdk
Replied by ggppdk on topic cascade after master
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)


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...
Last edit: 8 years 3 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #63094 by ggppdk
Replied by ggppdk on topic cascade after master
See my previous message

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


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #63095 by prathumwan
Replied by prathumwan on topic cascade after master
Ok ggppdk,
not used github for a while but will try to do this next weekend along with a country/district sample.

Please Log in or Create an account to join the conversation.

Moderators: vistamediajoomlacornerggppdk
Time to create page: 0.289 seconds
Save
Cookies user preferences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accept all
Decline all
Essential
These cookies are needed to make the website work correctly. You can not disable them.
Display
Accept
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline