Skip to main content

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?


Notes


  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 

WHERE   (

            (IsDeleted = false)

            OR

            (

                Id not in   (

                                SELECT AccountId 

                                FROM Contact

                            )

            )

        )

        AND

        (

            (

                (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="">

         <fields>

            <field id="FirstName"/>

            <field id="LastName"/>

            <field id="CreatedDate"/>

            <field id="AccountId"/>

            <field id="Account&#46;Name"/>

            <field id="Account&#46;Id"/>

         </fields>

         <conditions>

            <condition type="param" enclosevalueinquotes="true" operator="=" field="Id" value="id"/>

         </conditions>

         <actions/>

      </model>

      <model id="AccountSearch" limit="" query="false" createrowifnonefound="false" adapter="" type="" sobject="Account" doclone="no" processonclient="true">

         <fields>

            <field id="Name"/>

            <field id="Id"/>

         </fields>

         <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"/>

         </conditions>

         <actions/>

      </model>

   </models>

   <components>

      <pagetitle model="Contact" uniqueid="sk-2NOem-71">

         <maintitle>

            <template>{{FirstName}} {{LastName}}</template>

         </maintitle>

         <subtitle>

            <template>{{Model&#46;label}}</template>

         </subtitle>

         <actions>

            <action type="savecancel" window="self"/>

         </actions>

      </pagetitle>

      <basicfieldeditor showsavecancel="false" showheader="true" model="Contact" mode="edit" uniqueid="sk-2NOem-72" buttonposition="">

         <columns>

            <column width="100%">

               <sections>

                  <section title="Basics">

                     <fields>

                        <field id="AccountId" valuehalign="" type="" optionsource="model" optionmodel="AccountSearch">

                           <searchfields>

                              <searchfield query="true" return="true" show="true" field="Name" operator="contains"/>

                           </searchfields>

                        </field>

                        <field id="FirstName"/>

                        <field id="LastName"/>

                     </fields>

                  </section>

               </sections>

            </column>

         </columns>

      </basicfieldeditor>

   </components>

   <resources>

      <labels/>

      <css/>

      <javascript/>

   </resources>

   <styles>

      <styleitem type="background" bgtype="none"/>

   </styles>

</skuidpage>

Skuid Team - Any input on this?

Also having the same problem

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,

Kartik

Kartik is correct, this is a Salesforce SOQL limitation, which can sometimes be sidestepped by breaking up the components of the OR clause into separate Models, and then joining them together with several “Field from another Model” conditions with Grouping logic.

Kartik/Zach -

Thanks for the reply and information.  Understood that using a WHERE clause in a nested WHERE expression is a SOQL limitation and that this isn’t a Skuid limitation/bug.

I didn’t do a very good job in my OP explaining what I was thinking.  I mentioned that this might be an SFDC bug but didn’t explain why.  In short, the SOQL above does not contain nested WHERE clause which is what the exception message indicates.  In my view, the SOQL above is valid and should work OR the error message is very misleading or maybe I’m just misunderstanding it.  To me, that SOQL is fairly basic.

Couple of questions/comments:

1) Given the known limitation of SOQL, do you feel this falls in to that category or should we treat this as a potential SFDC bug?

2) Zach - Regarding the fact that Skuid doesn’t give any visual or even console indication that a problem was encountered - is this something that can be improved?  From a user perspective, there’s just no data returned and unless you know that there should be and inspect the network traffic, you’d never know there was a problem with the SOQL.

In the meantime, I’ll work on workaround using the multiple model idea 🙂

Thanks guys!


Barry -


The error is actually a little confusing. It's not a nested WHERE clause that's disallowed - but rather a semi join sub-select at the nested WHERE clause level.


What that means is that you can't have use an "OR" in your grouping logic when one of your conditions is a sub-query. For your example, it seems that the grouping logic is "(1 OR 2) AND 3," where 2 is your sub-query.


Kartik

Barry, I agree that Skuid should give better visual indication of the SOQL errors - right now this is only visible through the “debug” property on Models — e.g. skuid.$M(‘MyModel’).debug will return the SOQL query error. We are planning to expose Model query errors visibly in the UI at some point.

Thanks Zach, UI visibility would be great.  In the interim, would a console.log be feasible?

Thanks again for the info Kartik, I follow you know.  

I did some reading and further testing/probing on this and it looks like, as you mention, it comes down to the way SFDC defines “nested”.  In this case it’s the use of “OR” as you indicated coupled with the need to use parenthesis (due to the AND) that results in their definition of Nesting.  That said, even using just a simple “OR” with a semi-join is not allowed. What’s interesting is that the limitation is a result of the SOQL parser not the SQL parser.  If SFDC didn’t have the extra parsing layer in there (which they do need for other reasons), SQL would handle this just fine.  Coming from a SQL background and fairly new to SFDC myself, this limitation is pretty poor in my opinion. 😦

I’m going to try to rework things with multiple models and see how it goes.

Thanks again for all your help!

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings