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);
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)
>>> 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.
