Romain Piel Romain Piel - 1 year ago 77
Android Question

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

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



A purchase can have several products and one products can be in several purchases.
Using ORMLite I could have a
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 Source

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

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:

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