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 🙂

One thought on “Sugar SubPanels and SubQueries

  1. […] Re: Custom "where" clause for Accounts Subpanel Not Working Here it's the solution : Sugar SubPanels and SubQueries […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">