Assume I have a class Customers mapped with a contextual mapper to a
table "customers" and assume the customers have an attribute city.
Now I want to get all different cities of my customers, starting with
'A', ordered alphabetically. In plain SQL this would be:
select distict city from customers where city like 'A%' order by 1
How would I do this with SQLAlchemy, ideally without knowing the name of
the table, i.e. referring to the class name only? The following works,
but is not very efficient because it loads all the customers:
sorted(set(cust.city for cust in Customer.query.filter(
Customer.city.startswith('A%'))))
So instead, I thought of something like the following:
Customer.query.from_statement(select([Customer.city],
Customer.city.startswith('A%'), distinct=True, order_by=[1])).all()
But this does not work, since the select statement does not return the
attribute set of the Customer class.
However, I feel something along these lines should be possible.
Is there something I'm missing?
Bonus question: How can I make the comparison case insensitive, i.e.
create a where clause like that:
... where city ilike 'A%' ...
or
... where lower(city) like 'a%'
Any help appreciated.
-- Christoph
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
