0

So I have a couple aura/lightning components that are displaying a customer order header (the parent record) along with it's order lines (the child records). Here's a pic:

enter image description here

I have two methods that populate the records for the orders and order lines respectively. Code below. Currently, when I filter on the order lines using the Item/Product Name filter, the non-matching lines disappear, but the header still shows up. How do I also filter the header when there are no lines to display?

    public static List<Order> getOrderRecords(String effectiveAccountId, Integer pageSize, Integer recordOffset, String orderStatus, Date startDate, Date endDate, String ponumber, String sortOption) {
        System.debug('getOrderRecords enter effectiveAccountId:' + effectiveAccountId);        
        System.debug('getOrderRecords enter pageSize:' + pageSize);        
        System.debug('getOrderRecords enter recordOffset:' + recordOffset);
        System.debug('getOrderRecords enter orderStatus:' + orderStatus);
        System.debug('getOrderRecords enter startDate:' + startDate);
        System.debug('getOrderRecords enter endDate:' + endDate); 
        System.debug('getOrderRecords enter ponumber:' + ponumber);           
        System.debug('getOrderRecords enter sortOption:' + sortOption);           

        String query = 'SELECT Id FROM Order WHERE AccountId = :effectiveAccountId';
        query += buildorderwherestatement(orderStatus, startDate, endDate, ponumber);
        query += buildOrderByStatement(sortOption);
        query += ' LIMIT :pageSize OFFSET :recordOffset';        
        System.debug('query:' + query);
        if(String.isNotBlank(ponumber)){
            ponumber = '%' + ponumber + '%';
        }
        List<Order> orders =  (List<Order>) Database.query(query);
        System.debug('getDetails exit orders:' + orders);
        return orders;
    }

    @AuraEnabled
    public static List<OrderItem> getOrderProductRecords(String orderId, String fieldSetName, String productName, String productCategory){  
        System.debug('getOrderProductRecords enter');
        System.debug('getOrderProductRecords orderId: ' + orderId);
        System.debug('getOrderProductRecords fieldSetName: ' + fieldSetName);
        System.debug('getOrderProductRecords productName: ' + productName);
        System.debug('getOrderProductRecords productCategory: ' + productCategory);
        FieldSetWrapper orderItemFSW = getOrderItemFields(fieldSetName);
        Set<String> orderProductQueryFields = new Set<String>{'Id'};
        
        for (FieldWrapper field : orderItemFSW.fieldList) {
            orderProductQueryFields.add(field.fieldName);
        }
        
        String orderProductQueryString = 'SELECT ' + orderProductQueryFields.toString().remove('{').remove('}') + ' FROM OrderItem WHERE OrderId = :orderId';   
        orderProductQueryString += buildorderproductwherestatement(productName, productCategory);
        System.debug('query:' + orderProductQueryString);
        if(string.isNotBlank(productName)){
            productName = '%' + productName + '%';
        }     
        List<OrderItem> records = Database.query(orderProductQueryString);
        System.debug('getOrderProductRecords exit');
        System.debug('getOrderProductRecords records: ' + records);
        return records;
    }

    private static String buildorderwherestatement(String orderStatus, Date startDate, Date endDate, String ponumber){
        System.debug('buildorderwherestatement Enter');
        System.debug('orderStatus:' + orderStatus);
        System.debug('startDate:' + startDate);
        System.debug('endDate:' + endDate);
        System.debug('ponumber:' + ponumber);
        String whereQuery = '';
        if(startDate != null){
            whereQuery += ' AND EffectiveDate >= :startDate';
        }
        if (endDate != null){
            whereQuery += ' AND EffectiveDate <= :endDate';
        }
        if (String.isNotBlank(orderStatus)){
            whereQuery += ' AND Status = :orderStatus';
        }
        if (String.isNotBlank(ponumber)){
            whereQuery += ' AND PoNumber LIKE :ponumber';
        }
        return whereQuery;
    }

    private static String buildorderproductwherestatement(String productName, String productCategory){
        System.debug('buildorderproductwherestatement Enter');
        System.debug('productName:' + productName);
        System.debug('productCategory:' + productCategory);
        String whereQuery = '';
        if (String.isNotBlank(productName)){
            whereQuery += ' AND product2.name LIKE :productName';
        }
        if (String.isNotBlank(productCategory)){
            whereQuery += ' AND product2.ECatalog_Category__c LIKE :productCategory';
        }
        return whereQuery;
    }```
2
  • Generally, to filter out records without children use semi-joins in queries Jun 21 at 18:48
  • @OlehBerehovskyi So something like below? My only concern with that working is the orderId I'm referencing in the subquery hasn't been populated yet since that's what the main query is doing. Will this still work? The last part in asterisks is what I'm concerned about, how do I reference the Id from the order in the subquery? SELECT Id FROM Order WHERE AccountId = :effectiveAccountId AND Id IN ( SELECT OrderId FROM OrderItem WHERE OrderId = **Order.Id**)
    – Ryan Neel
    Jun 21 at 18:50

0

Your Answer

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

Browse other questions tagged or ask your own question.