Hi Cayenne community,
I'm trying to get a large set of objects using prefetching.
The following entities are involved:
Mailing
has one RecipientSet
has many RecipientField(s)
has many Recipient(s)
has many RecipientValue(s) (one for each field)
I intend to get the whole object structure with a single query on the
RecipientValue entity with prefetches for Recipient and RecipientField.
The query generated by Cayenne looks like this:
SELECT t0.field, t0.recipient_set, t0.value, t0.recipient,
t1.position, t1.recipient_set,
t2.name, t2.position, t2.address_field, t2.recipient_set
FROM public.recipient_value t0
LEFT JOIN public.recipient t1
ON (t0.recipient = t1.position
AND t0.recipient_set = t1.recipient_set)
LEFT JOIN public.recipient_field t2 ON (t0.field = t2.position
AND t0.recipient_set = t2.recipient_set)
JOIN public.recipient_set t3
ON (t0.recipient_set = t3.id)
JOIN public.recipient t4
ON (t0.recipient = t4.position
AND t0.recipient_set = t4.recipient_set)
WHERE t3.mailing = ? ORDER BY t4.position
[bind: 1->mailing:880]
To me this looks like everything should be available after this query.
Nevertheless, Cayenne issues additional queries when I access the
RecipientField and Recipient properties:
SELECT DISTINCT t0.position, t0.recipient_set
FROM public.recipient t0
JOIN public.recipient_value t1
ON (t0.position = t1.recipient
AND t0.recipient_set = t1.recipient_set)
WHERE t1.recipient_set = ?
AND t1.field = ?
AND t1.recipient = ?
[bind: 1->recipient_set:980, 2->field:8, 3->recipient:0]
Here's the code that builds the query:
final String path = RecipientValue.RECIPIENT_SET_PROPERTY + "."
+ RecipientSet.MAILING_PROPERTY + "." + Mailing.ID_PROPERTY;
final SelectQuery query = new SelectQuery(RecipientValue.class,
ExpressionFactory.matchExp(path, getId()));
query.addPrefetch(RecipientValue.RECIPIENT_PROPERTY).setSemantics(
PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
query.addPrefetch(RecipientValue.FIELD_PROPERTY).setSemantics(
PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
query.addOrdering(RecipientValue.RECIPIENT_PROPERTY + "." +
Recipient.POSITION_PROPERTY, true);
final List<RecipientValue> values = getObjectContext().performQuery(query);
What could I be doing wrong?
Thanks for any hints!
-- Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
