function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Janno RipJanno Rip 

Use field from custom metadata type as parameter in SOQL Query

Hello developers,

I have created a custom metadata type called 'Geolocation__mdt' with 2 custom fields: Distance__c and Limit__c

What I am (unsuccessfully) trying to achieve is to use the values stored in Distance__c and Limit__c as parameters for my SOQL query: 
String queryString =
           'SELECT Id, Name,AD_JobAds_Anz__c,Vollst_ndiger_Name__c,ShippingStreet,LocateCity__longitude__s, LocateCity__latitude__s  ' +
           'FROM Account ' +
           'WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') <  3 and Kundenstatus_Direktvertrieb__c = \'Bestandskunde\' and Id != :theaccId ' +
           'ORDER BY DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') ' +
'LIMIT 50';
But instead of:  
WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') <  3
I want to use: 
WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') <  Geolocation__mdt.Distance__c
and instead of : 
'LIMIT 50';
I want to use  
'LIMIT = Geolocation__mdt.Limit__c';

The main goal is to stay flexible since the values may change over the course of time and I don't want to touch my code every time.
 

Thanks!


 

Best Answer chosen by Janno Rip
Manish UManish U
Hello!

Don't think you can directly use custom metadata types values in WHERE clause. Instead you can first query on Geolocation__mdt separately and then read parameter values into variables to be injected in the WHERE clause of main query string.

Just for hint, below. 
Geolocation__mdt deoMdt =  [SELECT MasterLabel, QualifiedApiName, Distance__c, Limit__c FROM Geolocation__mdt LIMIT 1]; 
Integer distanceVal = (Integer) deoMdt.Distance__c;   
Integer limitVal = (Integer) deoMdt.Limit__c;   

SObject [] objs = Database.query('SELECT <fields> FROM <SObject> WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') < :distanceVal LIMIT :limitVal');

Make sure:
1. while injecting parameter values into variables to be injected in the WHERE clause, use ":" colon before it
2. place any WHERE clause in custom metadata type query as appropriate
3. Apex has visibility to custom metadata type

Hope this helps!

Manish

All Answers

Manish UManish U
Hello!

Don't think you can directly use custom metadata types values in WHERE clause. Instead you can first query on Geolocation__mdt separately and then read parameter values into variables to be injected in the WHERE clause of main query string.

Just for hint, below. 
Geolocation__mdt deoMdt =  [SELECT MasterLabel, QualifiedApiName, Distance__c, Limit__c FROM Geolocation__mdt LIMIT 1]; 
Integer distanceVal = (Integer) deoMdt.Distance__c;   
Integer limitVal = (Integer) deoMdt.Limit__c;   

SObject [] objs = Database.query('SELECT <fields> FROM <SObject> WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') < :distanceVal LIMIT :limitVal');

Make sure:
1. while injecting parameter values into variables to be injected in the WHERE clause, use ":" colon before it
2. place any WHERE clause in custom metadata type query as appropriate
3. Apex has visibility to custom metadata type

Hope this helps!

Manish
This was selected as the best answer
Janno RipJanno Rip
Hello Manish,

thanks for your reply. That really helped. Here is my final code:
  
Geolocation__mdt geolocation = [SELECT Distance__c, Limit__c FROM Geolocation__mdt];
         Decimal maxDistance = geolocation.Distance__c;
         Integer recordLimit = integer.valueOf(geolocation.Limit__c);


String queryString =
           'SELECT Id, Name,AD_JobAds_Anz__c,Vollst_ndiger_Name__c,ShippingStreet,LocateCity__longitude__s, LocateCity__latitude__s  ' +
           'FROM Account ' +
           'WHERE DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') <  :maxDistance and Kundenstatus_Direktvertrieb__c = \'Bestandskunde\' and Id != :theaccId ' +
           'ORDER BY DISTANCE(LocateCity__c, GEOLOCATION('+lat+','+lon+'), \'km\') ' + 
           'LIMIT :recordLimit';

 
kalyan varma 46kalyan varma 46
@manish

I have a similar type of conditions

custom Fileter__mdt  cusMdt =  [SELECT MasterLabel, type__c, stage__c  FROM Fileter__mdt ];
 
But the type and stage is picklist in my object. I have two condiotins for stage like where ((stage='test 1' or stage ='test 2' )And (type='type1' or type ='type2').

can you please help me in this case

Thanks
kalyan