1

I want to translate the below postgres query into Sqlalchemy asyncio format, but so far, I could only retrieve the first column only, or the whole row at once, while I need only to retrieve only two columns per record:

SELECT
    table.xml_uri,
    max(table.created_at) AS max_1 
FROM
    table
GROUP BY
    table.xml_uri 
ORDER BY
    max_1 DESC;

I reach out to the below translation, but this only returns the first column xml_uri, while I need both columns. I left the order_by clause commented out for now as it generates also the below error when commented in:

Sqlalchemy query:

from sqlalchemy.ext.asyncio import AsyncSession

query = "%{}%".format(query)
records = await session.execute(
    select(BaseModel.xml_uri, func.max(BaseModel.created_at))
    .order_by(BaseModel.created_at.desc())
    .group_by(BaseModel.xml_uri)
    .filter(BaseModel.xml_uri.like(query))
)

# Get all the records
result = records.scalars().all()

Error generated when commenting in order_by clause:

column "table.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Yaakov Bressler
  • 9,056
  • 2
  • 45
  • 69
Omar AlSaghier
  • 340
  • 4
  • 12
  • At first glance this looks ok - are you sure you didn't have `scalar()` instead of `scalars()` in your original code? – snakecharmerb Jul 18 '22 at 08:50
  • @snakecharmerb, yes, I copied that from the code. so far, I could find a work around from this answer `https://stackoverflow.com/questions/67809165/sqlalchemy-select-only-one-column-syntax-1-4` and I could retrieve them as a list of dictionaries that I will handle the logic from that point. – Omar AlSaghier Jul 18 '22 at 09:06
  • Ah, ok, each row in the resultset is a tuple `(uri, max_date)`, so `scalars` only takes the first element. Omitting the call `scalars()` should work. – snakecharmerb Jul 18 '22 at 09:27
  • You can't order by created date, but if you give the max a label you can order by that: `select(M.uri, max(date).label('mymax')).group_by(M.uri).order_by('mymax')` – snakecharmerb Jul 18 '22 at 09:31
  • @snakecharmerb, Thank you so much. my whole problem was is `scalars()`. but regarding the order, I implemented that out of the query as I might receive desc, or asc from the front-end. If you can please post this as an answer so I can accept it. – Omar AlSaghier Jul 18 '22 at 09:57

1 Answers1

3

The query is returning a resultset consisting of two-element tuples. session.scalars() is taking the first element of each tuple. Using session.execute instead will provide the desired behaviour.

It's not permissable to order by the date field directly as it isn't part of the projection, but you can give the max column a label and use that to order.

Here's an example script:

import sqlalchemy as sa
from sqlalchemy import orm 

Base = orm.declarative_base()


class MyModel(Base):
    __tablename__ = 't73018397'

    id = sa.Column(sa.Integer, primary_key=True)
    code = sa.Column(sa.String)
    value = sa.Column(sa.Integer)


engine = sa.create_engine('postgresql:///test', echo=True, future=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(engine, future=True)

with Session.begin() as s:
    for i in range(10):
        # Split values based on odd or even
        code = 'AB'[i % 2 == 0]
        s.add(MyModel(code=code, value=i))


with Session() as s:
    q = ( 
        sa.select(MyModel.code, sa.func.max(MyModel.value).label('mv'))
        .group_by(MyModel.code)
        .order_by(sa.text('mv desc'))
    )   
    res = s.execute(q)
    for row in res:
        print(row)

which generates this query:

SELECT 
    t73018397.code, 
    max(t73018397.value) AS mv   
  FROM t73018397 
  GROUP BY t73018397.code 
  ORDER BY mv desc
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153