1

Newbie to Salesforce. I have an object ExpenseItem which master detail relationship with another object Expense where Expense is the master.

I am trying to list expense items for a particular expense.

EDITED Code in the apex class

try {
        String parentId = 'xxx';
        String sQuery =  'select id, Name from expenseItem__c WHERE expenseItem__c.expense__c.Id = :' + parentId + 'order by createdDate DESC';

        List < sObject > lstOfRecords = Database.query(sQuery);

        for (sObject obj: lstOfRecords) {
            returnList.add(obj);
        }
    }
    catch (Exception e) {
        // "Convert" the exception into an AuraHandledException
        throw new AuraHandledException('Error: '
            + e.getMessage());    
    }
    finally {
        // Something executed whether there was an error or not
    }

The above code gives me variable does not exist error. Anything I am missing here? Can someone provide a simple example of how to get a child record filtered by the master id?

  • 1
    Where are you executing the Query in Developer Console OR in apex class? If using in Apex class please share the code that you are using so that we can provide the best possible solution – Amit Singh Aug 2 '18 at 11:09
  • Using it in a Apex Class that I created in the developer console – user275157 Aug 2 '18 at 11:15
  • Please update the completed code Here or the part of the Code so that we can help – Amit Singh Aug 2 '18 at 11:16
  • Added the code. Just a simple example of filtering by master ID would be very helpful. – user275157 Aug 2 '18 at 11:18
  • When you're running into the error message, it really does help to include the full text of the error message (including the stack trace) verbatim. Otherwise, people who are answering your question need to play a guessing game to try to figure out exactly what your error is. This leads to the situation you have here: multiple answers that don't quite solve your issue. – Derek F Aug 2 '18 at 13:02
3

If you are passing hardcoded id ( Which you should not) then your query will look like

[select id, Description from expenseItem__c where expense__c = 'parentExpenseId' order by createdDate DESC];

While if you have Id in some variable then your query will look like

Id parentRecordId = 'SomeValueHere';
[select id, Description from expenseItem__c where expense__c =: parentRecordId  order by createdDate DESC];
  • Tried with the =: Edited the code above to reflect the same. it now says - variable does not exist: expenseID – user275157 Aug 2 '18 at 11:24
  • @user275157 can you try this query 'select id, Name from expenseItem__c WHERE expenseItem__c.expense__c.Id = : parentId order by createdDate DESC'; – Tushar Sharma Aug 2 '18 at 11:36
3

If you are doing a SOQL in Apex and want to use a variable in your query, you'll need to use a colon to reference it. You've used quotes which means you're literally looking for an Id of "parentExpenseId" instead of the value of the parentExpenseId variable.

Try this:

List<ExpenseItem__c> expenseItems = [SELECT Id, Description FROM ExpenseItem__c WHERE Expense__c = :parentExpenseId ORDER BY CreatedDate DESC];

EDIT: I've just seen your code sample. When you use Database.query() you can't specify variables so you have to add them to your query string instead. In this case, you can use quotes. To put a quote in a string, you'll need to use \' so that the compiler doesn't think you're closing the string. As others have said, this is vulnerable to SOQL injection so should be avoided.

String sQuery =  'SELECT Id, Name from ExpenseItem__c WHERE Expense__c.Id = \'' + parentId + '\' ORDER BY CreatedDate DESC';
  • Tried - String sQuery = 'select id, Name from expenseItem__c WHERE expenseItem__c.expense__c.Id = :' + parentId + 'order by createdDate DESC'; It now says - variable does not exist - with the ID of the parent expense – user275157 Aug 2 '18 at 11:21
  • As Tushar said in his answer, you should consider switching to the first syntax I suggested. In general, static SOQLs (where you specify the query in square brackets and it compiles) are easier to work with than dynamic SOQLs (where you form a string and then query using that string). – fred Aug 2 '18 at 11:30
  • You can bind apex variables in dynamic queries, but there are more restrictions than when you try to do the same thing in a static query. Database.query('SELECT Id FROM ExpenseItem WHERE Expence__c = :parentId'); would work as expected. See the "Dynamic SOQL Considerations" section of developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – Derek F Aug 2 '18 at 12:46
2

Edit as noted on a comment below. Using database.query doesn't protect from injections and on top of that, it is significantly slower. In the other hand it gives flexibility in the sense that it allows you to craft your request dynamically. I use both depending of use cases, in sensible ones you should do the injection prevention yourself.

Above answer is correct. (don't have reputation to post on the subcomments). If you have issues debugging try launching the String value of your variable in the query editor. Every SOQL string value must be wrapped in quotes, and obviously those have to be escaped in apex. You don't have to put the ":" because the value is processed during query writting, no SOQL association. To make debugging easier try debugging the query before executing it so you can directly copy paste it in the query editor of the developper console.

String parentId = 'xxx';
String sQuery =  'select id, Name from expenseItem__c WHERE 
expenseItem__c.expense__c.Id = \'' + parentId + '\' order by createdDate DESC';
System.debug(sQuery); // if it bugs copy past the sQuery value from the log 
                      // in the Query editor.
List<expenseItem__c> ls = Database.query(sQuery);
  • As a note for others, be very cautious in using this code. The expense__c = \'' + someVariable + '\' pattern is very vulnerable to SOQL injection and will not pass a security review (for ISVs). Variable binding (like in Tushar's question) is the safest way to do this. – Derek F Aug 2 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.