Get property from object in SOQL query

Question

I have symply query that get data from table Contact and Case ( lookup Contact). Result squery from Case is object with field CaseNumber and Subject(Like [{"CaseNumber":"00001000","Subject":"Starting generator after electrical failure"}].)

How I can refer to this field to filter the recording result? For example WHERE CaseNumber = '00001000' OR Subject LIKE '%generator%'

If I put this checking in (SELECT CaseNumber, Subject FROM Cases ) it only leave blank value in field Cases but don't filtred record as I need.

Answer 1

You can include filters in the inner (parent-child) subquery

e.g.

List<Contact> contactList = [SELECT 
    Name,
    (SELECT 
        CaseNumber, Subject
    FROM
        Cases
    WHERE
        CaseNumber = '00001000' OR Subject LIKE '%generator%'
    )
FROM
    Contact
]

Beyond that, subqueries become an embedded list of SObjects, and you access it with dot notation (like any other field).

for(Contact cont :contactList){
    // This only works if each parent has <= 200 children
    // Beyond that, Salesforce tries to call "queryMore" and will fail
    //List<Case> childCases = cont.Cases;

    // The safe way to get children is to have a nested loop
    List<Case> childCases = new List<Case>();

    for(Case c :cont.Cases){
        childCases.add(c);
    }
}