0

I am doing a project in Flask, I have a difficulty with Flask-SQLAlchemy.

class Films(db.Model):
    """Main film table"""
    __tablename__ = 'films'
    film_id = db.Column(db.Integer, primary_key=True)
    film_name = db.Column(db.Text, unique=False, nullable=False, index=True)
class Genres(db.Model):
    """Table with genres"""
    __tablename__ = 'genres'
    genre_id = db.Column(db.Integer, primary_key=True)
    genre_name = db.Column(db.String(20), unique=True, nullable=False)
    gen_films = db.relationship(
        'Films', secondary=films_genres, lazy='subquery',
        backref=db.backref('gen_films', lazy='joined'))
# Association table for table films and table genres
films_genres = db.Table(
    'films_genres',
    db.Column('films_film_id', db.Integer, db.ForeignKey('films.film_id')),
    db.Column('genres_genre_id', db.Integer, db.ForeignKey('genres.genre_id')))
all_films = Films.query.select_from(Films).join(films_genres).join(Genres).filter(
      (films_genres.c.films_film_id == Films.film_id) & (films_genres.c.genres_genre_id == Genres.genre_id))\
    .add_columns(
            Films.film_name,
            Genres.genre_name
            ).paginate()

When I make a request, I get an output like this:

[(<Films 1>, 'The Shawshank Redemption', 'Action'), (<Films 1>, 'The Shawshank Redemption', 'Crime')] ...

How can I redo the query to get this kind of result?

[(<Films 1>, 'The Shawshank Redemption', ('Action', 'Crime')]

Or something similar so that the genre list is one line, in order to further transfer this to flask_restx -> Resource -> json

Dima
  • 3
  • 3
  • Try grouping the result https://newbedev.com/group-by-count-function-in-sqlalchemy – Ammar Aslam Nov 16 '21 at 12:04
  • Added group by `Films.query.select_from(Films).join(films_genres).join(Genres).filter( (films_genres.c.films_film_id == Films.film_id) & (films_genres.c.genres_genre_id == Genres.genre_id))\ .add_columns(Films.film_name, Genres.genre_name).group_by( Films.film_name, Genres.genre_name).paginate()` But got an error : `[parameters: {'param_1': 20, 'param_2': 0}] (Background on this error at: https://sqlalche.me/e/14/f405)` – Dima Nov 16 '21 at 12:23
  • I've been trying to replicate your models for the last hours but I keep getting errors – Ammar Aslam Nov 16 '21 at 13:12
  • Can you give the error text above this message – Ammar Aslam Nov 16 '21 at 13:13
  • Sory https://gist.github.com/di2mot/6e4e1cc0c6ae81d2159f7ed46063615b – Dima Nov 16 '21 at 14:10
  • Might be possible to do these in separate queries; don't join, just list films and then add the tuple of genres in each film – Ammar Aslam Nov 16 '21 at 15:01
  • Yes, I did it, and it works, it's just not economical in terms of resources. I don't like this approach ( – Dima Nov 16 '21 at 16:58

0 Answers0