SOQL Query failing

Question

I am developing an app to work with custom sObjects.

In there I have a list of items, and the items are ordered via a hidden number Order__c.

I also have buttons with these fields, to move them up or down. Basically, it swaps the field with the one below/above it (depending on if you click down or up).

I have two custom sObjects:

• Master object
• Child object

The child object has a master-child relation lookup to its master-object ID. Therefore, an order is unique only in combination with its master-ID.

These are the steps to take:

• on button click, send an event
• in the list component, handle the event
• in the list controller function that handles the event, grab the item from the event, and create an action with a function in an apex controller (referenced in the list component)
• in the apex controller: execute a query to grab the below/above item
• set the order of both fields to the other one
• update the list of both fields (so that it's one DML call instead of two)

The problem is: it's failing at the select query (line where first [SELECT ... is).

13:52:24:004 FATAL_ERROR System.QueryException: List has no rows for assignment to SObject

See the code below.

I am certain it's supposed to work: the up button on the top item is disabled, as is the down button on the bottom item. I have used the workbench and tested the query (altough with literal strings and numbers instead of inserted variables) and then it works absolutely fine.

I've tried

• AND Order__c = :item.Order__c - 1
• AND Order__c = (:item.Order__c - 1)
• AND Order__c = :(item.Order__c - 1)
• AND Order__c = :order

The last one being what is displayed below. I've also tried with and without LIMIT 1 (as I am certain there can only be one item with the given parent and order), in the workbench it works fine, below it doesn't work at all.

 @AuraEnabled
public static List<childItem__c> moveField(childItem__c item, String direction) {
    childItem__c other;
    Decimal order;
    if(direction == 'up') {
        order = item.Order__c - 1;
        other = [SELECT Id, Order__c
                 FROM childItem__c 
                 WHERE Parent__c = :item.Parent__c
                 AND Order__c = :order
                 LIMIT 1
                ];    
    } else if (direction == 'down') {
        order = item.Order__c + 1;
        other = [SELECT Id, Order__c 
                 FROM childItem__c 
                 WHERE Parent__c = :item.Parent__c
                 AND Order__c = :order
                 LIMIT 1
                ];    

    }        

    if(direction == 'up') {
        item.Order__c = item.Order__c - 1;
        other.Order__c = other.Order__c + 1;
    } else if (direction == 'down') {
        item.Order__c = item.Order__c + 1;
        other.Order__c = other.Order__c - 1;            
    }
    List<childItem__c> updateList = new List<childItem__c>{item, other};

        try {
            update updateList;
        } catch(DmlException e) {
            System.debug('An unexpected error has occurred: ' + e.getMessage());
        }

    return getItems(item.Parent__c);
}

If at all possible, I would like to use as few objects as possible, i.e. to remove the current Decimal order variable. It should however be understandable, so that's why I did include the 'other' sObject.

Answer 1

According to your query it is expecting List<childItem__c>, but you are directly trying to assign to Decimal other. Which is causing the issue.

         [SELECT Id, Order__c
             FROM childItem__c 
             WHERE Parent__c = :item.Parent__c
             AND Order__c = :order
             LIMIT 1
            ];

You can change the below line to this:

 List<childItem__c> lstItem = [SELECT Id, Order__c
             FROM childItem__c 
             WHERE Parent__c = :item.Parent__c
             AND Order__c = :order
             LIMIT 1
            ]; 
if(lstItem.size()>0)
 other = lstItem[0].Order__c;