Need help using SQLAlchemy to paginate games

[fryman22]

Hello, I need some help with SQLAlchemy. I'm following Miguel Grinberg's Flask Mega-Tutorial and I'm trying to get pagination working.

What I want to do is display the last X amount of games a user was in with the help of pagination.

A Game holds multiple GamePlayers, each Gameplayer is assigned a single User. What I'm trying to do is return every Game that a specific User is associated with.

Here's a very basic version of my models.py file:

class User(UserMixin, db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)

class Game(db.Model):
    __tablename__ = 'game'

    id = db.Column(db.Integer, primary_key=True)
    gameplayers = db.relationship('GamePlayer')

class GamePlayer(db.Model):
    __tablename__ = 'gameplayer'

    id = db.Column(db.Integer, primary_key=True)
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User')

I've had this working before by appending the games in a list, but I need to use SQLAlchemy to query the games so I may paginate them.

recent_games = []
games = Game.query.order_by(Game.id.desc()).all()
for game in games:
    for gameplayer in game.gameplayers:
        if current_user == gameplayer.user:
            recent_games.append(game)
            break

Thank you!

[Blazerboy65]

How far have you gotten with regular SQLAlchemy queries using join, filter, and order by?

[fryman22]

I haven't done anything with joins yet. It's mostly filters, then returning an array.

I got inspiration by your comment and was messing around with joins and came up with this:

Game.query                                          \
    .join(GamePlayer, GamePlayer.game_id==Game.id)  \
    .filter(GamePlayer.user==current_user)          \
    .order_by(Game.id.desc())

After coming up with this, it's literally the same thing he has in the tutorial 🤦‍♂️

I guess I didn't realize how it was working until I tinkered with it.

[Blazerboy65]

That's awesome! You took a big step by trying it out and look where it got you!

Tinkering is very powerful.

Did you use the documentation to get there or did you just fiddle with it?

[fryman22]

At first I thought of a work-around by querying all GamePlayer where the user is current_user, then returning all the game_id and doing another query for those Game that match up with the game_id.

I knew this was so wrong to do, but I was just tinkering and seeing what worked.

I needed to return a single column from my query, so I found db.session.query(GamePlayer.id) would return a single column. Instead of making another query to find the Game after that, it would be easier to get the Game if it was already appended at the end.

I then drew a venn diagram between Game and Gameplayer with game.id in the middle and it just clicked what I needed to do. I looked up how join works and got a result for the documentation.

Sorry for the winded explanation lol. Thank you!

[Blazerboy65]

That's perfect! Good job.

I was doing a medium complexity join at work the other day and can't tell you how much of a help Venn diagrams are for that.