Last active
November 12, 2015 19:18
-
-
Save inklesspen/0e79ded81ca17c307a54 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import sqlalchemy as sa | |
from sqlalchemy.dialects.postgresql import UUID, array | |
rec_order = sa.select( | |
# using the 'WITH ORDINALITY' suffix adds a column named 'ordinality' | |
# the first row (from the first array value) has ordinality 1, the second has 2, and so on | |
# this is useful for sorting things by the array value; simply join on the array value | |
# and order by ordinality | |
[sa.column('ordinality', sa.Integer), | |
# The actual column generated by unnest is named 'unnest' by default, | |
# so I use a lightweight column construct to specify the name and type | |
# and then apply a label to it | |
sa.column('unnest', UUID).label('rec_uuid')], | |
from_obj=sa.func.unnest(array(rec_uuids)))\ | |
.suffix_with('WITH ORDINALITY')\ | |
.alias('rec_order') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
With this construct, I can now use
rec_order.c.ordinality
andrec_order.c.rec_uuid
in my queries.