www.qxorm.com

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.



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.

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");

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...

But what exactly does this do:

Code: Select all

 qx::QxSqlDatabase::getSingleton()->setForceParentIdToAllChildren(true);

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

Ahh I see, Thanks again!

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...

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.

Thank you again!