How do I model a many-to-many relationship over 3 tables in SQLAlchemy (ORM)?

Question

I'm rusty in SQL and completely new to SQL Alchemy, but have an upcoming project which uses both. So I thought I write something to get comfortable. Suffering from a hangover I decided to write something to keep track of alcohol levels.

I have events where users participate and consume drinks. Those are my three basic tables (with one helper table guestlist, for a m:n relationship between users and events).

drinks list the drinks available at all events to all users all the time (no need to map anything). users are created from time to time, so are events. all users can join all events, so I use the guestlist table to map those.

Now to the heart of the question: I need to keep track at what time which user consumes which drink on which event. I try to solve this whit another table shots (see below) but I'm not sure if this a good solution.

_{(source: anyimg.com)}

In SQL Alchemy it might look somewhat like this (or not, but this is what I came up with so far)::

guestlist_table = Table('guestlist', Base.metadata,
    Column('event_id', Integer, ForeignKey('events.id')),
    Column('user_id', Integer, ForeignKey('users.id'))
)

class Event(Base):

  __tablename__ = 'events'

  id = Column(Integer, primary_key=True)
  name = Column(String(80), nullable=False)
  started = Column(DateTime, nullable=False, index=True,
    default=datetime.datetime.now
  )
  users = relationship("User", secondary=guestlist_table, backref="events")
  # ...

class User(Base):

  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False, unique=True, index=True)
  birthdate = Column(Date, nullable=False)
  weight = Column(Integer, nullable=False)
  sex = Column(Boolean, nullable=False)
  # ...

class Drink(Base):

  __tablename__ = 'drinks'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False)
  volume = Column(Numeric(5,2), nullable=False)
  percent = Column(Numeric(5,2), nullable=False)
  # ...

class Shots(Base):

  __tablename__ = 'shots'

  id = Column(Integer, primary_key=True)
  at = Column(DateTime, nullable=False,
    default=datetime.datetime.now
  )
  user_id = Column(Integer, ForeignKey('users.id'), index=True)
  event_id = Column(Integer, ForeignKey('events.id'), index=True) 
  drink_id = Column(Integer, ForeignKey('drinks.id'))
  user = relationship("User", backref="shots")
  event = relationship("Event", backref="shots")
  drink = relationship("Drink", uselist=False) # one-to-one, no backref needed

I struggle to find a good way to build a table that maps events, users and drinks together: How should I formulate the relationships and how do I query it?

The thing is I kinda feel I overlooked something. And frankly I'm absolute lost on how to query it?

Here are the queries I would make most of the time:

• I at least need to get all the shots consumed on a event (probably sorted by user)
• I also sometimes need all shots for a specific user (probably sorted by event)
• And a lot of counting:
  • Number of shots per event
  • Number of shots per user
  • Number of shots a user downed at a event

Is the shots table a okay way to manage this?

Answer 1

I suggest you to draw an Entity Relationship Diagram, it would be much more clear for people and you to understand your question.

To answer your question:

I at least need to get all the shots consumed on a event (probably sorted by user)

To get all shots of an event, you could try

session.query(Shots).filter_by(event_id=event_id)

The event_id is the id of event you like to query. To store by user, you can try

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .order_by(asc(Shots.user_id))

Of course, you may want to sort by attribute of an user, you could join user table.

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .join(User) \
    .order_by(asc(User.name))

easy enough.

I also sometimes need all shots for a specific user (probably sorted by event)

Just like previous example

Number of shots per event

session.query(Shots) \
    .filter_by(event_id=event_id) \
    .count()

I didn't run they, I just wrote them here, but they should all work if I didn't make a typo.