What is the best way to implement many-to-many rel

Question

I'm currently playing with ORMlite to make a model with tables and relationships. One relationship is a many-to-many relationship. What's the best way to implement that?

To be more concrete:

Let's say I've got these two tables

Product
   id
   brand

Purchase
   id

A purchase can have several products and one products can be in several purchases. Using ORMLite I could have a @ForeignCollectionField in each model but I don't think it would work. The only valid solution I see is to make a third table Product_Purchase to link Product and Purchase with many-to-one relationships.

What do you folks think?

Answer 1

You have to create a ProductPurchase class and manage it like it was another object that has to go into your Database.

You can (but you don't have to) have a Collection of Products inside Purchases (and vice-versa) but they will have to be manually updated/created when you load the relations between Products and Purchases from the ProductPurchase linker table. Having those collections mean nothing for the ORM (you won't and shouldn't annotate them).

If anyone is looking for and Android App with the Many-to-Many relationship I worked on an example: https://github.com/arthurrauter/ormlite-android

Answer 2

Ok I guess the only way to go is to create a third table Product_Purchase. It is indicated in a sample project.

Answer 3

@Romain's self answer is correct but here's some more information for posterity. As he mentions there is an example many-to-many ORMLite project that demonstrates the best way to do this:

http://ormlite.com/docs/example-many

The example uses a join table with the id's of both of the objects to store a relationship. In @Romain's question, the join object would have both the Product and the Purchase object. Something like:

public class ProductPurchase {
    @DatabaseField(generatedId = true)
    private int id;
    @DatabaseField(foreign = true)
    private Product product;
    @DatabaseField(foreign = true)
    private Purchase purchase;
    ...
}

The id fields get extracted from the objects which creates a table like:

CREATE TABLE `userpost` (`id` INTEGER AUTO_INCREMENT , `user_id` INTEGER ,
    `post_id` INTEGER , PRIMARY KEY (`id`) ) 

You then use inner queries to find the Product objects associated with each Purchase and vice versa. See lookupPostsForUser() method in the example project for the details.

There has been some thought and design work around doing this automatically but right now ORMLite only handles one-to-many relationships internally.