What is the best way to implement many-to-many relationships using ORMLite?

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?

Solution 1

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

Solution 2

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

Comments

[Romain Piel]

Ok many thanks Gray! I just wanted to be sure and you explained it well.

[eento]

Thanks too for the answer but Gray, this fact is really poorly documented.. Not good for massive android apps which want to use ORMLite..

[Gray]

Can you be more specific @eento? Can you write some better docs?

[Parampal Pooni]

lookupPostsForUser() is here: github.com/j256/ormlite-jdbc/blob/…

[Rauter]

I understand why and how this works. However, this changes the representation of the data. Shouldn't a Purchase have a collection (List<>) of Products and the Products have a collection of Purchases they belong to?