Sorry, I was not finished yet.
The outline is a follows: I want to join three relations unary o
nullary o squared, where squared = binary^2, and find out which
elements n are in relation n (unary o nullary o squared) fixed to some
nullary element fixed.
Here are the details:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData("...")
metadata.bind.echo=True
nullary = Table("nullary", metadata,
Column("id", Integer, primary_key=True),
Column("data", String))
nullary.create()
nullary.insert().execute([{"data": 1}, {"data": 2}])
class Nullary(object):
pass
mapper(Nullary, nullary)
unary = Table("unary", metadata,
Column("id", Integer, primary_key=True),
Column("fk", Integer, ForeignKey("nullary.id")))
unary.create()
unary.insert().execute([{"fk": 1}, {"fk": 2}])
class Unary(object):
pass
mapper(Unary, unary,
properties={"ref": relation(Nullary, uselist=False,
backref="inv")})
binary = Table("binary", metadata,
Column("id", Integer, primary_key=True),
Column("fk1", Integer, ForeignKey("nullary.id")),
Column("fk2", Integer, ForeignKey("nullary.id")))
binary.create()
binary.insert().execute([{"fk1": 1, "fk2": 1}, {"fk1": 2, "fk2": 2}])
class Binary(object):
pass
mapper(Binary, binary,
properties={"ref1": relation(Nullary, uselist=False,
primaryjoin=binary.c.fk1 ==
nullary.c.id),
"ref2": relation(Nullary, uselist=False,
primaryjoin=binary.c.fk2 ==
nullary.c.id)})
binary_alias = binary.alias("binary_alias")
squared = select([binary.c.fk1.label("fk1"),
binary_alias.c.fk2.label("fk2"),
func.count(text("*")).label("n")],
True,
[join(binary, binary_alias,
binary.c.fk2 == binary_alias.c.fk1)])
squared = squared.group_by(binary.c.fk1,
binary_alias.c.fk2).alias("squared")
class Squared(object):
pass
mapper(Squared, squared, primary_key=(squared.c.fk1, squared.c.fk2),
properties={"ref1": relation(Nullary),
"ref2": relation(Nullary)})
session = create_session()
fixed = session.query(Nullary).get(1)
query = session.query(Squared).filter_by(ref2=fixed)
query = query.add_entity(Nullary).join("ref1")
count = query.count()
query = query.add_entity(Unary).join("inv", from_joinpoint=True)
query = query.order_by([squared.c.n])
print [n for s, n, u in query]
The traceback:
2007-11-06 15:57:34,122 INFO sqlalchemy.engine.base.Engine.0x..94
ROLLBACK
Traceback (most recent call last):
File "wrong_select4.py", line 117, in ?
print [n for s, n, u in query]
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/orm/query.py", line
630, in __iter__
return self._execute_and_instances(context)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/orm/query.py", line
633, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/orm/session.py", line
527, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {}, **kwargs)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/engine/base.py", line
781, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/engine/base.py", line
832, in _execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/engine/base.py", line
844, in _execute_compiled
self.__execute_raw(context)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/engine/base.py", line
856, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/home/barthelmannk/local/lib/python.new/
SQLAlchemy-0.4.1dev_r3733-py2.4.egg/sqlalchemy/engine/base.py", line
872, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) invalid
reference to FROM-clause entry for table "nullary"
HINT: There is an entry for table "nullary", but it cannot be
referenced from this part of the query.
'SELECT squared.fk1 AS squared_fk1, squared.fk2 AS squared_fk2,
squared.n AS squared_n, nullary.id AS nullary_id, nullary.data AS
nullary_data, unary.id AS unary_id, unary.fk AS unary_fk \nFROM
nullary, (SELECT "binary".fk1 AS fk1, binary_alias.fk2 AS fk2,
count(*) AS n \nFROM "binary" JOIN "binary" AS binary_alias ON
"binary".fk2 = binary_alias.fk1 \nWHERE True GROUP BY "binary".fk1,
binary_alias.fk2) AS squared JOIN unary ON nullary.id = unary.fk
\nWHERE %(param_1)s = squared.fk2 AND %(param_1_1)s = squared.fk1
ORDER BY squared.n' {'param_1_1': 1, 'param_1': 1}
Best regards
Klaus
On 6 Nov., 15:40, klaus <[EMAIL PROTECTED]> wrote:
> Hi all,
> when I try to build up a complicated query like this:
>
> query = session.query(Class)
> query = query.filter_by(...).add_entity(...).join(...)
> count = query.count()
> query = query.add_entity(...).join(...).order_by(...)
> print query.all()
>
> the last statement fails due to a broken SELECT. The error disappears
> if I remove the line with the query.count().
>
> The following is an example to reproduce the behavior. I'm sorry that
> it is so complicated, but a certain complexity seems to be necessary
> to trigger the bug.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
