As a developer, I'd love advanced WHERE conditions in QueryFactory, so that I can build faceted search pages about fflib-apex-common HOT 8 CLOSED

Below are a couple of ideas for letting a developer establish parentheses inside a of a WHERE clause. The first idea is that the 'WhereClause' class can have a ".parens(list of conditions)" method. This makes the start and end of the parens very apparent, because there are literal open and close parentheses in the source code that mark the bounds of the parenthetical that will be built. A drawback is that the list of conditions in the parameter does not feel very fluent. Deeper nesting could be done by having a .parens() method on the condition class.

A different approach is to have a method like .paren(...condition parameters...). Any 2 usages would establish the bounds of parenthetical. This could be made more explicit with method names like 'parenOpen()' and 'parenClose()'. That would also help with deeper nesting.

Any thoughts or opinions? Momentum on WHERE clause functionality would be a tremendous benefit, because of the opportunity to centralize things like string escaping and platform encryption logic (eg, changing a LIKE into an "=" or throwing a custom error if Shield encryption is on).

static void TestParens(){
    string q = '\''; 
    string deptFilter = 'Billing';      
    boolean isBind = true;

    string expected = '(Email = ' + q + '[email protected]' + q + ')'  + ' AND ((AssistantName != ' + q + 'Alice' +q+ ') OR (Department = :deptFilter))'  + ' AND (Birthdate < 17-02-1960)';
    
    whereClause w = new whereClause();
    w.add(Contact.Email, '[email protected]')
    .parens(new list<whereClause.condition> { 
                 new whereClause.condition(Contact.AssistantName, ' != ', 'Alice'),
                 new whereClause.condition('OR', Contact.Department, ':deptFilter', isBind)
             }
     )
    .add(Contact.Birthdate, '<', date.newinstance(1960, 2, 17));

    string result = w.toString();
    system.assert(result == expected);

   //more fluent alternative:
    w = new whereClause();
    w.add(Contact.Email, '[email protected]')
    .paren(Contact.AssistantName, ' != ', 'Alice')  //open...  could be followed by an .add()
    .paren('OR', Contact.Department, ':deptFilter', isBind)  //close
    .add(Contact.Birthdate, '<', date.newinstance(1960, 2, 17));

    result = w.toString();
    system.assert(result == expected);

}    
`

from fflib-apex-common.

+1, would love to see this! Its something I've run up against but just haven't had the time to explore. I'd love to see the condition APIs behave more fluently similar to the way selectField does. In other words, as you want to add conditions you can just call "addCondition" instead of a single setCondition that assumes the one and only where clause. Building the proper and/ors is not trivial but the API signatures could guide this. Would be an awesome addition to the library.

from fflib-apex-common.

That comment was actually mine, but gets credited to Andy here because of how we synced with our internal repo.

The hard things it really would need to support to have enough support to replace string-based clauses:

1. semi and anti-joins

2. nested conditionals, think ( Something = true AND ( SomethingElse = true or TheLastThing = false) )

3. apex bind variables (this is a very tricky part to fully encapsulate, so a half-measure may be appropriate)

   I feel like there's another two items I had in mind when writing that, but they escape me at the moment.

   Certainly agree with @jondavis9898 about being fluent as the best option, keeping the whole query building in a fluent pattern is a pretty handy trick.

   We also have the challenge of keeping backwards compatibility: we've exposed a string-based where clause option and need to continue to support that somehow.

   It's certainly not a small undertaking, but if you do decide to work on it I'd be happy to help in what time I can find for it.

from fflib-apex-common.

Yep indeed, @capeterson is the father of the amazing Query Factory! 👍

from fflib-apex-common.

Yes, growing the WHERE clause functionality would be very useful. The list from @capeterson of hard things to support is illuminating. I imagine the fluent usage looking something like this:

fflib_QueryFactory qf = new fflib_QueryFactory(Contact.SObjectType);
qf.Where().andCondition(new condition(field, operator, value, isBind));

for the nested above, my first thought is to have the developer type .Where again, to open a nest of parens, like this:

qf.where().andCondition(something)
.where().andCondition(somethingElse).orCondition(thelastThing)

from fflib-apex-common.

@SWhalen-gh nice design work - i am big fan of fluent model - so am naturally drawn to that - i would also use an enum for the operators. Here is another example i found https://sqlkata.com/docs also here https://www.jooq.org/ - could we get closer to these perhaps?

from fflib-apex-common.

@afawcett the sqlkata examples use either hard-coded operators, or operators suffixed on the method name:

    .Where("Likes", ">", 10)
    .WhereIn("Lang", new [] {"en", "fr"})
    .WhereNotNull("AuthorId")

enums are more self-documenting, but the sqlkata approach is appealing, because of its brevity.

The jooq examples show operators that are methods of the fields, which is very cool, but might be out of reach of Apex. This would require objects and fields that were not just strings. For example see "BOOK.PUBLISHED.gt" below:

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED.gt(date("2008-01-01")))

I went forward with operators based on enums in my local code. The benefit is that the class has total control over the vocabulary (including things like spacing and capitalization) but the downside is that they look a little wordy (eg, "whereClause.operator.LT").

    @isTest
    static void TestFluentCondition(){
        string q = '\'';  
        string expected = '(Email = ' + q + '[email protected]' + q + ') AND (AssistantName != ' + q + 'Alice' + q + ') AND (Department = :deptFilter) AND (Birthdate < 17-02-1960)';
         
        string deptFilter = 'billing';
        whereClause w = new whereClause()
        .add(Contact.Email, '[email protected]') //whereClause.operator.EQ is the default operator
        .add(Contact.AssistantName, whereClause.operator.NOTEQ,'Alice')
        .add(Contact.Department, ':deptFilter', true)//default operator; true is the value for the bind flag parameter.
        .add(Contact.Birthdate, whereClause.operator.LT, date.newinstance(1960, 2, 17));

        string result = w.toString();
        
        system.debug(loggingLevel.warn, 'expect = ' + expected);  
        system.debug(loggingLevel.warn, 'result = ' + result);
        
        system.assert(result == expected);
    }    

from fflib-apex-common.

@afawcett @ImJohnMDaniel and @daveespo discussed this thread today in real time. We'd like to propose the following:

• Because of the rather massive undertaking that this proposal would require, we would like to suggest this starts small with a strawman implementation (i.e. a few methods that demonstrate functionality and use cases)
• Rather than integrate this directly into this repository, we propose that new extension points be added to fflib_QueryFactory and fflib_SObjectSelector to support supplying a "filter builder" to be used during the SOQL generation
• The strawman implementation would be committed to its own git repo and initially maintained by the direct contributors to this first proof of concept before being added to the group that's maintaining the apex-enterprise-patterns repositories
• I'm going to close this thread since it's so old and is on the 3rd page of Issues where no one will ever see it :-)

Also going to drop a couple of links in here (thanks to @ImJohnMDaniel for providing these):

https://appexchange.salesforce.com/appxListingDetail?listingId=a0N300000057GBMEA2

Docs are here:

https://partners.salesforce.com/servlet/servlet.FileDownload?file=00P3000000P3YRrEAN

@SWhalen-gh or anyone else that's interested in taking on this initiative -- can you start a new GH Issue referencing this one to propose the integration point for a new "filter builder" and a skeleton of an API?

from fflib-apex-common.