nuBuilder Forum

Greetings,

I'm trying to set up the SQL to provide choices for browsing in a Browse and Edit form, but the only thing that works are the simplest SELECT queries. Perfectly valid SQL that works in mysql in a terminal window including a JOIN or other SQL clauses doesn't work. Why is this? Is there some way around it? In particular, I have some tree-structured data that I would like to be able to browse with a computed name that contains the concatenation of the elements that form a path (for example, root:level1:level2) and it's pretty straightforward to calculate these names using a Common Table Expression which works fine in mysql 8.0 in a terminal, but yields nothing when that SQL is entered into the Browse form. I then tried pre-computing the concatenated names into another table and trying to JOIN them in a query, but that doesn't work either.

What am I missing? Is there some other way to use a custom arbitrary chunk of SQL as long as it returns IDs that nuBuilder know about?

Thanks,
--Douglas Mandell

Hi,

The nuBuilder's SQL parser has certain limitations when it comes to handling complex SQL queries. To address this, you can take advantage of PHP to create a temporary table or opt to establish a VIEW.

To set up the temporary table, use the PHP "Before Browse" event:

(It's important to know is that the query must select the table's primary key)

And in the Browse SQL:

Hi,

The simplest solution is to use a VIEW, which conceptually is exactly what I'm trying to do: view a table through a different filter, and that works fine. Thanks for the suggestion. Not sure why I didn't think of that...

Thanks,
--Douglas