To select only some columns from some tables using session object

Question

Hello,

I have these classe where items (class Item) is related to channel object:

channel_items = Table(
        "channel_items",
        metadata,
        Column("channel_id", Integer,
            ForeignKey("channels.id")),
        Column("item_id", Integer,
            ForeignKey(Item.id))
    )


class Channel(rdb.Model):
    """ Set up channels table in the database """
    rdb.metadata(metadata)
    rdb.tablename("channels")

    id = Column("id", Integer, primary_key=True)
    title = Column("title", String(100))

    items = relation(Item, secondary=channel_items, backref="channels")

class Item(rdb.Model):
    """ Set up items table in the database """
    rdb.metadata(metadata)
    rdb.tablename("items")

    id = Column("id", Integer, primary_key=True)
    title = Column("title", String(100))

I know how to get all the columns using something like:

session = rdb.Session() channels = session.query(Channel).order_by(Channel.title)

However, I'd like to get some columns from both tables and the field items in channel object to be related to Item class because I've tried something like this:

session = rdb.Session()
channels = session.query(Channel.title, Item.title).order_by(Channel.title)

I got the channel title and item title, but I just get one item from every channel. I'd like to get all the items related to every channel.

Thanks in advance!

Answer 1

You want join here, not Cartesian product.

If I understand you correctly, and you want to select only titles, w/out building actual instances, it can be done like this:

session = rdb.Session()
result = session.query(Channel).join(Channel.items).values(Channel.title, Item.title)

Result is generator, which will provide you (Channel.title, Item.title) tuples. So if you have some 'channel1' which has two items 'item1' and 'item2', you will receive [('channel1', 'item1'), ('channel1', 'item2')]

If you need to just load channels with their items associated, you would probably want this instead:

from sqlalchemy.orm import eagerload
channels = session.query(Channel).options(eagerload('items')).all()
channels[0].items[0].title