Filtering SOQL results with optional conditionalsHow to Apply a DateTime Filter when using SOQL via REST...

Does a warlock using the Darkness/Devil's Sight combo still have advantage on ranged attacks against a target outside the Darkness?

Vocabulary for giving just numbers, not a full answer

Do I really need to have a scientific explanation for my premise?

How is the wildcard * interpreted as a command?

Accepted offer letter, position changed

Virginia employer terminated employee and wants signing bonus returned

Shifting between bemols (flats) and diesis (sharps)in the key signature

Could you please stop shuffling the deck and play already?

Motivation for Zeta Function of an Algebraic Variety

Declaring and defining template, and specialising them

Examples of a statistic that is not independent of sample's distribution?

Does this video of collapsing warehouse shelves show a real incident?

How can The Temple of Elementary Evil reliably protect itself against kinetic bombardment?

Can Mathematica be used to create an Artistic 3D extrusion from a 2D image and wrap a line pattern around it?

What problems would a superhuman have whose skin is constantly hot?

In the late 1940’s to early 1950’s what technology was available that could melt a LOT of ice?

How many characters using PHB rules does it take to be able to have access to any PHB spell at the start of an adventuring day?

List elements digit difference sort

How strictly should I take "Candidates must be local"?

Reverse string, can I make it faster?

Can one live in the U.S. and not use a credit card?

What does "the touch of the purple" mean?

Why was Goose renamed from Chewie for the Captain Marvel film?

Does "Until when" sound natural for native speakers?



Filtering SOQL results with optional conditionals


How to Apply a DateTime Filter when using SOQL via REST APIMultiple SOQL queries in single requestWhy is my SOQl Query with OR filtering this much slow?Filter and search is not workingSOQL Statement with Uppercase FunctionSOQL: Differing results when adding a relationship fieldSOQL query not returning resultsUsing INCLUDES multiple times in a SOQL queryUse SOQL to select records filtered by lookup fieldFiltering results of an SOQL query













2















I am creating a web component that allows users to filter the results of some data based on some optional filtering fields. In SOQL, how do you handle returning results when a filter is not set?



For example, I have a Student__c object. I want users to be able to filter by the the Student__c's Field A (String), Field B (Integer) and Field C (String).



I have this function:



function getStudents(nationality, school, birthYear) {
return [SELECT * FROM Student__c WHERE ....];
}


I call this function to load some data to display in a graph to a user. The user doesn't have to filter the students by nationality, school, and birthYear, so by default, these values will be null, and should return the results of all students. But if the user opts to filter by birthYear, then it should filter by birthYear. If they want to filter by birthYear and school, then it should do so.



I'm trying to avoid having to do something like:



function getStudents(nationality, school, birthYear) {
if (nationality == null && school == null, && birthYear == null) {
return [SELECT * FROM Student__c];
} else if (nationality != null && school == null, && birthYear == null)
return [SELECT * FROM Student__c WHERE nationality__c == :nationality];
}
else if ( .... ) {
...
}


Instead of having different queries for the different scenarios (3 field filters is 8 different queries, 4 fields is 15 different, etc.) I would like my query to handle it all. I was thinking about using a contains, but SOQL doesn't have that from what I believe...










share|improve this question





























    2















    I am creating a web component that allows users to filter the results of some data based on some optional filtering fields. In SOQL, how do you handle returning results when a filter is not set?



    For example, I have a Student__c object. I want users to be able to filter by the the Student__c's Field A (String), Field B (Integer) and Field C (String).



    I have this function:



    function getStudents(nationality, school, birthYear) {
    return [SELECT * FROM Student__c WHERE ....];
    }


    I call this function to load some data to display in a graph to a user. The user doesn't have to filter the students by nationality, school, and birthYear, so by default, these values will be null, and should return the results of all students. But if the user opts to filter by birthYear, then it should filter by birthYear. If they want to filter by birthYear and school, then it should do so.



    I'm trying to avoid having to do something like:



    function getStudents(nationality, school, birthYear) {
    if (nationality == null && school == null, && birthYear == null) {
    return [SELECT * FROM Student__c];
    } else if (nationality != null && school == null, && birthYear == null)
    return [SELECT * FROM Student__c WHERE nationality__c == :nationality];
    }
    else if ( .... ) {
    ...
    }


    Instead of having different queries for the different scenarios (3 field filters is 8 different queries, 4 fields is 15 different, etc.) I would like my query to handle it all. I was thinking about using a contains, but SOQL doesn't have that from what I believe...










    share|improve this question



























      2












      2








      2








      I am creating a web component that allows users to filter the results of some data based on some optional filtering fields. In SOQL, how do you handle returning results when a filter is not set?



      For example, I have a Student__c object. I want users to be able to filter by the the Student__c's Field A (String), Field B (Integer) and Field C (String).



      I have this function:



      function getStudents(nationality, school, birthYear) {
      return [SELECT * FROM Student__c WHERE ....];
      }


      I call this function to load some data to display in a graph to a user. The user doesn't have to filter the students by nationality, school, and birthYear, so by default, these values will be null, and should return the results of all students. But if the user opts to filter by birthYear, then it should filter by birthYear. If they want to filter by birthYear and school, then it should do so.



      I'm trying to avoid having to do something like:



      function getStudents(nationality, school, birthYear) {
      if (nationality == null && school == null, && birthYear == null) {
      return [SELECT * FROM Student__c];
      } else if (nationality != null && school == null, && birthYear == null)
      return [SELECT * FROM Student__c WHERE nationality__c == :nationality];
      }
      else if ( .... ) {
      ...
      }


      Instead of having different queries for the different scenarios (3 field filters is 8 different queries, 4 fields is 15 different, etc.) I would like my query to handle it all. I was thinking about using a contains, but SOQL doesn't have that from what I believe...










      share|improve this question
















      I am creating a web component that allows users to filter the results of some data based on some optional filtering fields. In SOQL, how do you handle returning results when a filter is not set?



      For example, I have a Student__c object. I want users to be able to filter by the the Student__c's Field A (String), Field B (Integer) and Field C (String).



      I have this function:



      function getStudents(nationality, school, birthYear) {
      return [SELECT * FROM Student__c WHERE ....];
      }


      I call this function to load some data to display in a graph to a user. The user doesn't have to filter the students by nationality, school, and birthYear, so by default, these values will be null, and should return the results of all students. But if the user opts to filter by birthYear, then it should filter by birthYear. If they want to filter by birthYear and school, then it should do so.



      I'm trying to avoid having to do something like:



      function getStudents(nationality, school, birthYear) {
      if (nationality == null && school == null, && birthYear == null) {
      return [SELECT * FROM Student__c];
      } else if (nationality != null && school == null, && birthYear == null)
      return [SELECT * FROM Student__c WHERE nationality__c == :nationality];
      }
      else if ( .... ) {
      ...
      }


      Instead of having different queries for the different scenarios (3 field filters is 8 different queries, 4 fields is 15 different, etc.) I would like my query to handle it all. I was thinking about using a contains, but SOQL doesn't have that from what I believe...







      soql filters lightning-web-components






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 4 hours ago









      David Reed

      36.8k82255




      36.8k82255










      asked 4 hours ago









      BlondeSwanBlondeSwan

      1567




      1567






















          1 Answer
          1






          active

          oldest

          votes


















          3














          This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.



          Start with a query template string, with placeholders for your dynamic entries:



          String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';


          We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.



          Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:



          List<String> whereClauses = new List<String>();

          if (nationality != null) {
          whereClauses.add('Nationality__c = :nationality');
          }
          if (school != null) {
          whereClauses.add('School__c = :school');
          }
          if (birthYear != null) {
          whereClauses.add('Birth_Year__c = :birthYear');
          }


          Then, once the list is complete, we can construct a final query and issue it dynamically:



          return Database.query(
          String.format(
          queryTemplate,
          new List<String> {
          whereClauses.size() > 0 ? 'WHERE' : '',
          String.join(whereClauses, ' OR ')
          }
          )
          );


          This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.



          I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.






          share|improve this answer


























          • Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

            – BlondeSwan
            3 hours ago













          • Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

            – David Reed
            3 hours ago











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "459"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f253457%2ffiltering-soql-results-with-optional-conditionals%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          3














          This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.



          Start with a query template string, with placeholders for your dynamic entries:



          String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';


          We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.



          Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:



          List<String> whereClauses = new List<String>();

          if (nationality != null) {
          whereClauses.add('Nationality__c = :nationality');
          }
          if (school != null) {
          whereClauses.add('School__c = :school');
          }
          if (birthYear != null) {
          whereClauses.add('Birth_Year__c = :birthYear');
          }


          Then, once the list is complete, we can construct a final query and issue it dynamically:



          return Database.query(
          String.format(
          queryTemplate,
          new List<String> {
          whereClauses.size() > 0 ? 'WHERE' : '',
          String.join(whereClauses, ' OR ')
          }
          )
          );


          This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.



          I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.






          share|improve this answer


























          • Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

            – BlondeSwan
            3 hours ago













          • Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

            – David Reed
            3 hours ago
















          3














          This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.



          Start with a query template string, with placeholders for your dynamic entries:



          String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';


          We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.



          Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:



          List<String> whereClauses = new List<String>();

          if (nationality != null) {
          whereClauses.add('Nationality__c = :nationality');
          }
          if (school != null) {
          whereClauses.add('School__c = :school');
          }
          if (birthYear != null) {
          whereClauses.add('Birth_Year__c = :birthYear');
          }


          Then, once the list is complete, we can construct a final query and issue it dynamically:



          return Database.query(
          String.format(
          queryTemplate,
          new List<String> {
          whereClauses.size() > 0 ? 'WHERE' : '',
          String.join(whereClauses, ' OR ')
          }
          )
          );


          This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.



          I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.






          share|improve this answer


























          • Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

            – BlondeSwan
            3 hours ago













          • Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

            – David Reed
            3 hours ago














          3












          3








          3







          This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.



          Start with a query template string, with placeholders for your dynamic entries:



          String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';


          We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.



          Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:



          List<String> whereClauses = new List<String>();

          if (nationality != null) {
          whereClauses.add('Nationality__c = :nationality');
          }
          if (school != null) {
          whereClauses.add('School__c = :school');
          }
          if (birthYear != null) {
          whereClauses.add('Birth_Year__c = :birthYear');
          }


          Then, once the list is complete, we can construct a final query and issue it dynamically:



          return Database.query(
          String.format(
          queryTemplate,
          new List<String> {
          whereClauses.size() > 0 ? 'WHERE' : '',
          String.join(whereClauses, ' OR ')
          }
          )
          );


          This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.



          I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.






          share|improve this answer















          This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.



          Start with a query template string, with placeholders for your dynamic entries:



          String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';


          We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.



          Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:



          List<String> whereClauses = new List<String>();

          if (nationality != null) {
          whereClauses.add('Nationality__c = :nationality');
          }
          if (school != null) {
          whereClauses.add('School__c = :school');
          }
          if (birthYear != null) {
          whereClauses.add('Birth_Year__c = :birthYear');
          }


          Then, once the list is complete, we can construct a final query and issue it dynamically:



          return Database.query(
          String.format(
          queryTemplate,
          new List<String> {
          whereClauses.size() > 0 ? 'WHERE' : '',
          String.join(whereClauses, ' OR ')
          }
          )
          );


          This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.



          I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 3 hours ago

























          answered 4 hours ago









          David ReedDavid Reed

          36.8k82255




          36.8k82255













          • Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

            – BlondeSwan
            3 hours ago













          • Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

            – David Reed
            3 hours ago



















          • Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

            – BlondeSwan
            3 hours ago













          • Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

            – David Reed
            3 hours ago

















          Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

          – BlondeSwan
          3 hours ago







          Awesome! So if everything is null, the WHERE clause won't create an issue? Because if everything was null, wouldn't the query string end up being 'SELECT ... FROM Student__c WHERE ';

          – BlondeSwan
          3 hours ago















          Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

          – David Reed
          3 hours ago





          Ah, that's a good point, @BlondeSwan. Let me slightly amend my answer to cover the case where no filters are provided.

          – David Reed
          3 hours ago


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Salesforce Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f253457%2ffiltering-soql-results-with-optional-conditionals%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          VNC viewer RFB protocol error: bad desktop size 0x0I Cannot Type the Key 'd' (lowercase) in VNC Viewer...

          Couldn't open a raw socket. Error: Permission denied (13) (nmap)Is it possible to run networking commands...

          Why not use the yoke to control yaw, as well as pitch and roll? Announcing the arrival of...