Null entity while exploring many-to-many relationships

[Anthonin Lizé]

Hi all,

I have this 'issue' since a long time, but never took the time to ask about it since I was able to work around it.

Whenever I have a many-to-many table (Oracle), if I add this table in the include of a request, it displays null for the entity content (shows that there is a record, but it's null).

It happens when the M-to-N has a compound PK. To work around that, I simply add a auto-generated PK (eg 'ID') to the table, and everything works fine.

I have the M-to-N table in my model because it contains other fields describing the relationship.

Example:

Table 'A'

Table 'B'

Table M-N 'AB' (A_ID, B_ID, field1)

if I include, querying A, include=["aB.B"]

I will see in the result something like "aB:[{"B": null, "B": null, ...}]"

but if 'AB' contains its own PK 'ID', that will work fine and I'll see the content of B. Which I've been doing so far...

Am I doing something wrong? Missing a configuration thing in Cayenne? Specific to Oracle?

Thank you,

anthonin

[Andrus Adamchik]

Hi Anthonin, 

Yeah, that's weird. Similar semantics works fine in my tests and in my apps. E.g.:

https://github.com/agrestio/agrest/commit/393ca76a33267431878eebe713216988dc29d378

Maybe you can post a piece of the Cayenne DataMap XML for these 3 entities? Maybe there's something special there.

Andrus

--
You received this message because you are subscribed to the Google Groups "Agrest Framework User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to agrest-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/agrest-user/669601bb-bbe9-4706-a51c-057afaaf0ce4n%40googlegroups.com.

[Anthonin Lizé]

Hi Andrus,

Thanks for replying.

Indeed, that's weird.

Here's an extract from the datamap XML of the 3 tables: SENSOR_MODEL, SENSOR_TYPE, SENSOR_MODEL_SENSOR_TYPE (the MN table).

I think I have it all, let me know if you need something else.

thanks a lot

anthonin

SENSOR_MODEL

    <obj-entity name="SensorModel" className="org.oceanops.api.orm.SensorModel" dbEntityName="SENSOR_MODEL">

        <obj-attribute name="axesNo" type="java.lang.Integer" db-attribute-path="AXES_NO"/>

        <obj-attribute name="bodcId" type="java.lang.String" db-attribute-path="BODC_ID"/>

        <obj-attribute name="calibFrequency" type="java.lang.Integer" db-attribute-path="CALIB_FREQUENCY"/>

        <obj-attribute name="description" type="java.lang.String" db-attribute-path="DESCRIPTION"/>

        <obj-attribute name="id" type="java.lang.Integer" db-attribute-path="ID"/>

        <obj-attribute name="name" type="java.lang.String" db-attribute-path="NAME"/>

        <obj-attribute name="nameLong" type="java.lang.String" db-attribute-path="NAME_LONG"/>

        <obj-attribute name="nameShort" type="java.lang.String" db-attribute-path="NAME_SHORT"/>

        <obj-attribute name="originalDataset" type="java.lang.String" db-attribute-path="ORIGINAL_DATASET"/>

        <obj-attribute name="spec" type="byte[]" db-attribute-path="SPEC"/>

        <obj-attribute name="yearlyDrift" type="java.lang.Integer" db-attribute-path="YEARLY_DRIFT"/>

    </obj-entity>

    <db-entity name="SENSOR_MODEL" schema="OCEANOPS">

        <db-attribute name="AGENCY_ID" type="INTEGER" length="10"/>

        <db-attribute name="AXES_NO" type="NUMERIC" length="38"/>

        <db-attribute name="BODC_ID" type="VARCHAR" length="30"/>

        <db-attribute name="CALIB_FREQUENCY" type="NUMERIC" length="38"/>

        <db-attribute name="DESCRIPTION" type="VARCHAR" length="500"/>

        <db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>

        <db-attribute name="IMAGE_ID" type="INTEGER" length="10"/>

        <db-attribute name="NAME" type="VARCHAR" length="80"/>

        <db-attribute name="NAME_LONG" type="VARCHAR" length="150"/>

        <db-attribute name="NAME_SHORT" type="VARCHAR" length="50"/>

        <db-attribute name="ORIGINAL_DATASET" type="VARCHAR" length="30"/>

        <db-attribute name="SPEC" type="BLOB" length="4000"/>

        <db-attribute name="WEBLINK_ID" type="INTEGER" length="10"/>

        <db-attribute name="YEARLY_DRIFT" type="NUMERIC" length="38"/>

    </db-entity>

SENSOR_TYPE

    <obj-entity name="SensorType" className="org.oceanops.api.orm.SensorType" dbEntityName="SENSOR_TYPE">

        <obj-attribute name="description" type="java.lang.String" db-attribute-path="DESCRIPTION"/>

        <obj-attribute name="id" type="java.lang.Integer" db-attribute-path="ID"/>

        <obj-attribute name="name" type="java.lang.String" db-attribute-path="NAME"/>

        <obj-attribute name="nameShort" type="java.lang.String" db-attribute-path="NAME_SHORT"/>

        <obj-attribute name="pub47Ref" type="java.lang.String" db-attribute-path="PUB47_REF"/>

        <obj-attribute name="wigosCode" type="java.lang.String" db-attribute-path="WIGOS_CODE"/>

    </obj-entity>

    <db-entity name="SENSOR_TYPE" schema="OCEANOPS">

        <db-attribute name="DESCRIPTION" type="VARCHAR" length="250"/>

        <db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>

        <db-attribute name="NAME" type="VARCHAR" length="100"/>

        <db-attribute name="NAME_SHORT" type="VARCHAR" length="50"/>

        <db-attribute name="PUB47_REF" type="VARCHAR" length="50"/>

        <db-attribute name="SENSOR_FAMILY_ID" type="INTEGER" length="10"/>

        <db-attribute name="VARIABLE_ID" type="INTEGER" length="10"/>

        <db-attribute name="WIGOS_CODE" type="VARCHAR" length="200"/>

    </db-entity>

SENSOR_MODEL_SENSOR_TYPE

    <obj-entity name="SensorModelSensorType" className="org.oceanops.api.orm.SensorModelSensorType" dbEntityName="SENSOR_MODEL_SENSOR_TYPE">

        <obj-attribute name="accuracy" type="java.lang.String" db-attribute-path="ACCURACY"/>

        <obj-attribute name="precision" type="java.lang.String" db-attribute-path="PRECISION"/>

        <obj-attribute name="range" type="java.lang.String" db-attribute-path="RANGE"/>

        <obj-attribute name="resolution" type="java.lang.Integer" db-attribute-path="RESOLUTION"/>

        <obj-attribute name="sensorModelId" type="java.lang.Integer" db-attribute-path="SENSOR_MODEL_ID"/>

        <obj-attribute name="sensorTypeId" type="java.lang.Integer" db-attribute-path="SENSOR_TYPE_ID"/>

        <obj-attribute name="unit" type="java.lang.String" db-attribute-path="UNIT"/>

    </obj-entity>

    <db-entity name="SENSOR_MODEL_SENSOR_TYPE" schema="OCEANOPS">

        <db-attribute name="ACCURACY" type="VARCHAR" length="50"/>

        <db-attribute name="PRECISION" type="VARCHAR" length="50"/>

        <db-attribute name="RANGE" type="VARCHAR" length="50"/>

        <db-attribute name="RESOLUTION" type="VARCHAR" length="50"/>

        <db-attribute name="SENSOR_MODEL_ID" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>

        <db-attribute name="SENSOR_TYPE_ID" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>

        <db-attribute name="UNIT" type="VARCHAR" length="20"/>

    </db-entity>

Relationships

    <db-relationship name="sensorModelSensorTypes" source="SENSOR_MODEL" target="SENSOR_MODEL_SENSOR_TYPE" toDependentPK="true" toMany="true">

        <db-attribute-pair source="ID" target="SENSOR_MODEL_ID"/>

    </db-relationship>

    <db-relationship name="sensorModel" source="SENSOR_MODEL_SENSOR_TYPE" target="SENSOR_MODEL">

        <db-attribute-pair source="SENSOR_MODEL_ID" target="ID"/>

    </db-relationship>

    <db-relationship name="sensorType" source="SENSOR_MODEL_SENSOR_TYPE" target="SENSOR_TYPE">

        <db-attribute-pair source="SENSOR_TYPE_ID" target="ID"/>

    </db-relationship>

    <db-relationship name="sensorModelSensorTypes" source="SENSOR_TYPE" target="SENSOR_MODEL_SENSOR_TYPE" toDependentPK="true" toMany="true">

        <db-attribute-pair source="ID" target="SENSOR_TYPE_ID"/>

    </db-relationship>

    <obj-relationship name="sensorModelSensorTypes" source="SensorModel" target="SensorModelSensorType" deleteRule="Deny" db-relationship-path="sensorModelSensorTypes"/>

    <obj-relationship name="sensorModel" source="SensorModelSensorType" target="SensorModel" deleteRule="Nullify" db-relationship-path="sensorModel"/>

    <obj-relationship name="sensorType" source="SensorModelSensorType" target="SensorType" deleteRule="Nullify" db-relationship-path="sensorType"/>

    <obj-relationship name="sensorModelSensorTypes" source="SensorType" target="SensorModelSensorType" deleteRule="Deny" db-relationship-path="sensorModelSensorTypes"/>

You received this message because you are subscribed to a topic in the Google Groups "Agrest Framework User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/agrest-user/eWs5T-K5q9Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to agrest-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/agrest-user/8E04ED74-8E55-4637-902F-7642555CAB22%40objectstyle.com.

[Andrus Adamchik]

Hi Antonin,

I finally got around to trying your model. I copy/pasted it in our test suite, and ran it on Derby DB and Agrest 5.x. No issues found. Everything works as expected. So my next theory is what you mentioned before - maybe this is Oracle related. Do you see anything weird in the SQL generated by the app when you run a request with include crossing the relationship? Like the queries not returning the counts you'd expect, etc.?

Also are all the PKs and FKs involved in relationships defined as the same type on Oracle? (do a DESC on these three tables).

Thanks,

Andrus

[Anthonin Lizé]

Hi Andrus,

Thanks for digging into this.

I'm using Agrest 4.9 at the moment, maybe I should try with 5.x.

Checking the queries generated, everything looks fine. I ran those queries against the DB and the results are there. The logs show the number of rows returned.

PK and FK are in Oracle and Cayenne modeler both NUMBER(10,0), Integer for the Java type.

thanks again,

anthonin

Here are the logs:

16:01:46.698 [qtp300804223-27] DEBUG io.agrest.cayenne.compiler.CayenneAgEntityCompiler - compiling Cayenne entity for type: class org.oceanops.api.orm.Ptf
16:01:46.721 [qtp300804223-27] DEBUG io.agrest.cayenne.compiler.CayenneAgEntityCompiler - compiling Cayenne entity for type: class org.oceanops.api.orm.PtfVariable
16:01:46.723 [qtp300804223-27] DEBUG io.agrest.cayenne.compiler.CayenneAgEntityCompiler - compiling Cayenne entity for type: class org.oceanops.api.orm.SensorModel
16:01:46.723 [qtp300804223-27] DEBUG io.agrest.cayenne.compiler.CayenneAgEntityCompiler - compiling Cayenne entity for type: class org.oceanops.api.orm.SensorModelSensorType
16:01:46.724 [qtp300804223-27] DEBUG io.agrest.cayenne.compiler.CayenneAgEntityCompiler - compiling Cayenne entity for type: class org.oceanops.api.orm.SensorType
16:01:47.380 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - --- transaction started.
16:01:47.461 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - SELECT t0.ACTIVITY_CRITERION, t0.BATCH_REQUEST_REF, t0.CLOSURE_CRITERION, t0.DATA_URL, t0.DELETE_TAG, t0.DESCRIPTION, t0.E_NOTIFICATION_DATE, t0.ENDING_DATE, t0.ID, t0.INSERT_DATE, t0.LAST_UPDATE, t0.METADATA_AVAILABLE, t0.NAME, t0.NOK_REASON, t0.REF, t0.REF_PARENT, t0.UPDATE_DATE, t0.VALIDATED, t0.WIGOS_SYNCHRONISED, t0.BACKUP_TELECOM_ID, t0.BACKUP_TRACKING_SYSTEM_ID, t0.DATA_STATUS_ID, t0.DOC_ID, t0.ENDING_CAUSE_ID, t0.LAST_LOC_ID, t0.LATEST_OBS_ID, t0.PROGRAM_ID, t0.PTF_AUTOMATION_ID, t0.PTF_BATCH_STATUS_ID, t0.PTF_DEPL_ID, t0.PTF_HARDWARE_ID, t0.PTF_IDENTIFIERS_ID, t0.PTF_MODEL_ID, t0.PTF_SOFTWARE_ID, t0.PTF_STATUS_ID, t0.RETRIEVAL_ID, t0.SERVICE_SCHEDULE_ID, t0.TELECOM_ID, t0.TRACKING_SYSTEM_ID FROM OCEANOPS.PTF t0 WHERE t0.ID = ? [bind: 1->ID:1026308]
16:01:47.752 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - === returned 1 row. - took 367 ms.
16:01:47.785 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - +++ transaction committed.
16:01:48.188 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - --- transaction started.
16:01:48.198 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - SELECT t0.ACCURACY, t0.AVERAGING_METHOD, t0.AVERAGING_PERIOD, t0.CALIB_DATE, t0.CALIB_DATE_NEXT, t0.COMMENTS, t0.CONTROL_SCHEDULE, t0.DISTANCE_X, t0.DISTANCE_Y, t0.END_DATE, t0.FIRST_MEASURED, t0.HEIGHT, t0.HEIGHT_SECONDARY_REF_LEVEL, t0.ID, t0.LAST_MEASURED, t0.MANUF_DATE, t0.NB_OBS, t0.OWNERSHIP, t0.PACKAGE_N, t0.PRECISION, t0.RANGE, t0.RESOLUTION, t0.SAMPLING_RATE, t0.SAMPLING_RATE_TOTAL, t0.SERIAL_NO, t0.SIDE_INDICATOR, t0.START_DATE, t0.TIME_OFFSET, t0.VARIABLE_ID, t0.DATA_PROCESSING_METHOD_ID, t0.MANUF_WEBLINK_ID, t0.PROGRAM_ID, t0.PTF_ID, t0.SENSOR_EXPOSURE_ID, t0.SENSOR_LOCATION_ID, t0.SENSOR_MODEL_ID, t0.SENSOR_STATUS_ID, t0.SERVICE_SCHEDULE_ID, t0.WEBLINK_ID, t1.ID FROM OCEANOPS.PTF_VARIABLE t0 JOIN OCEANOPS.PTF t1 ON t0.PTF_ID = t1.ID WHERE t1.ID = ? [bind: 1->ID:1026308]
16:01:48.259 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - === returned 5 rows. - took 71 ms.
16:01:48.292 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - +++ transaction committed.
16:01:48.767 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - --- transaction started.
16:01:48.769 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - SELECT t0.AXES_NO, t0.BODC_ID, t0.CALIB_FREQUENCY, t0.DESCRIPTION, t0.ID, t0.NAME, t0.NAME_LONG, t0.NAME_SHORT, t0.ORIGINAL_DATASET, t0.SPEC, t0.YEARLY_DRIFT, t0.AGENCY_ID, t0.IMAGE_ID, t0.WEBLINK_ID, t1.ID FROM OCEANOPS.SENSOR_MODEL t0 JOIN OCEANOPS.PTF_VARIABLE t1 ON t0.ID = t1.SENSOR_MODEL_ID JOIN OCEANOPS.PTF t2 ON t1.PTF_ID = t2.ID WHERE t2.ID = ? [bind: 1->ID:1026308]
16:01:48.857 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - === returned 2 rows. - took 89 ms.
16:01:48.888 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - +++ transaction committed.
16:01:49.271 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - --- transaction started.
16:01:49.278 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - SELECT DISTINCT t0.ACCURACY, t0.PRECISION, t0.RANGE, t0.RESOLUTION, t0.SENSOR_MODEL_ID, t0.SENSOR_TYPE_ID, t0.UNIT, t1.ID FROM OCEANOPS.SENSOR_MODEL_SENSOR_TYPE t0 JOIN OCEANOPS.SENSOR_MODEL t1 ON t0.SENSOR_MODEL_ID = t1.ID JOIN OCEANOPS.PTF_VARIABLE t2 ON t1.ID = t2.SENSOR_MODEL_ID JOIN OCEANOPS.PTF t3 ON t2.PTF_ID = t3.ID WHERE t3.ID = ?
[bind: 1->ID:1026308]
16:01:49.338 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - === returned 2 rows. - took 66 ms.
16:01:49.370 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - +++ transaction committed.
16:01:49.747 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - --- transaction started.
16:01:49.752 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - SELECT DISTINCT t0.DESCRIPTION, t0.ID, t0.NAME, t0.NAME_SHORT, t0.PUB47_REF, t0.WIGOS_CODE, t0.SENSOR_FAMILY_ID, t0.VARIABLE_ID, t1.SENSOR_MODEL_ID, t1.SENSOR_TYPE_ID FROM OCEANOPS.SENSOR_TYPE t0 JOIN OCEANOPS.SENSOR_MODEL_SENSOR_TYPE t1 ON t0.ID = t1.SENSOR_TYPE_ID JOIN OCEANOPS.SENSOR_MODEL t2 ON t1.SENSOR_MODEL_ID = t2.ID JOIN OCEANOPS.PTF_VARIABLE t3 ON t2.ID = t3.SENSOR_MODEL_ID JOIN OCEANOPS.PTF t4 ON t3.PTF_ID = t4.ID WHERE t4.ID = ? [bind: 1->ID:1026308]
16:01:49.808 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - === returned 2 rows. - took 59 ms.
16:01:49.839 [qtp300804223-27] INFO  org.apache.cayenne.log.JdbcEventLogger - +++ transaction committed.

and the results in the browser (underlined is where the issue happens):

To view this discussion on the web visit https://groups.google.com/d/msgid/agrest-user/7CA9D455-E29E-4641-8C84-B1A457AEF921%40objectstyle.com.

[Anthonin Lizé]

Hi Andrus and all,

Just to let you know, I completed a test with 5.0.M6, and it works well!

So I guess an issue related to 4.x? Maybe something to do with JaxRS, since you worked a lot on this for 5.x?

Anyway, I will just move to 5.x and that will be it. A good reason to spend time on upgrading.

Thanks for your efforts!

Have a nice weekend,

Anthonin

[Andrus Adamchik]

Thanks for the update. This is very good to know. Agrest 5.0 FTW :) 

<image.png>