(OperationalError) (1066, "Not unique table/alias: '...'") when selecting only from a joined table

[Oliver]

I want to have the following query in sqlalchemy:

SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS
t_objects_2 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid =
c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid
= t_objects_2.parent_id WHERE c.id = 1;

this would return a list of parent_ids whose childs match a certain
condition...


I would go like the following:
s.query(t_objects_1.tid).join((c_objects, t_objects_2.tid ==
c_objects.tid),(t_objects_1, t_objects_1.tid ==
t_objects_2.parent_id)).filter(c_objects.id == 1).all()


...but I get this error: (OperationalError) (1066, "Not unique table/
alias: 't_objects_1'")

so sqlalchemy is putting the wrong alias in the from clause:

SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS
t_objects_1 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid =
c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid
= t_objects_2.parent_id WHERE c.id = %s'

how can I bring sqlalchemy to use 't_objects_2' instead of
't_objects_1'?


Due to project constraints I have to use sqlalchemy==0.5.6

if maybe this is a known bug of 0.5.6 please let me now

[Michael Bayer]

On Jun 28, 2011, at 12:26 PM, Oliver wrote:

I want to have the following query in sqlalchemy:

SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS
t_objects_2 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid =
c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid
= t_objects_2.parent_id WHERE c.id = 1;

this would return a list of parent_ids whose childs match a certain
condition...


I would go like the following:
s.query(t_objects_1.tid).join((c_objects, t_objects_2.tid ==
c_objects.tid),(t_objects_1, t_objects_1.tid ==
t_objects_2.parent_id)).filter(c_objects.id == 1).all()


...but I get this error: (OperationalError) (1066, "Not unique table/
alias: 't_objects_1'")

so sqlalchemy is putting the wrong alias in the from clause:

SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS
t_objects_1 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid =
c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid
= t_objects_2.parent_id WHERE c.id = %s'

Its going to start the FROM chain from "t_objects_1" since that's what's in the columns clause, at which point you then join *to* c_objects.   query.select_from(t_objects_2).join()... will start the FROM clause instead from t_objects_2.

Due to project constraints I have to use sqlalchemy==0.5.6

if maybe this is a known bug of 0.5.6 please let me now

ah.   It might not work in a version that old.   Try the select_from(table) approach first, and if 0.5 isn't handling it ,  you should create your joins using the sqlalchemy.orm.join() function, then place the fully constructed join() construct into select_from() - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins     

(Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins)

[King Simon-NFHD78]

Michael Bayer wrote:
> - I am loathe to reference the 0.5 docs as people keep finding them
> and thinking they are current, but an example of this is at
> http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-
> joins
>
> (Note to people reading this: these are the *OLD DOCS* regarding 0.5;
> for current join usage please see
> http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins)
>

In the "Quick Select" links at the top of the 0.5 docs, there's no link
to the 0.7 docs. Is this deliberate or has it just been overlooked?

Would it be worth putting some sort of big banner at the top of the
older docs pointing out that they are old?

FWIW, I *really* appreciate that you keep the old versions of the docs
around - I have an application that I maintain using SA 0.3, and just
last week I needed to refer back to the docs. I hope they never go away!
(I know they still exist in the repository, but the website is so
convenient...)

Cheers,

Simon

[Michael Bayer]

On Jun 28, 2011, at 1:31 PM, King Simon-NFHD78 wrote:

> Michael Bayer wrote:
>> - I am loathe to reference the 0.5 docs as people keep finding them
>> and thinking they are current, but an example of this is at
>> http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-
>> joins
>>
>> (Note to people reading this: these are the *OLD DOCS* regarding 0.5;
>> for current join usage please see
>> http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins)
>>
>
> In the "Quick Select" links at the top of the 0.5 docs, there's no link
> to the 0.7 docs. Is this deliberate or has it just been overlooked?

its a buglike thing. I'd have to commit new templates to the 0.5 repo.

>
> Would it be worth putting some sort of big banner at the top of the
> older docs pointing out that they are old?

yeah Ive considered various things..

>
> FWIW, I *really* appreciate that you keep the old versions of the docs
> around - I have an application that I maintain using SA 0.3, and just
> last week I needed to refer back to the docs. I hope they never go away!
> (I know they still exist in the repository, but the website is so
> convenient...)

heh....0.3 and 0.4 aren't up there ! I took them down a while ago. They don't work well with the build and I'm mortified by the prospect that someone might think they are current....

[King Simon-NFHD78]

Michael Bayer wrote

I could have sworn I managed to get to them by tweaking the URL, but
according to my browser history it never happened. I must have dreamt it
(note to self: stop dreaming about SQLAlchemy)

In that case, thanks for nothing ;-)

Cheers,

Simon