1

I am trying to build a query, that will return books with all information including genres of that book. I have 3 objects: Book__c in which I have all information about books, Genre__c in which I have all genres, BookGenreAssociation__c in which I link book to genre.

My apex query right now looks like this:

String query = 'SELECT Id, Name, Picture_Url__c, Author__r.Name, Author__r.Photo_Url__c' +
    ', (SELECT Genre__r.Name, Genre__r.Id FROM BookGenreAssociations__r)' + 
    ' FROM Book__c';
if(fieldName != null && search != null && fieldName != '' && search != '') {
    query += ' WHERE ' + fieldName + ' LIKE \'%' + search + '%\'';
}
if(max != null) {
    query += ' LIMIT ' + max;
}
if(page != null) {
    Integer offset = max * page;
    query += ' OFFSET ' + offset;
}
return Database.query(query);

I have here pagination, search by author or title and I want to add filtering by multiple genres (for example user wants to find books in Action and Adventure genres.

I tried doing something like this in query editor:

SELECT Name, (
    SELECT Genre__r.Name, Genre__r.Id
    FROM BookGenreAssociations__r
) 
FROM Book__c 
WHERE BookGenreAssociations__r IN ('Action', 'Adventure')

but BookGenreAssociations__r isn't recognized and even if it was, subquery returns multiple results, so if I'm not mistaken, I can't use IN operator.

I tried also with Count function, but it can't be used in where clause.

I don't want to edit subquery, because I need all genres associated with book in order to print them in LWC. I don't want to filter records in apex, because pagination will be broken. I don't want to query BookGenreAssociation__c first, because there is possibility that not all books will be associated with genres. Is it possible?

1 Answer 1

0

The thing you're looking for here is a semi-join (It's the second section in the comparison operators documentation). It's very similar to the parent-child query in the SELECT clause, just located in the WHERE clause (and it must follow some other rules. If I recall, the SELECT in a semi-join can only return an Id).

The query you'd be looking for is then something like

[
  SELECT Name, (
    SELECT Genre__r.Name, Genre__r.Id
    FROM BookGenreAssociations__r
  ) 
  FROM Book__c 
  WHERE Id IN (
    SELECT Book__c 
    FROM BookGenreAssociations__c 
    WHERE Genre__r.Name IN ('Action', 'Adventure')
  )
]
1
  • Thank you very much! I only changed in semi-join BookGenreAssociations__c to BookGenreAssociation__c and it worked. Thanks! Jul 30, 2021 at 19:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .