Hello guys,
I encountered a strange behavior with prefetch using Expression. Let's
explain the context:
I have two object entities, Machine and MachineAux, there is a relationship
between them on the Machine's primary key (MachId, see an extraction of the
cayenne mapping file in attachement for details).
When I run a SelectQuery with an Expression that only point to the
obj-entity properties, things are going well. See the sample bellow :
Step 1)
Expression exp = ExpressionFactory.matchExp(Machine.MACH_ID_PROPERTY, 3);
SelectQuery query = new SelectQuery(Machine.class, exp);
query.addPrefetch(Machine.RELATED_MACHINE_AUX_PROPERTY);
List<Machine> machines = context.performQuery(query);
Cayenne runs the following SQL Query :
SELECT t0.MACHGWAYID, t0.NETSTATUS, t0.OPERSTATUS, t0.MACHID, t0.MACHGRPID,
t0.MAXJOBS, t0.MACHTYPE, t0.CURRJOBS, t0.LASTUPDATE, t0.MACHNAME,
t0.MACHSOCKET FROM dbo.MACHS t0 WHERE t0.MACHID = ? [bind: 1->MACHID:3]
Step 2)
List<MachineAux> auxs = machines.get(0).getRelatedMachineAux();
Cayenne runs the following SQL Query :
SELECT t0.MAVALUE, t0.MAFC, t0.MACHID, t0.MASEQNO FROM dbo.MACHS_AUX t0 JOIN
dbo.MACHS t1 ON (t0.MACHID = t1.MACHID) WHERE t1.MACHID = ? [bind:
1->MACHID:3]
-> Here the list "aux" is correct, and I got all MachineAux object
reflecting the database contents.
But, when I run a SelecQuery with an Expression that point on related
properties, on the step 2 I'll only get auxs that match the given expression
from the step 1. See the sample bellow :
Step 1)
String maFc = Machine.RELATED_MACHINE_AUX_PROPERTY + "." +
MachineAux.MA_FC_PROPERTY;
String maValue = Machine.RELATED_MACHINE_AUX_PROPERTY + "." +
MachineAux.MA_VALUE_PROPERTY;
Expression exp = ExpressionFactory.matchExp(maFc, 121);
exp = exp.andExp(ExpressionFactory.matchExp(maValue, "2"));
exp = exp.andExp(ExpressionFactory.matchExp(Machine.LSAM_TYPE_ID_PROPERTY,
3));
exp = exp.andExp(ExpressionFactory.matchExp(Machine.MACH_ID_PROPERTY, 3));
SelectQuery query = new SelectQuery(Machine.class, exp);
query.addPrefetch(Machine.RELATED_MACHINE_AUX_PROPERTY);
List<Machine> machines = context.performQuery(query);
Cayenne runs the following SQL Query :
SELECT DISTINCT t0.MACHGWAYID, t0.NETSTATUS, t0.OPERSTATUS, t0.MACHID,
t0.MACHGRPID, t0.MAXJOBS, t0.MACHTYPE, t0.CURRJOBS, t0.LASTUPDATE,
t0.MACHNAME, t0.MACHSOCKET FROM dbo.MACHS t0 JOIN dbo.MACHS_AUX t1 ON
(t0.MACHID = t1.MACHID) WHERE (t1.MAFC = ?) AND (t1.MAVALUE = ?) AND
(t0.MACHTYPE = ?) AND (t0.MACHID = ?) [bind: 1->MAFC:121, 2->MAVALUE:'2',
3->MACHTYPE:3, 4->MACHID:3]
Step 2)
List<MachineAux> auxs = machines.get(0).getRelatedMachineAux();
Cayenne runs the following SQL Query :
SELECT t0.MAVALUE, t0.MAFC, t0.MACHID, t0.MASEQNO FROM dbo.MACHS_AUX t0 JOIN
dbo.MACHS t1 ON (t0.MACHID = t1.MACHID) WHERE (t0.MAFC = ?) AND (t0.MAVALUE
= ?) AND (t1.MACHTYPE = ?) AND (t1.MACHID = ?) [bind: 1->MAFC:121,
2->MAVALUE:'2', 3->MACHTYPE:3, 4->MACHID:3]
-> Here we can notice from the generated SQL Query that when we request the
related MachineAux in step 2, there is additionnal parameters than we had
with the working sample. The result as well is that, only MachineAux object
matching the Expression from the step 1 will be returned back.
When I remove the addPrefetch, my sample 2 is working fine especilly the
step 2... I don't think it's a normal behavior.
What's your thoughts ?
Cheers,
Francois Eyl
BTW : Sorry for the long mail, I had to detail my issue to be sure that you
get all necessary keys to your hands.
This archive was generated by hypermail 2.0.0 : Fri Apr 17 2009 - 12:21:28 EDT