CODEWITHSUNDEEP
pythonjoinfilterflasksqlalchemy
ericMTR
ericMTR

Reputation: 211

How to obtain data from a table that has been joined

I have two tables items and games.

@app.route('/collection/<username>/<int:page>/<platform>/<path:path>')    
def collection(username, page=1, platform='DMG', path=None):
    # first I get the user by his username
    user = User.query.filter_by(username=username).first()
    # then I get all items form the user and related games
    items = user.items.join(Game)
    # until now it works perfectly fine        
    # now I would like to obtain all titles from the joined table games
    game_titles = items.filter(Game.title).all()
    # but unfortunately I get only an empty list

What is missing?

Here my models:

class Game(db.Model):
    __tablename__ = 'games'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), index=True)
    publisher = db.Column(db.String(32), index=True)
    region = db.Column(db.String(3), index=True)
    code_platform = db.Column(db.String(3), index=True)
    code_identifier = db.Column(db.String(4), index=True)
    code_region = db.Column(db.String(3), index=True)
    code_revision = db.Column(db.String(1))
    code = db.Column(db.String(16), index=True, unique=True)
    year = db.Column(db.Integer)

    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    items = db.relationship('Item', backref='game', lazy='dynamic')

    def __repr__(self):
        return '<Game %r>' % (self.title)


class Item(db.Model):
    __tablename__ = 'items'
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(8), index=True)
    cart = db.Column(db.Boolean)
    box = db.Column(db.Boolean)
    manual = db.Column(db.Boolean)

    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'))

    def __repr__(self):
       return '<Collection %r>' % (self.user_id)

Upvotes: 0

Views: 204

Answers (2)

univerio
univerio

Reputation: 20518

You have two options. Using SQLAlchemy ORM:

game_titles = [i.game.title for i in user.items]

To make this more efficient, you can apply the joinedload optimization:

game_titles = [i.game.title for i in user.items.options(joinedload(Item.game))]

Alternatively, you can use SQLAlchemy core if all you care about are the titles (and nothing else):

game_titles = user.items.join(Item.game).with_entities(Game.title).all()

You can even skip fetching the user altogether if you don't care about the user at all:

game_titles = User.query.join(User.items).join(Item.game).filter(User.username == username).with_entities(Game.title).all()

As an aside, .filter and .filter_by correspond to the selection operator in relational algebra, whereas .with_entities and db.session.query(...) correspond to the projection operator, contrary to what you had initially assumed.

Upvotes: 1

tryexceptpass
tryexceptpass

Reputation: 559

Try something like this:

items.join(Game).options(joinedload(Item.game, innerjoin=True))

Essentially, you're joining with Game and explicitly loading it, where the innerjoin forces it to do so only on the games listed in the table you're joining with (items)

Upvotes: 1

Related Questions