[email protected] 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]. > 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. -- 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.
