SQL for Browse only works with very simple SELECT queries Topic is solved

[DEMandell]

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

[kev1n]

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:

Code: Select all

$createTable  = "CREATE TABLE #TABLE_ID# "; 

$select  = "
  SELECT * FROM table
";

nuRunQuery("$createTable $select");

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

And in the Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID#

[DEMandell]

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