another postgresql "distinct on" question

[Jonathan Vanasco]

I can't seem to generate a "distinct on(column)" query ( .8 branch )

i'm using a connection with an ORM session , and working on an alias

this just creates a "SELECT DISTINCT column"

    sqlalchemy.select( sqlalchemy.distinct(_slurped.c.object_id).label('object_id') , _ordered.c.event_timestamp)

I looked in the source and tried this:

    sqlalchemy.select( sqlalchemy.distinct((_slurped.c.object_id,)).label('object_id') , _ordered.c.event_timestamp )

which creates:

    "SELECT DISTINCT %(param_1)s"

'param_1': (Column('object_id', Integer(), ForeignKey('object.id'), table=<slurped>, nullable=False),),

[Michael Bayer]

so I think the basic "SELECT DISTINCT ON(x) y, z, ..." is by sending an argument to the built-in distinct, not the standalone one, that is query.distinct(expr) or select.distinct(expr).

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Jonathan Vanasco]

ah ha! yes!

that is right.  

     query = select( (these,columns,) ).distinct( this.column )

this was an even tricker problem...  and I might have been executing correct queries last night without realizing it.

i just noticed that i was getting a correct query in my database, while I was seeing the wrong query on screen.

sqlalchemy didn't know that that 'core' commands I was using were for postgresql, so it rendered the query not using that dialect.  

when i actually did query the database, it was compiling with the right dialect :

    _query_EXT = dbSession.query( model.Table.data )

    _query_INT = dbSession.query( model.Table.data )

    _slurped = sqlalchemy.union( _query_EXT , _query_INT )

    _slurped = sqlalchemy.sql.expression.alias( _slurped , name='slurped')

    _deduped = sqlalchemy.select(\

            (\

                _slurped.c.object_id.label('object_id') , 

                _slurped.c.event_timestamp.label('event_timestamp') 

            ),

        )\

        .distinct( _slurped.c.object_id )\

        .order_by(\

            _slurped.c.object_id.desc() ,

            _slurped.c.event_timestamp.desc()

        )

    _deduped = sqlalchemy.sql.expression.alias( _deduped , name='deduped')

    _culled = sqlalchemy.select( (_deduped.c.object_id,)  )\

        .order_by(\

            _deduped.c.event_timestamp.desc()

        )

    _query = _culled

    # this executes a "DISTINCT ON ( slurped.object_id ) slurped.object_id , event_timestamp"

    yay = dbSession.execute( _query )

    # this renders a "DISTINCT slurped.object_id , event_timestamp"

    nay = str( _query )

    

    

[Philip Scott]

I went though the exact same process of discovery that you did Jonathan :) It does work perfectly but does not get rendered properly when printing out the queries (possibly even when I set echo=True on the connection, if I remember correctly)

--

[Michael Bayer]

On Sep 23, 2013, at 9:40 AM, Philip Scott <safetyf...@gmail.com> wrote:

I went though the exact same process of discovery that you did Jonathan :) It does work perfectly but does not get rendered properly when printing out the queries (possibly even when I set echo=True on the connection, if I remember correctly)

it will definitely show the right thing for echo=True, that's what's being sent to the database.

DISTINCT ON is postgresql specific so a string repr wont show it unless you pass a PG dialect:

from sqlalchemy import select, literal

s = select([literal(1)]).distinct(literal(2))

print s

from sqlalchemy.dialects import postgresql

print s.compile(dialect=postgresql.dialect())

[Jonathan Vanasco]

On Monday, September 23, 2013 10:31:16 AM UTC-4, Michael Bayer wrote:

it will definitely show the right thing for echo=True, that's what's being sent to the database.

yeah, the `echo` in my debug log is what showed me that postgres was getting the right data.

i was doing this to audit (pseudocode):

   query = build_query()

   results = session( query )

   raise ValueError( str(query) , results )

I naively thought that sqlalchemy was aware that the intended dialect was postgresql.    that was wrong.

drove me crazy for a day, but I learned a lot about the expression syntax and understood a bit more of the source.  no complaints, I'm better from this.

[Michael Bayer]

On Sep 23, 2013, at 10:59 AM, Jonathan Vanasco <jona...@findmeon.com> wrote:

On Monday, September 23, 2013 10:31:16 AM UTC-4, Michael Bayer wrote:

it will definitely show the right thing for echo=True, that's what's being sent to the database.

yeah, the `echo` in my debug log is what showed me that postgres was getting the right data.

i was doing this to audit (pseudocode):

   query = build_query()

   results = session( query )

   raise ValueError( str(query) , results )

I naively thought that sqlalchemy was aware that the intended dialect was postgresql.    that was wrong.

yeah it does that only if the MetaData is associated with the engine, which as you know I'm not a fan of doing.   I don't have another nice way to make this "automatic"...