First we have:
waitingForCat := session
readManyOf: Person
where:
[:each |
each booksOnOrder anySatisfyJoin: [:book | book title like: 'Cat%']].
This generates the following SQL query on the database.
SELECT DISTINCT t1.id, t1.first_name, t1.last_name
FROM
((PEOPLE t1 INNER JOIN BOOKS_ON_ORDER t3 ON (t3.customer_id = t1.id))
INNER JOIN BOOKS t2 ON (t3.BOOK_ID = t2.id))
WHERE (t2.title LIKE 'Cat%')
A second way is to use:
waitingForCode := session readManyOf: Person
where:
[:each |
each booksOnOrder anySatisfyExists: [:book | book title like: 'Code%']].
Or equivalently
waitingForCode := session readManyOf: Person
where:
[:each |
each booksOnOrder anySatisfy: [:book | book title like: 'Code%']].
This results in the following SQL:
SELECT t1.id, t1.first_name, t1.last_name
FROM PEOPLE t1
WHERE EXISTS
(SELECT t2.id
FROM BOOKS_ON_ORDER s1t1, BOOKS t2
WHERE
((t2.title LIKE 'Code%') AND
((s1t1.BOOK_ID = t2.id) AND
(s1t1.customer_id = t1.id))))
You can decide which is better for your situation.