KeyedTuple with a list of related data

Question

I have a couple of simple models that look something like this:

class StoreImage(Base):
    imagepath = Column(Text, nullable=False)
    store_id = Column(Integer, ForeignKey('store.id'), nullable=False)
    store = relationship('Store')

class Store(Base):
    status = Column(Enum('published', 'verify', 'no_position',
                         name='store_status'),
                    nullable=False, default='verify')
    type = Column(Enum('physical', 'web', name='store_type'),
                  nullable=False, default='physical')
    name = Column(Text, nullable=False)
    street = Column(Text)
    postal_code = Column(Text)
    city = Column(Text)
    country = Column(Text)
    phone_number = Column(Text)
    email = Column(Text)
    website = Column(Text)
    location = Column(Geometry(geometry_type='POINT', srid=4326))
    is_flagship = Column(Boolean, default=False)
    images = relationship(StoreImage)

Now what I want to do is query like this:

q = Store.query.filter(Store.is_flagship == True).with_entities(
    Store.id,
    Store.status,
    Store.slug,
    Store.type,
    Store.name,
    Store.street,
    Store.postal_code,
    Store.city,
    Store.country,
    Store.phone_number,
    Store.email,
    Store.website,
    func.ST_AsGeoJSON(Store.location).label('location'),
    Store.images,
)

The query works, but Store.images just returns True for every row. How can I make it return a list of StoreImage instances/KeyedTuples?

I want to do it this way mainly because I haven't found any other way to make Store.query return the location in GeoJSON format.

EDIT: One solution for me would be to just return Store instances from the query and somehow add in location GeoJSON, either on the declared model or some other way if possible. Don't know how to do this though.

score 1 · Accepted Answer · edited Aug 08 '14 at 09:49

Your current query not only returns wrong value, but in fact returns wrong number of rows, because it will perform a cartesian product of both tables.
Also I would not overwrite the column name location. So I will use geo_location in the code below.

You are right, and in order to pre-load images, you have to query for the whole Store instances. For example, like the query below:

q = (session.query(Store)
        .outerjoin(Store.images) # load images
        .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
        .filter(Store.is_flagship == True)
    ).all()

In order to combine the two, you can do the following:

q = (session.query(Store, func.ST_AsGeoJSON(Store.location).label('geo_location'))
        .outerjoin(Store.images) # load images
        .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
        .filter(Store.is_flagship == True)
    ).all()

# patch values in the instances of Store:
for store, geo_location in q:
    store.geo_location = geo_location

Edit-1: Alternatively try using column_property

class Store(...):
    # ...
    location_json = column_property(func.ST_AsGeoJSON(location).label('location_json'))

    q = (session.query(Store).label('geo_location'))
            .outerjoin(Store.images) # load images
            .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
            .filter(Store.is_flagship == True)
        ).all()
    for store in q:
        print(q.location_json)
        print(len(q.images))

Thanks for explaining @van. Is it possible to not have it returned as a two-tuple with Store at first index and location at second? Perhaps with `Bundle`? — jmagnusson, Aug 07 '14 at 13:57
These are actually instances of [`KeyedTuple`](http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.util.KeyedTuple), and I think the `Bundle` will give you the same end result. But why don't you try using [`column_property`](http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#sqlalchemy.orm.column_property). I updated the answer with an example. — van, Aug 07 '14 at 20:35

KeyedTuple with a list of related data

1 Answers1