SOQL exception on Model that contains custom condition group logic with subselect query

  • 1
  • Problem
  • Updated 3 years ago
  • Solved
  • (Edited)
When a model contains custom condition group logic and when there is a subselect condition, when the user inputs search criteria (e.g. part of a name), the following exception is encountered:

Error:Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.

The SOQL generated by Skuid doesn't really align with the error message as it appears that it should be valid SOQL.  Unfortunately, an exception is encountered nontheless.  Possibly this is a SFDC bug?

1) When the exception is encountered, no visible message is ever displayed to the user - instead, the user is just left to think that no results were found.  You must check the response (in the Network tab) to see the actual error.
2) There must be more than one condition on the model and it must have custom grouping logic.  This is is causing Skuid to group the custom logic with parens and then add an AND with the "Like '%value%'" for each search field.  It's this extra clause that is grouped separately from the model conditions group that causes the SOQL to fail.  The generated SOQL seems valid - SFDC bug?

This is the generated SOQL that fails:
SELECT Name,Id FROM Account 
            (IsDeleted = false)
                Id not in   (
                                SELECT AccountId 
                                FROM Contact
                (Name LIKE '%foobar%')

Steps to reproduce:
1) Create page using XML below
2) Preview page picking existing account
3) Type a value in to the Account field to cause a search to occur

Expected Result
Search results are returned based on value input

Actual Result
"Error:Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions." is encountered although not visible anywhere to user.

Sample Page XML

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Contact">   <models>
      <model id="Contact" limit="1" query="true" createrowifnonefound="false" sobject="Contact" adapter="" type="">
            <field id="FirstName"/>
            <field id="LastName"/>
            <field id="CreatedDate"/>
            <field id="AccountId"/>
            <field id="Account.Name"/>
            <field id="Account.Id"/>
            <condition type="param" enclosevalueinquotes="true" operator="=" field="Id" value="id"/>
      <model id="AccountSearch" limit="" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="no" processonclient="true">
            <field id="Name"/>
            <field id="Id"/>
         <conditions logic="1 OR 2">
            <condition type="fieldvalue" value="false" enclosevalueinquotes="false" field="IsDeleted"/>
            <condition type="join" value="" operator="not in" enclosevalueinquotes="true" field="Id" joinobject="Contact" joinfield="AccountId"/>
      <pagetitle model="Contact" uniqueid="sk-2NOem-71">
            <template>{{FirstName}} {{LastName}}</template>
            <action type="savecancel" window="self"/>
      <basicfieldeditor showsavecancel="false" showheader="true" model="Contact" mode="edit" uniqueid="sk-2NOem-72" buttonposition="">
            <column width="100%">
                  <section title="Basics">
                        <field id="AccountId" valuehalign="" type="" optionsource="model" optionmodel="AccountSearch">
                              <searchfield query="true" return="true" show="true" field="Name" operator="contains"/>
                        <field id="FirstName"/>
                        <field id="LastName"/>
      <styleitem type="background" bgtype="none"/>
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,266 Points 10k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Kartik Sethuraman

Kartik Sethuraman

  • 798 Points 500 badge 2x thumb
Official Response

Unfortunately this is a Salesforce limitation - not a skuid one. If you attempt to run the code in the developer console, you'll get the same error.

The workaround is to split the query into different models. For your example Barry, I would create one model whose SOQL query is "SELECT AccountId from Contact." Then in your existing model, you'd update the "Id IN ...." condition to be "field from another model" rather than a subquery.

Hope that helps,