referencing a `label` in a group by clause ?

[Jonathan Vanasco]

I want to generate this SQL:

        SELECT 

            SUBSTR(domain_name, 1, 1) as first_letter, 

            COUNT(domain_name) as counted

        FROM domain

        GROUP BY first_letter;

The closest I can get to it is this:

        dbSession.query(

            sqlalchemy.func.count( model.Domain.domain_name ).label('counted'),

            sqlalchemy.func.substr( model.Domain.domain_name, 1, 1 ).label('first_letter'),

        ).group_by(

            sqlalchemy.func.substr( model.Domain.domain_name, 1, 1 )

        )

I tried using this as a the group by:

    sqlalchemy.sql.expression.literal('first_letter')

however postgres doesn't like the group by coming in as a bind param.

is there another way to construct the intended query, or do I have to use 2 substring operations (or a subquery)?

[Michael Bayer]

you should be able to say:

group_by(“first_letter”)

should go right in under that label.

works for order by at least.

--
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/d/optout.

[Jonathan Vanasco]

On Wednesday, July 23, 2014 7:50:48 PM UTC-4, Michael Bayer wrote:

you should be able to say:

group_by(“first_letter”)

should go right in under that label.

works for order by at least.

i can't believe i didn't try that first.  works as promised.  thanks!

 

[Jonathan Vanasco]

Revisiting this question, with coalesce.  The target sql is:

        SELECT 

             letter

            ,COALESCE(cache_counted, 0) AS counted

        FROM letters

        ORDER BY counted DESC

The closest I can get to it is this:

        dbSession.query(

            sqlalchemy.func.count( model.Letters.letter ),

            sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).label('counted'),

        ).order_by(

            sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).desc

        )

[Michael Bayer]

order_by(‘counted’) 

?

there’s a feature for 1.0 that will actually search out “counted” when you pass as a string and match it up too. 

[Jonathan Vanasco]

On Wednesday, July 30, 2014 3:49:15 PM UTC-4, Michael Bayer wrote:

order_by(‘counted’) 

That generates:

     ORDER BY counted

I can't figure out a way to apply `.desc()` (or specify `.asc()`) with that :

     ORDER BY counted DESC

I tried going though the docs and constructing various objects that might allow me to do something like:

    .order_by( foo('counted').desc() )

but I couldn't find the right `foo`, if any.

[Simon King]

I haven’t followed the whole thread, but can’t you just use:

.order_by(sa.desc('counted'))

http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.desc)

Simon

[Jonathan Vanasco]

THAT IS EXACTLY WHAT I NEEDED !!!!!!!

I seriously spent a very stressful hour today combing through the docs and trying to construct something that would let me apply a `.desc()` ; I never thought of looking for a desc function to apply.

Thank you so much!