How QxOrm done with this case?

[henyana]

Hi all,

I've just thinking about this multi-columns/composite-key using QxOrm to build this table below:

Code: Select all

Create table CustomerProducts
(
    CustomerID int references Customers(CustomerID) not null,
    ProductID int references Products(ProductID) not null,
    OrderLimit int not null,
    Primary key (CustomerID, ProductID)
)


The CustomerID and ProductID, both are as the Reference and also the Primary Key.
How you define those CustomerProducts class table related to Customer and Product tables?

Anyway I still can do this statement using single Primary Key for that particular case:

Code: Select all

Create table CustomerProducts
(
    Customer_ProductID int identity primary key,
    CustomerID int references Customers(CustomerID) not null,
    ProductID int references Products(ProductID) not null,
    OrderLimit int not null
)


But I prepared to choose the first statement to avoid duplicated data like this:

Code: Select all

CustomerProductID    CustomerID    ProductID    OrderLimit
1                    1             100          25
2                    1             100          30


best regards,
hdy

[QxOrm admin]

Hi,

I think you have a relation many-to-many between a Customer table and a Product table, and you have an extra-table CustomerProducts to link this relation many-to-many, that's right ?
If this is your design, I think this post will be useful for you :
viewtopic.php?f=2&t=45
The most interesting for you starts from page 2.

Here is the solution from this thread, you just have to replace Receipt by Customer and Article by Product :

-----------

Maybe there is a solution with current version of QxOrm library !
I have not tested yet but maybe it works :

1- You have your 2 classes : "Receipt" and "Article"
2- Create another class : "ReceiptArticle" with 3 properties : quantity, amount and TVA
3- Instead of define "many-to-many" relation for both "Receipt" and "Article" classes, define relation of type "one-to-many" to "ReceiptArticle" class
4- Into your "ReceiptArticle" class, define 2 relations "many-to-one" to "Receipt" and "Article" classes

So you obtain something like this :

Code: Select all

"Article" class   [one-to-many]    <---->   [many-to-one]   "ReceiptArticle" class    [many-to-one]    <---->    [one-to-many]   "Receipt" class

To fetch all datas with 1 query, initialize a "ReceiptArticle" instance, put a Receipt id or an Article id and fetch with all relations.
You can test it if you want, I think it could work...

[henyana]

Thanks for you fast reply.

It seem to me those solution might crated duplicated data like in the second case.
But I will try it.

One more question:
How can I add this statement to the table on QxOrm? My goal is to prevent duplicated data regarding CustomerID and ProductID.

Code: Select all

CREATE unique index cust_products_unique on CustomerProducts (CustomerID, ProductID)

regards,
hdy

[QxOrm admin]

It seem to me those solution might crated duplicated data like in the second case.

No, absolutely not ! I think my proposition is exactly your first case :

Code: Select all

Create table CustomerProducts
    (
        CustomerID int references Customers(CustomerID) not null,
        ProductID int references Products(ProductID) not null,
        OrderLimit int not null,
        Primary key (CustomerID, ProductID)
    )

QxOrm library will not verify your condition on your index automatically : this is the role of your database and not your ORM.
When you try to insert an element to the database with an ID already saved => the database must return an error, not directly the ORM.

How can I add this statement to the table on QxOrm? My goal is to prevent duplicated data regarding CustomerID and ProductID.

Why do you want to define this into QxOrm library ?
You have to define your conditions on the database !

This is why I strongly recommend to not use qx::create_table<T> function (this function is only for test, not for a production software).
It's better to use a tool provided by your SGBD to design and put conditions (indexes, views, etc...) to your database.

BUT (yes there is a BUT ), in the next version of QxOrm library (version 1.1.9 released in few days), I have added a new method : qx::QxClassX::dumpSqlSchema().
This is like qx::create_table<T>, not for a production software, BUT you can write your own function (based on qx::QxClassX::dumpSqlSchema()) if you really want to generate automatically your SQL schema. With all improvements on introspection engine of QxOrm library, it will be easy to write all conditions you want and generate your SQL schema...