Question - why do hasMany relationships require a separate join table?

[nicholasf]

Hi,

I'm just coming up to speed with ORM2. Great work so far!

I'm curious why you chose to implement hasMany by relying on a third join table between the associated models? In Downstairs (the ORM I created but probably won't continue) I assumed that if a Foo hasMany Bars, then the Bar table would have a foreign key called foo_id.

https://github.com/nicholasf/downstairs.js/blob/master/lib/collection.js#L127

Cheers,
Nicholas

[dresende]

In your foobar example, you can do Bar.hasOne(Foo). I know the associations can be a little tricky and names can be confusing specially for people having some background study on the matter.

hasOne associations is for associations like your example (you have a table with a foreign key to another)
hasMany associations is for Many-to-Many associations, since you can very recently activate reverse option and access associations from both sides (and have extra properties on the middle table).

[nicholasf]

Ok, I understand. The fundamental difference is that you don't have a belongsTo abstraction.

Have you used ActiveRecord (the Ruby version, not Fowler's pattern) at all?

I've read through your association definitions here https://github.com/dresende/node-orm2#hasone-vs-hasmany . Are you open to changing them to match what ActiveRecord has used successfully? If so I will help (I have this logic in Downstairs.js already).

I think this will be a confusing point for a lot of people who have used association semantics before.

Your solution also means that three database tables are required for hasMany relationships - this is not necessary if you introduce the belongsTo association, which means only two database tables are required. I think other people will point this out in the future.

Many to many mappings are obviously possible with belongsTo associations. You simply create a join table and model - for example a FooBars table, where Foo hasMany FooBars and Bar hasMany FooBars. Then, in ActiveRecord, you define a hasMany "through" condition where you can directly call foo.getBars().

The way I think about it is if the foreign key is on the model, it belongsTo another model. If there can be multiple references back to the same column, it's a hasMany (and calling getB should return an array), otherwise it's a hasOne.

Like I said, I'm happy to work on this for you.

[dresende]

I never used ActiveRecord but don't mind changing orm to match it. I don't want to break people's code so maybe we could use other names or have an option to change this behavior.

[nicholasf]

Ok, let me think about it.

I agree that we shouldn't break existing code. It would also be nice to support this style of associations too (unless the code becomes too complex).

[dxg]

I also found this confusing initially, but using Bar.hasOne(Foo) makes perfect sense once you think about it.

[nicholasf]

My current thinking is that it will confuse things to support different styles of associations. The problem is that you are forced to use a third join table to do a hasMany and it isn't necessary. Although the current solution is workable and I'm inclined to just accept it rather than add complexity with another set of associations. :)

[dresende]

We can change the hasMany association when no reverse option is defined and no extra properties are set to use only the model tables and not third table.

[nicholasf]

Agreed. I'll think about that.

[dresende]

When you have a clear definition of the changes to propose, please open a new ticket describing it.

[simg]

Hi, I have this same issue.

For example, I have two models. "Article" and "Image" (it's a blog / cms).

The Article hasMany Images but also an Image may belong to many articles.

In my database (postgres), I'd like to have an indexed multi-value column called (say) "images" on the "articles" table.

From the docs (and this post #276) it seems that the way to make this work with node orm uses something like the following code (but it's not clear how this would work):

db.models.role.hasMany('app_module', db.models.app_module, {}, { 
  mergeTable: 'role_app_module',
  getAccessor: "getAppModules",
  setAccessor: "setAppModules",
  addAccessor: "setAppModules",
  delAccessor: "removeAppModules",
  hasAccessor: "hasAppModules"
});

So my questions:

Am I on the right lines?

Might there any sample code to flesh out more specifically what this should look like ?

Many thanks in advance

[dxg]

What do you mean by indexed multi-value column?

[simg]

Sorry, throwback to a previous life (Pick Databases). Postgres calls multi-value columns arrays. Most RDBMS can only store single values in columns hence patterns like the join table. Postgres allows storing of values like [5,3,1,4] in one column (or ["imagekey1","imagekey2","imagekey3"]). (sorry if that's teaching you to suck eggs).

So, an index on that array column and you have everything you need for fast access to both sides of a hasMany relationship.

[dxg]

ORM expects many-to-many relationships to use a join table. Weather you let ORM auto generate one or use your own, it still expects a join table. I don't think any amount of hacking will get you what you want unless you implement the association yourself from scratch.

[simg]

you implement the association yourself from scratch

I think this is what it will probably come to :).

I've noticed though that node-orm doesn't support postgres array fields at all? so I'm probably going to have to implement my own data layer since arrays are an essential feature for my app(s).

thanks for you help.