Sugar SubPanels and SubQueries

Sugar allows you to specify a where clause in a subpanel This is useful if you want to show only certain records in the subpanel, or to filter the subpanel results, as detailed in my blog entry Filtering Sugar Subpanels.

While it’s a useful feature, it does have a problem – subqueries. If you specify something like this:

"where" = "id in (select id from some_table where field='xyz')"

Sugar will make a mess of your subquery. There’s an easy solution. Sugar handles query rewriting based on the strings “where” or “WHERE”. So, all you need to do is write your “WHERE” in mixed case, like so:

"where" = "id in (select id from some_table WhErE field='xyz')"

In my experience most, if not all, database servers will happily run the query with the where clause written as “WhErE”. Problem solved 🙂