Error querying a derived class with a relation in base class

[jduran]

Dear all,

I've written some a small class hierarchy mapped to QxOrm. As a difference of qxDllSample, the relation of 1 to N is set to the base class. QxOrm is able to create the tables of the database successfully but, when querying information about the derived class, the column of the external key refers to the table of the base class than the table of the derived class.

Bellow, there is an example:

Hierarchy of classes:
Class Appointment: has a relation of 1 to N with AbsInfo class.
Class AbsInfo: has a relation of N to 1 with Appointment class. Appoint_id is the foreign key to Appointment.
Class InfoHL7: derives from AbsInfo. (no more additional info is included respected to base class)

The tables corresponding to the previous classes are created successfully. See the SQL sentences bellow:

Code: Select all

[QxOrm] sql query (112 ms) : CREATE TABLE Appointment (appoint_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, description TEXT)
[QxOrm] sql query (100 ms) : CREATE TABLE AbsInfo (info_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, description TEXT, appoint_id INTEGER)
[QxOrm] sql query (98 ms) : CREATE TABLE InfoHL7 (info_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, description TEXT, appoint_id INTEGER)


At the program, when I query for HL7 objects, the next SQL query is executed. as you can see, appoint_id column is referring to AbsInfo table, but not to InfoHL7.

Code: Select all

SELECT InfoHL7.info_id AS InfoHL7_info_id_0, InfoHL7.description AS InfoHL7_description_0, AbsInfo.appoint_id AS AbsInfo_appoint_id_0 FROM InfoHL7


The same happens when relations are also included when querying for infoHL7 objects. The SQL sentence is bellow:

Code: Select all

SELECT InfoHL7.info_id AS InfoHL7_info_id_0, InfoHL7.description AS InfoHL7_description_0, AbsInfo.appoint_id AS AbsInfo_appoint_id_0, Appointment_1.appoint_id AS Appointment_1_appoint_id_0, Appointment_1.description AS Appointment_1_description_0 FROM InfoHL7 LEFT OUTER JOIN Appointment Appointment_1 ON Appointment_1.appoint_id = AbsInfo.appoint_id


Could you confirm that this is a bug of the library?

Thanks and Best Regards,
Joaquim Duran

[QxOrm admin]

Hi,

Could you confirm that this is a bug of the library ?

Perhaps it's a regression of the new relationships engine of the version BETA of QxOrm library 1.2.4 BETA XXX.

Have you tried to test your case with the official version QxOrm 1.2.3 (not a BETA version) ?
Do you have the same issue with QxOrm 1.2.3 ?

[jduran]

Hello,

I've repeated the execution with QxOrm version 1.2.3 and the generated SQL works fine. Bellow are included the generated SQL queries:

Code: Select all

[QxOrm] sql query (0 ms) : SELECT InfoHL7.info_id AS InfoHL7_info_id_0, InfoHL7.description AS InfoHL7_description_0, InfoHL7.appoint_id AS InfoHL7_appoint_id_0, Appointment_1.appoint_id AS Appointment_1_appoint_id_0, Appointment_1.description AS Appointment_1_description_0 FROM InfoHL7 LEFT OUTER JOIN Appointment Appointment_1 ON Appointment_1.appoint_id = InfoHL7.appoint_id
[QxOrm] sql query (1 ms) : SELECT InfoHL7.info_id AS InfoHL7_info_id_0, InfoHL7.description AS InfoHL7_description_0, InfoHL7.appoint_id AS InfoHL7_appoint_id_0 FROM InfoHL7
Info Id: 1


Thanks and Best Regards,
Joaquim Duran

[QxOrm admin]

I've repeated the execution with QxOrm version 1.2.3 and the generated SQL works fine.

Sorry, even if it's working fine with QxOrm 1.2.3, forget my previous message !
I think that QxOrm 1.2.4 BETA XXX have the good behaviour and your query doesn't have to work fine : you can't define relationships into bases classes.
This is a design problem between C++ classes and tables of database, not a bug of the library.
Don't forget that there is tables mapped to your C++ classes, so you can't do all things that you can do with object and inheritance concepts

So, here is my advice :
- in your base class, you can register all properties you want ;
- but in your base class, you can't register a relationship => you have to register all relationships into your derived classes.

So, in your case :
- remove registration of your relationship from AbsInfo class ;
- add registration of your relationship to InfoHL7 class.

I think this is a global issue with many ORM, here is a similar post using Hibernate :
http://chriswongdevblog.blogspot.fr/200 ... ships.html

[jduran]

Dear all,

IMO, you should not be so restrictive, this is what I've observed:

- If an appointment has serveal InfoHL7 associated objectets, and I store the Appointment to the DDBB with all relations, all infoHL7 objects are stored as AbsInfo object, so inherited information and type info is lost.
- If an InfoHL7 object has an Appointment object and I store the InfoHL7 object to the DDBB with all relations, the appointment is stored and the InfoHL7 is stored in its corresponding table. When I query for an InfoHL7 object and an Appointment, no information is lost (well, this should to be fixed in version 1.2.4BXX).

What I see, in some way is that the information of the type is lost (All objects are stored as AbsInfo lost their type) but if I store/query an object with their own type (in this case quering an InfoHL7 object), no information is lost, including inherited relations.

If you want to test that, I could send the testing code that I've written to you.

Joaquim Duran

[QxOrm admin]

Here is a new version BETA of QxOrm 1.2.4 (with a fix for your problem to have the same behaviour as version 1.2.3), you can download it :
http://www.qxorm.com/version/QxOrm_1.2.4_BETA_23.zip

in some way is that the information of the type is lost

Yes, this is why I recommend to not register relationship into base class.

Could you please tell me if the new version BETA (QxOrm 1.2.4 BETA 23) is working fine for you ?

[jduran]

Hello,

Could you please tell me if the new version BETA (QxOrm 1.2.4 BETA 23) is working fine for you ?

Yes, It works fine.

Joaquim Duran