Thnak you, very much! It works: nd = sqla_aliased(Node) note_link_node = Note._descriptor.metadata.tables['note_link__node'] subq = self.session.query(nd.name).join(note_link_node, note_link_node.c.node_id == nd.id).filter(note_link_node.c.note_id == Node.id).order_by(nd.name). limit(1).correlate(Node).as_scalar() q = q.order_by(subq) Compiles into: SELECT note.id AS note_id, node.id AS node_id, node.name AS node_name FROM node JOIN note ON note.id = node.id ORDER BY (SELECT node_1.name FROM node AS node_1 JOIN note_link__node ON note_link__node.node_id = node_1.id WHERE note_link__node.note_id = node.id ORDER BY node_1.name LIMIT :param_1)
2015. január 29., csütörtök 17:48:04 UTC+1 időpontban Michael Bayer a következőt írta: > > > > [email protected] <javascript:> wrote: > > > I have a database scheme with polymorphic types: Node is the base of all > tables. (SQLAlchemy 0.7.7 with Elixir 0.7.1) > > The relevant tables are Node, Notes and note_link__node. > > The relevant attributes are id and name, both declared in the Node class > and a relationship attribute on Note called link. > > The note_link__node table holds the Note-link-Node relations. > (many-to-many) > > > > I want to query Notes ordered by the link attribute. Specifically I want > to have the linked nodes' names sorted and have the Notes sorted by the > first name. > > > > It's really simple in raw (postgre)sql. Here is a query that gets all > Notes: > > SELECT node_1.id, node_1.name > > FROM node AS node_1 INNER JOIN note AS note_1 ON node_1.id=note_1.id; > > > > > And here is a query that gets all Notes sorted by the link attribute: > > SELECT node_1.id, node_1.name > > FROM node AS node_1 INNER JOIN note AS note_1 ON node_1.id=note_1.id > > ORDER BY ( > > SELECT node.name > > FROM note_link__node INNER JOIN node ON note_link__node.node_id= > node.id WHERE note_link__node.note_id=node_1.id > > ORDER BY node.name LIMIT 1); > > why do you need to ORDER BY a correlated subquery? you should just join > out straight, node_1 -> note_1 -> note_link_node -> node_2 order by > node_2.name. will work out better in the query optimizer. I see > there’s some awkwardness with LIMIT here though, so perhaps join out to a > MIN subquery: > > SELECT … FROM node JOIN note ON … JOIN (SELECT MIN(node.name) AS > node_name, note_link_node.note_id FROM node JOIN note_link_node ON … GROUP > BY note_link_node.note_id) AS subq ON subq.note_id = note.id ORDER BY > subq.node_name > > > > > > > Here is the best I could come up with so far. Firstly q is the query I > have to apply the order_by on. > > >>> print str(q) > > SELECT note.id AS note_id, node.id AS node_id, node.name AS > node_name FROM node JOIN note ON note.id = node.id > > > > I tried to make the subquery: > > >>> nd = sqla_aliased(Node) > > >>> nt = sqla_aliased(Note) > > >>> esq = self.session.query(nd.name).join(nt.link).order_by(nd.name) > > > OK the awkwardness here is that you’d prefer nt.link to be expressed > between note_link_node and node directly, but your relationship is on note. > So take Note out of this query and join manually: > > note_link_node = metadata.tables[‘note_link_node’] # I don’t use elixir, > so wherever you get this from > subq = query(nd.name).join(note_link_node, note_link_node.c.node_id == > nd.id).limit(1).correlate(Node).as_scalar() > > q = q.order_by(subq) > > > > > > > >>> print str(esq) > > SELECT node_1.name AS node_1_name > > FROM node AS node_1, > > (SELECT node.id AS node_id, node.name AS node_name, note.id AS > note_id FROM node JOIN note ON note.id = node.id) AS anon_1 > > JOIN note_link__node AS note_link__node_1 ON anon_1.note_id = > note_link__node_1.note_id > > JOIN node ON node.id = note_link__node_1.node_id > > ORDER BY node_1.name > > > > But applying it makes a query with no sense: > > >>> q = q.order_by(esq.filter(Note.id == > > nt.id).correlate(Node).limit(1).as_scalar()) > > > >>> print str(q) > > SELECT note.id AS note_id, node.id AS node_id, node.name AS > node_name > > FROM node JOIN note ON note.id = node.id > > ORDER BY ( > > SELECT node_1.name > > FROM > > node AS node_1, > > note, > > (SELECT node.id AS node_id, node.name AS node_name, note.id AS > note_id FROM node JOIN note ON note.id = node.id) AS anon_1 > > JOIN note_link__node AS note_link__node_1 ON anon_1.note_id > = note_link__node_1.note_id > > JOIN node ON node.id = note_link__node_1.node_id > > WHERE note.id = anon_1.note_id ORDER BY node_1.name > > LIMIT :param_1) > > > > The subquery should connect to the primary with the where caluse but > instead it pulls in the tables into the subquery. I think the correlate() > should do something to fix that, but it seems I don't get it. > > > > This is probably not even the best version I made but I lost my sanity > trying to get this overly-simple thing to work. > > > > -- > > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
