Filter CayenneDataObject getXXXArray() entries with parameters?

Question

My DB model is as following:

A.id (1 : n) B.ad_id

So in cayenne for object A a I can do a.getBArray() which returns me all the entries from B from this given A entry. Yet I would like to filter on this list, based on the property active = 1.

Obviously I can use Expression.fromString("active = 1") with SelectQuery, but for this approach I can't find how I associate the A instance under which I want to run this query on.

A different approach is to retrieve all entries from a.getBArray() and filter in code searching only those that have active == true, this approach is IMHO inefficient.

Recommendations are mostly appreciated.

Thank you, Maxim.

-- EDIT:

My current solution to is (object names have been replaced with a & b respectively):

long aId = DataObjectUtils.longPKForObject(db_a_instance);
String bSQL = "select * from b where active = 1 and a_id = " + aId;
SQLTemplate bQuery = new SQLTemplate(B.class, bSQL);
List<B> dbBs = context.performQuery(bQuery);

and I'm asking if there is a better, more elegent solution?

Thanks.

Answer 1

I've asked similar question on Cayenne's friendly mailing list. You can see here.

It seemed to be the preferred approach is to go via relationship and filter in Java unless the relationship returns very large data. The benefit of doing so that the full list will be in memory and next time when you use relationship, you don't need to make a trip to DB.

The answer is quoted here

Both require a trip to the DB.

1. (the traversing in relationship approach) requires a trip one time to the DB to fault the groups from the DB, but then it'll be in memory.

2. (the query with filter approach) requires a trip to the DB every time, so that could be slower in the long run even though it returns fewer matches.

If this is something that only happens once and you are REALLY concerned about performance (and possibly have a LOT of groups), I'd go with #2, otherwise #1. You can optimize #1 a bit, too, so you don't have to iterate each time to check.

via: Michael Gentry