Hi,
I am having problems with JOINs, which I can circumvent by adding more
arguments to session.query() and passing the join-clause using .filter().
However, that workaround is not only unintuitive, but also really slow
(disclaimer: since the join does not work yet, I cannot claim that that one
would /not/ suffer from bad performance), and I am trying to understand what
the problem with the original join-approach is.
I will *try* (but probably fail) to give a small, but not too incomplete
description of what I am trying to do.
Background story: I am using an SQLite database to store results from running
algorithms on test data, in order to e.g. compare the output of different
algorithm versions (or parameterizations) on a set of test cases.
The specific code I am having problems with is composing a 'commonResults'
Query that queries all pairs of TestResult entities that represent results for
two chosen algorithm versions on the same test cases (an indirection via a
TestingSession entity is necessary in order to get to the algorithm
reference):
# I need to refer to two different TestResult instances
# (for two algorithm versions algorithm1 and algorithm2):
results1 = aliased(erm.TestResult)
results2 = aliased(erm.TestResult)
# rel_algorithm is part of the TestingSession, so I need to
# follow (join via) TestResult.rel_session first:
session1 = aliased(erm.TestingSession)
session2 = aliased(erm.TestingSession)
commonResults = (session.query(results1.id, results2.id)
.filter(results1.rel_testCase_id == results2.rel_testCase_id)
.join(session1, results1.rel_session_id == session1.id)
.join(session2, results2.rel_session_id == session2.id)
.filter(session1.rel_algorithm == algorithm1)
.filter(session2.rel_algorithm == algorithm2)
.order_by(results1.rel_testCase_id))
This Query alone works well, but if I now try to expand on it, e.g. like this:
commonResults.join(erm.OutputParameterValue,
erm.OutputParameterValue.rel_testResult_id == results1.id)
the result is an invalid query, in which results2 is missing in the FROM
clause and thus leads to SQL errors like
no such column: TestResult_2.id
or
no such column: TestResult_2.rel_testCase_id
The original 'commonResults' query leads to this SQL statement:
SELECT
"TestResult_1".id AS "TestResult_1_id",
"TestResult_2".id AS "TestResult_2_id"
FROM
"TestResult" AS "TestResult_2",
"TestResult" AS "TestResult_1" JOIN
"TestingSession" AS "TestingSession_1" ON "TestResult_1".rel_session_id =
"TestingSession_1".id JOIN
"TestingSession" AS "TestingSession_2" ON "TestResult_2".rel_session_id =
"TestingSession_2".id
WHERE
"TestResult_1"."rel_testCase_id" = "TestResult_2"."rel_testCase_id" AND
"TestingSession_1".rel_algorithm_id = ? AND
"TestingSession_2".rel_algorithm_id = ?
ORDER BY
"TestResult_1"."rel_testCase_id"
After the .join(erm.OutputParameterValue), I am seeing this:
SELECT
"TestResult_1".id AS "TestResult_1_id",
"TestResult_2".id AS "TestResult_2_id"
FROM
"TestResult" AS "TestResult_1" JOIN
"TestingSession" AS "TestingSession_1" ON "TestResult_1".rel_session_id =
"TestingSession_1".id JOIN
"TestingSession" AS "TestingSession_2" ON "TestResult_2".rel_session_id =
"TestingSession_2".id JOIN
"OutputParameterValue" ON "OutputParameterValue"."rel_testResult_id" =
"TestResult_2".id
WHERE
"TestResult_1"."rel_testCase_id" = "TestResult_2"."rel_testCase_id" AND
"TestingSession_1".rel_algorithm_id = ? AND
"TestingSession_2".rel_algorithm_id = ?
ORDER BY
"TestResult_1"."rel_testCase_id"
It is more or less obvious that the TestResult_2 table in
SELECT … FROM "TestResult" AS "TestResult_2", …
got lost in the second statement.
To me, this looks like a bug (I am using SA 0.7.9), but maybe I am doing
something wrong/stupid/naive/unexpected? I am also using elixir 0.7.1, but I
think (hope?) that that does not play a role here (which might be a dangerous
assumption?).
Thanks for reading so far, I am looking forward to your comments!
Hans
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
