CODEWITHSUNDEEP
mysqlsqlsqlalchemyflask-sqlalchemy
Victor Silva
Victor Silva

Reputation: 3

How to make this query in sqlalchemy?

SELECT
  maintener.*,
  (SELECT COUNT(*)
     FROM device d
     WHERE d.in_stock_maintener_id = maintener.id) AS in_stock_devices
FROM maintener;

I'm creating a report that show all mainteners but i need to show the number of devices that each one of that mainteners has by looking at the devices model reference in_stock_maintener_id;

I have this models in my persist sqlalchemy.

class Maintener(persist.Base):
    __tablename__ = 'maintener'

      id = Column(Integer, primary_key=True)
      name = Column(String(255))
      document_number = Column(String(30))
      phone_1 = Column(String(12))
      phone_2 = Column(String(12))
      email = Column(String(255))




class Device(persist.Base):

    __tablename__ = 'device'

      id = Column(Integer, primary_key=True)
      serial = Column(String(45))
      in_stock = Column(SmallInteger)
      in_stock_maintener_id = Column(ForeignKey(u'maintener.id'), nullable=True, index=True)

    in_stock_maintener = relationship(u'Maintener', lazy='noload', \
      primaryjoin='Device.in_stock_maintener_id == Maintener.id')

If anyone could help me, i'll be grateful =)

Upvotes: 0

Views: 104

Answers (1)

van
van

Reputation: 76952

sq = (
    session
    .query(func.count())
    .select_from(Device)
    .filter(Device.in_stock_maintener_id == Maintener.id)
).as_scalar()

q = session.query(Maintener, sq.label('in_stock_devices'))

Query above will return an enumerable of tuple(Maintener, Integer).

If you would like to have columns instead (as per your comment), then you can either specify the columns you want in the query implicitly:

q = session.query(Maintener.id, Maintener.name, sq.label('in_stock_devices'))

or if you would like all columns (as in SELECT *), then you could query the Table instead of the mapped entity:

q = session.query(Maintener.__table__, sq.label('in_stock_devices'))

Above I assumed that you use declarative extension.

Upvotes: 1

Related Questions