Dynamic SOQL error

Question

I am getting the following error while running my dynamic query.But when i make it normal query it works.Error is : Didn't understand relationship 'gii__Product__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name.

My Dynamic soql query (which gives works )

soql =  'select id, name,Warehouse__c, Product_Inventory__c , Product_Reference__c,Product_Inventory__r.gii__Product__r.name,Product_Inventory__r.gii__Product__r.gii__ProductStyle__r.name,Product_Reference__r.gii__ProductCode__c, Sales_Year__c,(select id,name,Quantity__c,Month__c from Forecast_Details__R)'+' from forecast__c where Sales_Year__c =' + '\''+selectSalesYear.id +'\''+ 'and Warehouse__c =\'' + selectedWarehouse.id + '\' ' + filterQueryPart  + 
                ' Order By gii__Product__r.name NULLS LAST limit ' + PageSize + ' offset ' + PageNumber*PageSize; 

Query which works

select id, name,Warehouse__c, Product_Inventory__c , Product_Reference__c,Product_Inventory__r.gii__Product__r.name,Product_Inventory__r.gii__Product__r.gii__ProductStyle__r.name,Product_Reference__r.gii__ProductCode__c, Sales_Year__c,
                            (select id,name,Quantity__c,Month__c from Forecast_Details__R) from forecast__c where Sales_Year__c =: selectSalesYear.id and Warehouse__c =: selectedWarehouse.id];  

Can you please help why i getting an error in relation in dynamic query?

Thanks in advance Samir

Answer 1

Check the field you have used for Order BY in the query gii__Product__r.name .It should be Product_Inventory__r.gii__Product__r.name.

soql =  'select id, name,Warehouse__c, Product_Inventory__c , Product_Reference__c,Product_Inventory__r.gii__Product__r.name,Product_Inventory__r.gii__Product__r.gii__ProductStyle__r.name,Product_Reference__r.gii__ProductCode__c, Sales_Year__c,(select id,name,Quantity__c,Month__c from Forecast_Details__R)'+' from forecast__c where Sales_Year__c =' + '\''+selectSalesYear.id +'\''+ 'and Warehouse__c =\'' + selectedWarehouse.id + '\' ' + filterQueryPart  + 
            ' Order By gii__Product__r.name NULLS LAST limit ' + PageSize + ' offset ' + PageNumber*PageSize; 

soql =  'select id, name,Warehouse__c, Product_Inventory__c , Product_Reference__c,Product_Inventory__r.gii__Product__r.name,Product_Inventory__r.gii__Product__r.gii__ProductStyle__r.name,Product_Reference__r.gii__ProductCode__c, Sales_Year__c,(select id,name,Quantity__c,Month__c from Forecast_Details__R)'+' from forecast__c where Sales_Year__c =' + '\''+selectSalesYear.id +'\''+ 'and Warehouse__c =\'' + selectedWarehouse.id + '\' ' + filterQueryPart  + 
            ' Order By Product_Inventory__r.gii__Product__r.name NULLS LAST limit ' + PageSize + ' offset ' + PageNumber*PageSize; 

Answer 2

it should be like this :

Order By Product_Inventory__r.gii__Product__r.name NULLS LAST limit