Many to Many relationship

[Shemahmforash]

I've been creating a project using ramses (SQLAlchemy as the backend engine) by following the example project - https://github.com/brandicted/ramses-example - and the tutorial published on realpython.com - https://realpython.com/blog/python/create-a-rest-api-in-minutes-with-pyramid-and-ramses - although I'm stuck with something I see as a simple problem: I've been able to define one to may relationships using the schemas, but I haven't managed to do the same for many to many relationships. Is it possible to define many to many relationships using the schemas in ramses?

I've you've like to check the schemas I want to be related, here they are: https://github.com/Shemahmforash/ThisDayInMusicAPI/blob/develop/thisdayinmusic/schemas/tracks.json and https://github.com/Shemahmforash/ThisDayInMusicAPI/blob/develop/thisdayinmusic/schemas/playlists.json

Thanks a lot for your help.

[jstoiko]

Hi Carlos,

There are multiple ways to achieve this:

1. You can have a field of type: "list" on each side which list the ids of the other side. Simple.
2. Add support for two-way relationship fields by adding a junction table. This requires additional work. If you're interested, you can take a look at nefertari-sqla - the underlying engine that Ramses uses for SQLA - and we can discuss adding that feature.
3. Use mongodb instead: $ pip install nefertari-mongodb, then set nefertari.engine = nefertari_mongodb in your .ini file. I haven't tested it but it should work out-of-the-box.

[*] If you go with solution 1 and want to ensure that the ids exist on the other side, you can write a Processor that checks that the ids being passed in the requests exist in the database.

[vlcinsky]

Many to many relationship can be often necessary - in many systems there is no other choice.

We were investigating current options with Ramses (optionally with nefertari) and did not succeed yet. In the popular example The Pizza Factory there is a relation between a pizza recipe and a cheese, which seem to be as many to many. Evaluating the example we did not succeed making it running (even trying older versions of all related databases), anyway, it seems like the schema for a cheese contains a field linking to a pizza what is rather surprising. So even this did not served as example of possible solution.

As we really need to resolve the many to many (a user can have membership in multiple teams), we are trying to clarify current options:

1. use Ramses with the list (this works as long as the relationship does not have attribute like "team_role"
2. use nefertari (if it already supports junction table structure
3. use pure pyramid and do it ourself.

As this ticket is still open, I assume, ramses is not yet ready for junction tables.

The question is: is nefertari (and nefertari_sqla) able to support junction tables?

[jstoiko]

hey @vlcinsky: nefertari and nefertari-sqla are "just" layers on top of Pyramid and SQL Alchemy respectively. So whatever is supported underneath could be abstracted on top. Same applies to ramses which abstracts most of nefertari's features.

You might want to look at the #1 solution described in my original answer. This would be my preferred solution if I had to do this. I would use a Processor or an asynchronous worker to clean-up stale relationships.

[vlcinsky]

I wonder what is the meaning of:

So whatever is supported underneath could be abstracted on top. Same applies to ramses which abstracts most of nefertari's features.

Does it mean, that the underlying features (like UniqueConstraint from sqlalchemy') are already somehow reachable via these libraries or it is possible after nefertari_sqla is modified to provide such feature?

[jstoiko]

The latter.

For example, to understand how foreign keys are implemented in nefertari-sqla, you can look at those 2 classes:

• ForeignKeyField
• BaseSchemaItemField

This abstraction is exposed as engine.ForeignKeyField(...) using nefertari and _db_settings.type = "foreign_key" using ramses