combining join with alias

[Petra Clementson]

I want to do a self join on combined but it wont let me. Essensially,
I want to join two different tables, and create aliases so I can
compare one column and make sure that each item in the column is
uniqe. If there are duplicates, I want my piece of code to show the
duplicates. Combining join with alias seems like the best way to do
this because when using other methods, if I change the duplicates to a
unique name, the name that used to be a duplicate still prints. Note
that the Header table has a foreign key reference to DiskFile and the
sql version used was 0.6.5. This is the portion of code where I am
getting an error:


combined = join(DiskFile, Header)
combined_alias = aliased(combined)


ERROR:
Traceback (most recent call last):
File "duplicatedl.py", line 32, in <module>
combined_1 = aliased(combined)

File "/opt/sqlalchemy/lib/python2.5/site-packages/sqlalchemy/orm/
util.py", line
304, in __init__
self.__target = self.__mapper.class_
AttributeError: 'SQLCompiler' object has no attribute 'class_'



Please help!

[Michael Bayer]

This could perhaps be improved in the interface; "aliased()" applies to mapped classes and mappers. join() is a Selectable expression. To alias a selectable, use its alias() method to generate an alias object:

join(A, B).alias()

I'll think about having orm.aliased() detect a selectable and return selectable.alias() in 0.7.

[Petra Clementson]

Thanks for your prompt reply! This looks like something I haven't tried but would probably work. I'll have to try it on Monday and let you know how it goes.

 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Petra Clementson]

Okay, so I implemented "join(A, B).alias()", but I can't seem to access the columns of the aliased table. Here is my code:

session = sessionfactory()

combined_1 = join(DiskFile, Header).alias()

combined_2 = join(DiskFile, Header).alias()

query = session.query(header).select_from(combined_1, combined_2).filter(DiskFile.canonical == True).filter(Header.datalab != 'none').filter(combined_1.datalab == combined_2.datalab)

And I get:

AttributeError: 'Alias' object has no attribute 'datalab'

Any ideas?

[Michael Bayer]

On Jan 10, 2011, at 3:47 PM, Petra Clementson wrote:

> Okay, so I implemented "join(A, B).alias()", but I can't seem to access the columns of the aliased table. Here is my code:
>
>
> session = sessionfactory()
>
> combined_1 = join(DiskFile, Header).alias()
> combined_2 = join(DiskFile, Header).alias()
>
> query = session.query(header).select_from(combined_1, combined_2).filter(DiskFile.canonical == True).filter(Header.datalab != 'none').filter(combined_1.datalab == combined_2.datalab)
>
>
> And I get:
> AttributeError: 'Alias' object has no attribute 'datalab'
>
>

When you work with join() and alias() objects, you start using the SQL expression language. The objects are known as "selectables" and their namespace of column attributes is available via the .c. attribute.

It's advisable to get a feel for SQL expression constructs via the tutorial: http://www.sqlalchemy.org/docs/core/tutorial.html . You can probably skim the first half of it then start looking at the sections on "Aliases" and "Joins" more closely.