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