+ Start a Discussion
paul-lmipaul-lmi 

issue with wildcard SOQL on a relationship query

 

string productquery = 'select id, viewhref__c, subject__c, posteddate__c from lithiummessage__c where lithiumboard__r.productcode__c = ' + '\'%' + :getproduct() + '%\'' + ' ORDER BY posteddate__c DESC LIMIT 10';
		
		List<LithiumMessage__c> lm = new List<LithiumMessage__c>();
		lm = database.query(productquery);
system.debug(logginglevel.info,'items: ' + lm);

 

So, if productcode is equal to to what I'm passing in, the query works.  if it's no equal, say 'lmiignition,lmiignitionandroid', it will not return any results.  isn't '%' supposed to wildcard any number of chars?  This usually works for me, but I haven't had the need to try it in relationship queries until now.

 

JA-DevJA-Dev

Right after your WHERE clause, change the operator from '=' to 'LIKE'.

paul-lmipaul-lmi

same result.  I'd tried that as well.

 

 

        string productquery = 'select id, viewhref__c, subject__c, posteddate__c from lithiummessage__c where lithiumboard__r.productcode__c LIKE ' + '\'%' + getProduct() + '%\'' + ' ORDER BY posteddate__c DESC LIMIT 10';

 

        string productquery = 'select id, viewhref__c, subject__c, posteddate__c from lithiummessage__c where lithiumboard__r.productcode__c LIKE ' + '\'%' + getProduct() + '%\'' + ' ORDER BY posteddate__c DESC LIMIT 10';

 

getProduct() = 'lmiignitionandroid' and lithiumboard__r.productcode__c = 'lmiignition lmiignitionandroid'

JA-DevJA-Dev

Just to make sure I understand:

 

  1. when productcode__c = 'Imiignition' and getProduct() returns 'Imiignition' your query works
  2. when productcode__c = 'lmiignition' and getProduct() returns 'lmiignitionandroid' your query doesn't return anything
Is this correct? If that is the case, then that is expected since in scenario 2, the query will only return productcode__c values that contain the string Imiignitionandroid somewhere. Similarly, if you flip the order of the search in scenario 2 (productcode__c = 'lmiignitionandroid' and getProduct() returns 'lmiignition'), then your query should find the match.
Let me know if I understood your question and I'll see how you could change your query.

 

paul-lmipaul-lmi

if productcode__c = 'lmiignition lmiignitionandroid' and getProduct() returns 'lmiignitionandroid', the query will find no results.

 

basically, i want to allow for overlap, because we have 'lmiignition', 'lmiignitionandroid', and 'lmiignitioniphone', and I want them all to return the same records, so I want productcode__c to contain all 3 value, either space or comma separated.