Python sqlalchemy get all ratings from one user

Question

I have a question. For example, I have two tables - users and suggestions.

Each user can give some rating to this suggestion, for example, there is a suggestion "Improve the interface".

User #1 gives this suggestion a rating of 10, and user #2 gives it a rating of 3.

The next suggestion, "Fix bugs", was rated only by user #1, who gave it a rating of 5.

What I want to do is to somehow get every rating that a user has given to all the suggestions. Is it possible to do this? How could I do it?

Here is the basic code, and I don't know what my next steps should be.

class Suggestion(Base):
    __tablename__ = "suggestions"
    s_id = Column("s_id", Integer, primary_key=True, autoincrement=True)
    content = Column("content", String, nullable=False)
    rating = Column("rating", Integer, nullable=False, default=0)

    def __repr__(self):
        return f"Suggestion #{self.id}: {self.content}"


class User(Base):
    __tablename__ = "users"
    user_id = Column("user_id", Integer, primary_key=True)

    def __init__(self, user_id):
        self.user_id = user_id

    def __repr__(self):
        return f"User #{self.user_id}"

Answer 1

This is a many-to-many relationship (a user can rate many suggestions, and a suggestion can be rated by many users), so you can model this with an association table between user and suggestion.

Please check the document on: Association Object for more information.

This is a sample implementation. I'm using the dataclass integration to automatically generate the __init__ and __repr__ method:

from __future__ import annotations

from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import Mapped, MappedAsDataclass, sessionmaker
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(MappedAsDataclass, DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    ratings: Mapped[list[Rating]] = relationship(init=False, repr=False)


class Rating(Base):
    __tablename__ = "rating"
    user_id: Mapped[int] = mapped_column(
        ForeignKey("user.id"),
        primary_key=True,
    )
    suggestion_id: Mapped[int] = mapped_column(
        ForeignKey("suggestion.id"),
        primary_key=True,
    )
    score: Mapped[int] = mapped_column(default=0)
    suggestion: Mapped[Suggestion] = relationship(init=False, repr=False)


class Suggestion(Base):
    __tablename__ = "suggestion"
    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    content: Mapped[str] = mapped_column(String(255), nullable=False)


DATABASE_URL = "postgresql+psycopg2://test_username:test_password@localhost:5432/test"
engine = create_engine(
    DATABASE_URL,
    # echo=True,
)
Session = sessionmaker(
    bind=engine,
)

if __name__ == "__main__":
    Base.metadata.create_all(engine)

    # create initial users and suggestions
    with Session.begin() as session:
        session.add_all([
            User(id=1),
            User(id=2),
            Suggestion(content="Improve the interface."),
            Suggestion(content="Fix bugs."),
        ])

    # users rating suggestions:
    with Session() as session:
        suggestion1_id = session.scalars(
            select(Suggestion.id).filter_by(content="Improve the interface.")
        ).one()
        suggestion2_id = session.scalars(
            select(Suggestion.id).filter_by(content="Fix bugs.")
        ).one()

        session.add_all([
            Rating(user_id=1, suggestion_id=suggestion1_id, score=10),
            Rating(user_id=2, suggestion_id=suggestion1_id, score=3),
            Rating(user_id=1, suggestion_id=suggestion2_id, score=5),
        ])
        session.commit()

    # getting ratings for a user:
    with Session() as session:
        user1 = session.get(User, 1)
        print(user1)
        for rating in user1.ratings:
            print(
                f"  rated `{rating.suggestion.content}`"
                f" with a score of {rating.score}"
            )

This is the output:

User(id=1)
  rated `Improve the interface.` with a score of 10
  rated `Fix bugs.` with a score of 5

This can be done in numerous ways, so read the docs and pick one you like.