3

I have a structure question today regarding a dynamic SOQL query that I seem to be entering the wrong custom object id value for in the WHERE clause.

I have retrieved the custom object id from my SelectOption list object on a button submission. I then want to add this in to my SOQL query to filter further my search.

  • My list of SelectOption instances are formed of id and name values respectively for value and label formed by retrieving and looping through a custom object named Project.
  • Project__c is a lookup field.

Dynamic SOQL Apex function:

public String selectedProjectId { get; set; }

public String returnDynamicSOQL(string customObjectName)
{
    string dynamicSOQL = 'SELECT Id, Name, ' + customObjectName + '_Name__c, Date_Registered__c, Status__c, Originator__c, Owner__c, ' + customObjectName + '_Type__c, Project__c FROM ' + customObjectName + '__c ';
    if (projectIsSelected)
    {
        dynamicSOQL += ('WHERE Project__c.Id = ' + selectedProjectId);
    }

    return dynamicSOQL;
}

I am receiving this error:

Visualforce Error

System.QueryException: expecting a colon, found 'a4S24000000aPDfEAM' Class.RAIDController.getRAIDObjects: line 59, column 1 Class.RAIDController.getRisks: line 71, column 1

So I have the id for the project custom object selected at least, am I wrong in thinking this is what I need to retrieve the exact project by id? Does the WHERE clause need me to specify Project__c.Id or can I just use Project__c?

Any help much appreciated!

4

This sort of code can be open to SOQL Injection attack. Using the bind syntax avoids this in a clean way so prefer that:

 dynamicSOQL += ' WHERE Project__c.Id = :selectedProjectId';

Always better to be as specific as possible on data types (and in this case would also be defensive) so I suggest you also make this change:

public Id selectedProjectId { get; set; }

PS

Based on Matt's comment, the expression should really be:

dynamicSOQL += ' WHERE Project__c = :selectedProjectId';

(as Project__c is the ID of the Project object as it is a lookup field) or could also be:

dynamicSOQL += ' WHERE Project__r.Id = :selectedProjectId';

(as Project__r is a reference to the Project object that itself has an ID). The first pattern is what is normally used.

  • Hi Keith, thanks for getting back to me. Brilliant, this clears the colon error and +1 for security considerations. However I was hoping you could clear up the error I'm getting back and it is related to my initial question: "System.QueryException: Didn't understand relationship 'Project__c' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name." The Project__c field is a lookup field to a different custom object in my org. – MattGarnett Feb 9 '16 at 13:26
  • @MattGarnett See PS. – Keith C Feb 9 '16 at 13:50
  • Hi Keith, you're completely correct and I discovered this (fortunately, for my learnings sake) a few minutes ago! Thanks so much though wouldn't of got there without your help. – MattGarnett Feb 9 '16 at 13:55
1

try this

dynamicSOQL += (' WHERE Project__r.Id = \'' + selectedProjectId+'\'');

or

dynamicSOQL += (' WHERE Project__c = \'' + selectedProjectId+'\'');
0

Change following line

dynamicSOQL += ('WHERE Project__c.Id = ' + selectedProjectId);

into

dynamicSOQL += (' WHERE Project__c.Id = \'' + selectedProjectId+'\'');

or

 dynamicSOQL += (' WHERE Project__c.Id =: selectedProjectId');

Also Project__c.Id either should be Projects__r or keep it only Project__c

  • I haven't actually got a custom object indicated as Project__r, it's a lookup out to an object so is stated as Project__c I believe? Regardless I get an error when I try to access Project__r. – MattGarnett Feb 9 '16 at 13:34
  • @MattGarnett Lookup field can have same API name as object api name. Please go to lookup field definition and verify the relationship name. Default relationship name would be ProjectS and in SOQL we use it as Projects__r – Ashwani Feb 9 '16 at 13:37

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.