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.
<db-entity name="MACHS" schema="dbo" catalog="opconxps4"> <db-attribute name="CURRJOBS" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="LASTUPDATE" type="DOUBLE" isMandatory="true" length="53"/> <db-attribute name="MACHGRPID" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="MACHGWAYID" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="MACHID" type="SMALLINT" isPrimaryKey="true" isMandatory="true" length="5"/> <db-attribute name="MACHNAME" type="CHAR" isMandatory="true" length="24"/> <db-attribute name="MACHSOCKET" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="MACHTYPE" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="MAXJOBS" type="SMALLINT" isMandatory="true" length="5"/> <db-attribute name="NETSTATUS" type="CHAR" isMandatory="true" length="1"/> <db-attribute name="OPERSTATUS" type="CHAR" isMandatory="true" length="1"/> </db-entity> <db-entity name="MACHS_AUX" schema="dbo" catalog="opconxps4"> <db-attribute name="MACHID" type="SMALLINT" isPrimaryKey="true" isMandatory="true" length="5"/> <db-attribute name="MAFC" type="SMALLINT" isPrimaryKey="true" isMandatory="true" length="5"/> <db-attribute name="MASEQNO" type="SMALLINT" isPrimaryKey="true" isMandatory="true" length="5"/> <db-attribute name="MAVALUE" type="VARCHAR" isMandatory="true" length="4000"/> </db-entity> <obj-entity name="Machine" className="com.sma.core.api.machines.Machine" dbEntityName="MACHS" superClassName="com.sma.core.api.DataAccessObject"> <obj-attribute name="CurrentJobs" type="java.lang.Short" db-attribute-path="CURRJOBS"/> <obj-attribute name="LastUpdate" type="java.lang.Double" db-attribute-path="LASTUPDATE"/> <obj-attribute name="lsamTypeId" type="java.lang.Short" db-attribute-path="MACHTYPE"/> <obj-attribute name="machGatewayId" type="java.lang.Short" db-attribute-path="MACHGWAYID"/> <obj-attribute name="machGroupId" type="java.lang.Short" db-attribute-path="MACHGRPID"/> <obj-attribute name="machId" type="java.lang.Short" db-attribute-path="MACHID"/> <obj-attribute name="machName" type="java.lang.String" db-attribute-path="MACHNAME"/> <obj-attribute name="machSocket" type="java.lang.Short" db-attribute-path="MACHSOCKET"/> <obj-attribute name="maxJobs" type="java.lang.Short" db-attribute-path="MAXJOBS"/> <obj-attribute name="netStatus" type="java.lang.String" db-attribute-path="NETSTATUS"/> <obj-attribute name="operStatus" type="java.lang.String" db-attribute-path="OPERSTATUS"/> </obj-entity> <obj-entity name="MachineAux" className="com.sma.core.api.auxs.MachineAux" dbEntityName="MACHS_AUX" superClassName="com.sma.core.api.DataAccessObject"> <obj-attribute name="MaFc" type="java.lang.Short" db-attribute-path="MAFC"/> <obj-attribute name="MaSequenceNumber" type="java.lang.Short" db-attribute-path="MASEQNO"/> <obj-attribute name="MaValue" type="java.lang.String" db-attribute-path="MAVALUE"/> <obj-attribute name="MachId" type="java.lang.Short" db-attribute-path="MACHID"/> </obj-entity> <db-relationship name="toMachineAux" source="MACHS" target="MACHS_AUX" toDependentPK="true" toMany="true"> <db-attribute-pair source="MACHID" target="MACHID"/> </db-relationship> <obj-relationship name="relatedMachineAux" source="Machine" target="MachineAux" deleteRule="Cascade" db-relationship-path="toMachineAux"/>
