Failed to use multiple multi-level relations in query

[alecn2002]

Hi,

I have the following relation structure in my DB:

Process could have many Objects, and every Object could participate in many Processes (many-to-many)
Object could have many Documents attached (one-to-many)
Document could consist of many Files (one-to-many)
each of Process, Object, Document have certain Type (many-to-one, types for each entity are stored in separate tables)

related .hpp and .cc files are attached (simplified for this post, so they could or could not be compiled; also they are relevant to my post in viewtopic.php?f=2&t=86#p1053)

I try to fetch multiple levels of relation hierarchy in one query.

When I define relations like this:

Code: Select all

const QStringList ProcessDAO::relations__(
        QStringList()
                    << "process_type_id"
                    << "Object_list->Document_list->document_type_id"
                    << "Object_list->object_type_id"
                    << "Object_list->Document_list->File_list"
       );


I get the following SQL (field defs part omitted, relations only):

Code: Select all

FROM "Process"
LEFT OUTER JOIN "ProcessType" "ProcessType_1"
  ON "ProcessType_1"."id" = "Process"."process_type_id"
LEFT OUTER JOIN "ProcessObject" "ProcessObject_8"
  ON "Process"."id" = "ProcessObject_8"."process_id"
LEFT OUTER JOIN "Object" "Object_8"
  ON "ProcessObject_8"."object_id" = "Object_8"."id"
LEFT OUTER JOIN "Document" "Document_12"
  ON "Document_12"."object_id" = "Object_8"."id"
LEFT OUTER JOIN "File" "File_16"
  ON "File_16"."document_id" = "Document_12"."id"


no object type, nor document type

When I shuffle relations like this:

Code: Select all

const QStringList ProcessDAO::relations__(
        QStringList()
                    << "process_type_id"
                    << "Object_list->Document_list->document_type_id"
                    << "Object_list->Document_list->File_list"
                    << "Object_list->object_type_id"


I get object type, but no documents:

Code: Select all

FROM "Process"
LEFT OUTER JOIN "ProcessType" "ProcessType_1"
  ON "ProcessType_1"."id" = "Process"."process_type_id"
LEFT OUTER JOIN "ProcessObject" "ProcessObject_8"
  ON "Process"."id" = "ProcessObject_8"."process_id"
LEFT OUTER JOIN "Object" "Object_8"
  ON "ProcessObject_8"."object_id" = "Object_8"."id"
LEFT OUTER JOIN "ObjectType" "ObjectType_9"
  ON "ObjectType_9"."id" = "Object_8"."object_type_id"


So, looks like QxOrm takes the last complicated relation from the list with the same head part. Though, documentation states that this kind of complicated relations could be used.

Please advice the solution for this problem.

I use Linter 6.1 (http://www.linter.ru/en/main/) DB engine, it has QT connector and works OK with pure QT.

PS:
QxOrm 1.2.5
QT 5.0.1
boost 1.53.0

[qxorm]

Hi,

I will try to see what happens as soon as possible, but sorry for now I have no time to do it.
Are you running in debug or release mode ? Just to verify because in debug mode, there is an assert if you try to insert an invalid relation key.
Maybe you can try multiple levels of relationships like this : "*->*->*" (for 3 levels). Is it working fine with this syntax ?

[alecn2002]

Hi,

Thanks for your prompt answer.

I've tried it in debug mode only, not in release mode. I didn't receive error messages about invalid key(s). Also I've tried to change the order of relation definitions, and it always worked with the last definition of the set with the same beginning part, so all keys are OK.

I've tried to use "*->*->*", and it works OK, but verrrrryy slow.

I've found temporary workaround: I fetch the most nesessary relations (entity types in my case), and then run thru related entities and fetch the rest of relations one-by-one. This works, but overcomplicates my code. Also in some cases it prevents me from fetching all entity types in the first run, so I need to get too many objects and then filter them out.

[qxorm]

Your issue should be fixed, you can download a BETA version to test it here :
http://www.qxorm.com/version/QxOrm_1.2.6_BETA_03.zip

Please, let me know if your bug has been fixed with this BETA version, thx !

[alecn2002]

Thank you for your efforts.

Unfortunately multiple multi-level relations don't work even with this new version of QxOrm.

I was forced to switch to another project (not even C++), so I don't have time to analyze it in-depth right now.

When I'll return to my QxOrm-based project, I will perform analysis and report you back.

[remico]

hello.
It seems that last month I had the same problem.
And I even used the same workaround for multilevel relations (through all levels of the relations, one-by-one) )
So, I have tested QxOrm 1.2.6_beta_03.
It creates correct SQL query now (adds JOIN for each table from relations list).
But I have a little question

Let's suppose we have a cell phone.
It has 2 SIM cards.
Each SIM card has 2 phone numbers and 2 SMS saved in its internal memory (total: (2+2) different phone numbers and (2+2) different SMS) .
So, we can have 4 tables and use trivial relations, something like this :

Code: Select all

QStringList relation() << "simcards_list->phonenumbers_list" << "simcards_list->sms_list";

I want get a single object (Cellphone object #1) with all its related data (i.e. 1 cellphone, 2 simcards, 4 sms and 4 phonenumbers totally).
So I create an empty object:

Code: Select all

qx::dao::ptr<Cellphone> obj(new Cellphone);
obj->id = 1;
qx::dao::fetch_by_id_with_relation(relation, obj);

The code compiled without any problems; but at runtime I have an assert error:

Assertion failed!
...
boost-1_52/boost/archive/basic_binary_iprimitive.hpp, line 98
Expression: 0==i || 1==i

on the

Code: Select all

qx::dao::fetch_by_id_with_relation(relation, obj);

code line.

Meanwhile, if I use temporary collection, e.g. qx::QxCollection<Cellphone_ptr> templist, then
I can successfully execute

Code: Select all

qx::dao::fetch_by_query_with_relation(relation, qx_query().where("Cellphone.id").isEqualTo(1), templist);

and

Code: Select all

qx::dao::fetch_all_with_relation(relation, templist);

So multilevel relations can be resolved fine with collections.
And after that I can do something like this

Code: Select all

Cellphone_ptr obj = templist.getByIndex(0);

and obj can be used in later code as I want early (i.e. as single object).

So my questions:
why I can't fetch all related data for single object using multilevel relation list?
is that right behavior of QxOrm for single object and multilevel relations?

Thanks

[qxorm]

So, I have tested QxOrm 1.2.6_beta_03.
It creates correct SQL query now.

Yes ! I have fixed an issue in this BETA version.
Strange that the problem of alecn2002 is not resolved with this version !

So my questions:
why I can't fetch all related data for single object using multilevel relation list?
is that right behavior of QxOrm for single object and multilevel relations?

Strange ! I have tested your case and it works fine for me.
Please could you provide some minimal source code to reproduce your issue ?

[remico]

Hah... Sorry, I really don't understand what's going on...
I tried reproduce the error with clean sample of code.
But I really can't. The sample works fine.
However the error still going on in the application when I try to use fetch_by_id_with_relation() func.
Thereby, a workaround with temporary collections still in use.
I'll try find the reason. And of course I'll tell you if any.

[qxorm]

Thereby, a workaround with temporary collections still in use.
I'll try find the reason. And of course I'll tell you if any.

Ok thx !

[remico]

As I mentioned above, I used qx::dao::ptr and qx::QxCollection types.
So I tried to use boost::shared_ptr smart pointer and now I CAN use single objects without any workarounds.
Strange...

But it's works with qx::dao::fetch* functions.
And it's NOT works with qx::dao::update_with_relation(), for example.
i.e. NOT ALL of the relations in my multi-level list are used for UPDATE operation.
Strange...

P.S.
if my sample will works correctly for you, I will post more detailed description of the app's behavior and all SELECTs and UPDATEs queries

thx