Combine two queries, sqlalchemy

Question

I'm really new to sqlalchemy (as well as sql), so please don't shoot.

I have two queries:

user_logins_count = self.request.db.query(
    sa.func.count(UserSession.id)
).filter(
    sa.and_(
        UserSession.user_id == user_id,
        sa.between(UserSession.date_login_session, start_date, end_date)
    )
)

and

user_questions_count = self.request.db.query(
    sa.func.count(QuestionAnswer.id).label('questions_amount')
).filter(
    sa.and_(
        QuestionAnswer.user_id == user_id,
        sa.between(QuestionAnswer.created, start_date, end_date)
    )
)

Is it possible to have a single query instead of this two?

van · Accepted Answer · 2020-07-14T05:46:56.767

Simple subquery should do the trick:

q = session.query(user_logins_count.subquery(), user_questions_count.subquery())
logins_count, questions_count = q.first()

Another, and a bit more involved, way of getting the information at the same time as you are retrieving the User instance itself would be to use hybrid_method:

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    name = Column(String(255))

    @hybrid_method
    def user_logins_count(self, start_date: datetime, end_date: datetime):
        return len(
            [
                _login
                for _login in self.sessions
                if start_date <= _login.date_login_session <= end_date
            ]
        )

    @classmethod
    @user_logins_count.expression
    def user_logins_count_(cls, start_date: datetime, end_date: datetime):
        return (
            select([func.count(UserSession.id).label("user_logins_count")])
            .where(UserSession.user_id == cls.id)
            .where(UserSession.date_login_session >= start_date)
            .where(UserSession.date_login_session <= end_date)
            .group_by(UserSession.user_id)
            .label("user_logins_count")
        )

In this case, the following query can be executed:

res = session.query(User, User.user_logins_count(start_date, end_date)).filter(
    User.id == user_id
)
user, logins_count = res.first()
print(user)
print(logins_count)

Adding the questions_count can be done in the same way.

score 5 · Answer 2 · answered Jul 07 '20 at 20:28

Assuming that UserSession and QuestionAnswer are two tables in which user_id can be used as a foreign key, you can do something like

query(UserSession).join(QuestionAnswer, UserSession.user_id = QuestionAnswer.user_id)

to join the tables. If you have a join, then you can more easily query them as one unit, assuming that they both have the date information that you need.

That said, you have some complex stuff to check and it might make more sense to do two queries and join them up than to have a complicated sub-query. Or, it might make the most sense to do a union!

If you post your data structure, I can help you more specifically to build a single query that can do what you want to do. It's definitely possible, but it can get a bit tricky, so it would be really helpful to see an example of the data you are using and the data models you built (the classes that underlie the QuestionAnswer and UserSession tables).

References

Somewhat unpleasant issue with the join based solution is the need to deal extensively with the cases when there are no rows in the children tables or none that fit the criteria. — van, Jul 14 '20 at 04:43

Combine two queries, sqlalchemy

2 Answers2