Gino: How to do selection of few columns with joining 2 tables

[neeraj-mindtickle]

• GINO version: 0.8.1
• Python version: 3.7
• asyncpg version:
• aiocontextvars version:
• PostgreSQL version:

Description

Describe what you were trying to get done.
Tell us what happened, what went wrong, and what you expected to happen.

Hi,

I have 2 tables which I want to join and want to do selection of few columns with operations like array_agg on a column.

What I Did

Currently, my query looks like this:
MeetingThemes.__table__.join(Themes.__table__). select([MeetingThemes.meeting_id, db.func.array_agg(db.func.distinct(Themes.theme_name), type_=db.ARRAY(db.VARCHAR)), db.func.count(db.func.distinct(Themes.theme_name))]). where(MeetingThemes.tenant_id == tenant_id). group_by(MeetingThemes.meeting_id). gino. all()

The error I am receiving:
sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type instead.

One possible reason is I am using distinct method on a column and then using array_agg on those distinct value. SQL raw query for my requirement looks like this and working fine:

SELECT mt.meeting_id as meeting_id, ARRAY_AGG(distinct themes.theme_name) as all_themes, count(DISTINCT(themes.theme_name)) as count FROM meeting_themes mt INNER JOIN theme_info themes on themes.theme_id = mt.theme_id WHERE mt.tenant_id = 123 GROUP by mt.meeting_id;

How can I write this query in Gino?

[fantix]

Try this one:

db.select(
    [
        MeetingThemes.meeting_id,
        db.func.array_agg(
            db.func.distinct(Themes.theme_name), type_=db.ARRAY(db.VARCHAR)
        ),
        db.func.count(db.func.distinct(Themes.theme_name)),
    ]
).select_from(
    MeetingThemes.join(Themes)
).where(
    MeetingThemes.tenant_id == tenant_id
).group_by(
    MeetingThemes.meeting_id
).gino.all()

[neeraj-mindtickle]

Thanks, Fantix. It worked.

[fantix]

Good to know! 😃