Arrays with altered elements

[Jason]

I would like to use an array comparison in a query, but with each array element being the result of a function. I do this by making the array with:

terms = [func.dmetaphone(t) for t in terms.split()]

When I use this array in a comparison I get an error "can't adapt type 'Function'" because it is passing [<sqlalchemy.sql.expression.Function at 0x1057b8310; dmetaphone>] as the array.

My full query looks like:

terms = [func.dmetaphone(t) for t in terms.split()]

metaphones = Session.query(func.dmetaphone(func.unnest(func.string_to_array(Customer.name, ' '))).label('mphone'), Customer.id).subquery() 

customers = Session.query(metaphones.columns.id) \  

                   .group_by(metaphones.columns.id) \  

                   .having(func.array_agg(metaphones.columns.mphone).op('<@')(terms))

From all the documentation I have read it this should work, but SQLAlchemy is not evaluating the func.dmetaphone call within the array. Is there a way to force it to do that?

Thanks,

Jason

[Michael Bayer]

PG ARRAY comparisons and such aren't automatically supported right now.   Such an expression needs to be coerced into a clause element of some kind.   Perhaps a ClauseList:

from sqlalchemy.sql.expression import ClauseList

terms = ClauseList(*terms)

which would produce a comma separated display.   

There is also tuple:

http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=tuple#sqlalchemy.sql.expression.tuple_

but that might not be exactly right here (sorry I haven't worked with PG arrays, I don't know the specifics offhand).

If more syntactical coercion is needed I'd build a construct using @compiles:

http://www.sqlalchemy.org/docs/core/compiler.html

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Rv742SjyArEJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Jason]

I suppose then the simplest solution is to make a function in the database that will execute a function on each element of an array and use:

.having(func.array_agg(metaphones.columns.mphone).op('<@')(func.metaphone_array(terms)))

This seems to work fine.

Thanks,

Jason