Getting all distinct attribute values of a certain class attribute

[Christoph Zwerschke]

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

[Michael Bayer]

On Jan 9, 2008, at 6:57 AM, Christoph Zwerschke wrote:

> Now I want to get all different cities of my customers, starting with
> 'A', ordered alphabetically. In plain SQL this would be:

...

> But this does not work, since the select statement does not return the
> attribute set of the Customer class.

these two statements seem to contradict; do you want a collection of
strings representing each "city" ? or do you want Customer objects ?
or the full set of columns represented by a Customer ?

The basic request to get cities as strings by themselves should be
easy enough as:

select
([Customer
.city]).distinct().order_by(Customer.city).execute().fetchall()

>
> 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%'
>

select
([Customer
.city
]).distinct
().order_by(Customer.city).where(func.lower(Customer.city).like("a
%")).execute().fetchall()

we will eventually have a database-neutral "ilike" comparator but that
hasn't been implemented yet.

[Christoph Zwerschke]

Michael Bayer wrote:
> The basic request to get cities as strings by themselves should be
> easy enough as:
>
> select([Customer.city]).distinct().order_by(
> Customer.city).execute().fetchall()

That's exactly what I wanted - and yes, that's really easy.

> we will eventually have a database-neutral "ilike" comparator but that
> hasn't been implemented yet.

Ok, I'll use the approach with func.lower().like("a%") until then. I
noticed func.lower().startswith("a") is also possible, a bit nicer.

Thanks a lot for your quick support!

-- Chris