Problems with fetch_all_with_all_relation()

Forum for posting problems using QxOrm library

Problems with fetch_all_with_all_relation()

Postby Yves » Tue Jun 16, 2020 7:47 am

Hi,

I've got a small test project to evaluate QxOrm. The use case is an on-demand production with a few objects. The whole project code is also attached to this post as well, see below.

Image

I created 3 Address objects (one invoice Address with a 1:1 relation to a Customer object, two delivery Address objects with a 1:1 relation to an Order object each):

Code: Select all
    CustomerPtr customer(new Customer());
    ...
    AddressPtr invoiceAddress(new Address());
    invoiceAddress->setStreet("Musterstrasse");
    ...
    OrderPtr order(new Order());
    order->setCustomer(customer);

    OrderPtr order2(new Order());
    order2->setCustomer(customer);

    AddressPtr deliveryAddress(new Address());
    deliveryAddress->setStreet("Lieferstrasse");
    ...

    AddressPtr deliveryAddress2(new Address());
    deliveryAddress2->setStreet("Anderelieferstrasse");
    ...
    deliveryAddress->setOrder(order);
    order->setDeliveryAddress(deliveryAddress);

    deliveryAddress2->setOrder(order2);
    order2->setDeliveryAddress(deliveryAddress2);


    // persist customer, adresses and order to database
    daoError = qx::dao::save(customer);
    daoError = qx::dao::save(invoiceAddress);
    daoError = qx::dao::save(order);
    daoError = qx::dao::save(deliveryAddress);
    daoError = qx::dao::save(order2);
    daoError = qx::dao::save(deliveryAddress2);

    AddressVec addresses;
    qx::dao::fetch_all_with_all_relation(addresses);
    for (auto address : addresses)
    {
        qInfo() << "################### " << address->order()->id() << address->id() << address->street();
    }


The problem is the last part: I try to get all Address entries from tha database and check them to see whether they are connected to an order (which makes them a delivery address, invoice addresses don't have a related order). The output is:

Code: Select all
[QxOrm] sql query (total: 0.923 ms, db_exec: 0.281 ms) : SELECT Address.addressId AS Address_addressId_0, Address.street AS Address_street_0, Address.number AS Address_number_0, Address.city AS Address_city_0, Address.zipcode AS Address_zipcode_0, Customer_1.customerId AS Customer_1_customerId_0, Customer_1.firstName AS Customer_1_firstName_0, Customer_1.lastName AS Customer_1_lastName_0, Orders_2.orderId AS Orders_2_orderId_0, Orders_2.date AS Orders_2_date_0, Orders_2.state AS Orders_2_state_0, Orders_2.customer AS Orders_2_customer_0_2 FROM Address LEFT OUTER JOIN Customer Customer_1 ON Customer_1.customerId = Address.addressId LEFT OUTER JOIN Orders Orders_2 ON Orders_2.orderId = Address.addressId
###################  1 1 "Musterstrasse"
###################  2 2 "Lieferstrasse"


But I expected to get something like:

Code: Select all
###################  -1 1 "Musterstrasse"
###################  1 2 "Lieferstrasse"
###################  2 3 "Anderelieferstrasse"


I don't quite get, what happens there and how to get the correct result.
Attachments
production_qxorm.7z
The project code.
(20.57 KiB) Downloaded 4696 times
Yves
 
Posts: 5
Joined: Tue Jun 16, 2020 7:22 am

Re: Problems with fetch_all_with_all_relation()

Postby qxorm » Tue Jun 16, 2020 8:51 am

Hello,

I applied 3 modifications to your project to get the expected result (see attached project, you can do a diff to compare with yours) :

1- To work with relations, I would recommend to enable the option :
Code: Select all
qx::QxSqlDatabase::getSingleton()->setForceParentIdToAllChildren(true);


2- Your program crashed when an address is not associated to an order, so I added a condition to check that and put -1 if no order :
Code: Select all
qInfo() << "################### " << (address->order() ? address->order()->id() : -1) << address->id() << address->street();


3- You used a lot relationOneToOne, but this is really a very specific case when you share the same id between 2 entities.
So I replaced all relationOneToOne by relationManyToOne, for example in address.cpp file :
Code: Select all
   t.relationManyToOne(& Address::m_customer, "customerId");
   t.relationManyToOne(& Address::m_order, "orderId");
Attachments
production_qxorm_modif_1.zip
(9.97 KiB) Downloaded 4686 times
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Problems with fetch_all_with_all_relation()

Postby Yves » Tue Jun 16, 2020 9:12 am

Thanks!

I'm only used to databases where relations are kept in separate tables, so I was not sure about how 1:1 relations work with QxOrm. The crash is embarassing though, I should have caugth that... :oops:

But what exactly does this do:
Code: Select all
 qx::QxSqlDatabase::getSingleton()->setForceParentIdToAllChildren(true);
Yves
 
Posts: 5
Joined: Tue Jun 16, 2020 7:22 am

Re: Problems with fetch_all_with_all_relation()

Postby qxorm » Tue Jun 16, 2020 9:58 am

But what exactly does this do: qx::QxSqlDatabase::getSingleton()->setForceParentIdToAllChildren(true);

After saving a 1-n relationship, QxOrm library will automatically assigns the parent id to all items of the 'n' part of the relationship.
This is useful when you use :
Code: Select all
qx::dao::insert_with_relation
qx::dao::insert_with_all_relation
qx::dao::update_with_relation
qx::dao::update_by_query_with_relation
qx::dao::update_with_all_relation
qx::dao::update_by_query_with_all_relation
qx::dao::save_with_relation
qx::dao::save_with_all_relation
qx::dao::save_with_relation_recursive
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Problems with fetch_all_with_all_relation()

Postby Yves » Tue Jun 16, 2020 10:02 am

Ahh I see, Thanks again!
Yves
 
Posts: 5
Joined: Tue Jun 16, 2020 7:22 am

Re: Problems with fetch_all_with_all_relation()

Postby Yves » Tue Jun 16, 2020 1:42 pm

I've got another problem: I'm trying to delete one ProductionData object. When I look at the database, I see that it's gone. When I try to update my machine object by fetching its data from the database, QxOrm throws an error.

Related code in main.cpp (I stripped some parts for readability).
Code: Select all
    prodData = machine->productionData().takeFirst(); // machine had two ProductionData, now only one is left in the list
    qx::dao::delete_by_id(prodData);                  // delete from DB
    prodData.clear();                                 // clear/delete pointer

    ProductionDataVec prodDatas;
    qx::dao::fetch_all_with_all_relation(prodDatas);  // fetch remaining ProductionData with all relations

    QString ids;
    for (auto pdata : prodDatas)                      // create a string of the IDs of the remaining ProductionData
    {
        ids.append(QString::number(pdata->id()) % " "); 
    }
    qInfo().noquote() << "--    Fetch remaining production data (IDs): " << ids;    // prints "2", meaning only one ProductionData is left, OK!
   
    qx::dao::fetch_by_id_with_all_relation(machine);   // This FAILS with an error
    ...


I expected the last call to come up with only one related ProductionData left in machine. Instead it fails with an error message:
Code: Select all
[QxOrm] prepare sql query failed : SELECT Machine.machineId AS Machine_machineId_0, Machine.state AS Machine_state_0, ProductionData_1.productionDataId AS ProductionData_1_productionDataId_0, ProductionData_1.tool AS ProductionData_1_tool_0, ProductionData_1.program AS ProductionData_1_program_0, ProductionData_1.machine AS ProductionData_1_machine_0_1, ProductionData_1.parentOrder AS ProductionData_1_parentOrder_0_1 FROM Machine WHERE Machine.machineId = :machineId
Database error number '1' : Unable to execute statement
no such column: ProductionData_1.productionDataId
[QxOrm] dump sql query bound values :
  - :machineId :


Does the fetch-statement use cached data? It seems that it relies on some old relation data that still assumed two ProductionData in machine, not the current one from the database...
Attachments
production_qxorm.7z
(24.86 KiB) Downloaded 4675 times
Yves
 
Posts: 5
Joined: Tue Jun 16, 2020 7:22 am

Re: Problems with fetch_all_with_all_relation()

Postby qxorm » Wed Jun 17, 2020 12:06 pm

Hello,

Does the fetch-statement use cached data?

No.

Before getting your SQL error, there is an ASSERT in file IxSqlRelation.cpp, line 550 : so there is something wrong :)
Your problem is the definition of the relation 1-N in the file : machine.cpp
Instead of :
Code: Select all
t.relationOneToMany(& Machine::m_productionData, "productionDatas", "machineId");

You have to write :
Code: Select all
t.relationOneToMany(& Machine::m_productionData, "productionDatas", "machine");


The third parameter "machine" must be a key defined in the target entity, and in productiondata.cpp file, you defined it like this :
Code: Select all
t.relationManyToOne(& ProductionData::m_machine, "machine");


Please note that with QxEntityEditor application, you can't have this kind of error, because the generated C++ source code will be valid for QxOrm library.
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Problems with fetch_all_with_all_relation()

Postby Yves » Thu Jun 18, 2020 8:24 am

Thank you again!
Yves
 
Posts: 5
Joined: Tue Jun 16, 2020 7:22 am


Return to QxOrm - Help

Who is online

Users browsing this forum: No registered users and 3 guests

cron