Use field from custom metadata type as parameter in SOQL Query

Question

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!

Answer 1

Use some bindings:

Geolocation__mdt geolocation = [SELECT Distance__c, Limit__c FROM Geolocation__mdt WHERE ...];
Decimal maxDistance = geolocation.Distance__c;
Integer recordLimit = (Integer) 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';

Answer 2

Phil W. solution I think is the best approach. Just to give you some context, SOQL allows you to navigate between objects -as you wanted in your example- from the Query itself only when you have lookup (master-child) fields that connect them.

That's why Phil suggested making the first query to obtain the geolocation first. Only a gentle reminder that you watch your SOQL apex governor limitations and avoid putting queries in a for loop.

If this query is to be applied to several records at the time, maybe you need to rebuild the process from a bulk perspective.

Some documentation:

Using Variables in SOQL

Relationship Queries in SOQL and SOSL