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.
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))