Filtering SOQL results with optional conditionalsHow 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
When a wind turbine does not produce enough electricity how does the power company compensate for the loss?
Does a warlock using the Darkness/Devil's Sight combo still have advantage on ranged attacks against a target outside the Darkness?
Can I pump my MTB tire to max (55 psi / 380 kPa) without the tube inside bursting?
What Happens when Passenger Refuses to Fly Boeing 737 Max?
Recommendation letter by significant other if you worked with them professionally?
Is "history" a male-biased word ("his+story")?
Why was Goose renamed from Chewie for the Captain Marvel film?
Why does the negative sign arise in this thermodynamic relation?
Why does Captain Marvel assume the people on this planet know this?
They call me Inspector Morse
How to secure an aircraft at a transient parking space?
Definition of Statistic
Intuition behind counterexample of Euler's sum of powers conjecture
Does "Until when" sound natural for native speakers?
Do items de-spawn in Diablo?
weren't playing vs didn't play
Conservation of Mass and Energy
Should I tell my boss the work he did was worthless
Bash script should only kill those instances of another script's that it has launched
Latex does not go to next line
Reversed Sudoku
PTIJ: wiping amalek’s memory?
meaning and function of 幸 in "则幸分我一杯羹"
Why does liquid water form when we exhale on a mirror?
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
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
add a comment |
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
add a comment |
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
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
soql filters lightning-web-components
edited 6 hours ago
David Reed
36.8k82255
36.8k82255
asked 7 hours ago
BlondeSwanBlondeSwan
1567
1567
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
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
6 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
6 hours ago
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
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
6 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
6 hours ago
add a comment |
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.
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
6 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
6 hours ago
add a comment |
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.
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.
edited 6 hours ago
answered 6 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
6 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
6 hours ago
add a comment |
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
6 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
6 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
6 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
6 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
6 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
6 hours ago
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
-filters, lightning-web-components, soql