browsesql browse form issue

[Timo]

I have two tables that look like this:

Table Notes:

Code: Select all

╔══════════╦═════════════════╗
║ nid      ║    forDepts     ║
╠══════════╬═════════════════╣
║ 1        ║ 1,2,4           ║
║ 2        ║ 4,5             ║
╚══════════╩═════════════════╝

Table Positions:

Code: Select all

╔══════════╦═════════════════╗
║ id       ║    name         ║
╠══════════╬═════════════════╣
║ 1        ║ Executive       ║
║ 2        ║ Corp Admin      ║
║ 3        ║ Sales           ║
║ 4        ║ Art             ║
║ 5        ║ Marketing       ║
╚══════════╩═════════════════╝

This is the sql for the Browse Form:

Code: Select all

SELECT  a.nid, GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
INNER JOIN Positions b
ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP   BY a.nid

The output will look like this:

Code: Select all

╠══════════╬════════════════════════════╣
║ 1        ║ Executive, Corp Admin, Art ║
║ 2        ║ Art, Marketing             ║
╚══════════╩════════════════════════════╝

Issue: The browse form returns no rows.

nuCurrentProperties().browse_sql shows a completly messed up sql. How to make this work?

Code: Select all

SELECT a.nid, a.field1, a.field2 -- and all other fields (a.*)
FROM Notes a
INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid
WHERE 1
ORDER BY b.id) DepartmentName
FROM Notes a
INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid

[admin]

Timo,

What do you mean by this?

nuCurrentProperties().browse_sql shows a completly messed up sql

Steven

[Timo]

nuCurrentProperties().browse_sql shows a completly messed up sql

Please compare the two sql statements I posted. The latter is the one that got "mixed up".

[admin]

Timo,

nuBuilder has a fairly simple SQL parser.

If you want to use a complex statement like your's you might find the best way is to create a VIEW and then refer to that.

Steven

[Timo]

Got it. I could work it out with a temporary table.

[admin]

Timo,

Yes, that's another good alternative.

Steven