[sqlalchemy] Re: Renaming Columns and Union - possible bug
On Jan 3, 2:44 am, Michael Bayer mike...@zzzcomputing.com wrote:
I've made fixes to corresponding_column() to resolve this issue, and
in the process uncovered (and also solved) a whole class of problems
in that method which was, to my great surprise, also impacting some
very nested Query objects that *don't* use union() or anything
compound. This change is in trunk r5578.
Brilliant, this has done the trick.
Incidentally, I had to hack the ids in order to get the O-R mapper to
pick up two rows with the same id:
u = union(
MyE.__table__.select(),
select([MyE.id - 100, MyE.f_2.label('f_1'),
MyE.f_1.label('f_2'), MyE.date])
)
Ideally I'd like to select NULL/None for the id in the second SELECT
as the objects do not correspond to any rows which exist in the DB
(they are readonly).
Thanks again,
Eoghan
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Renaming Columns and Union - possible bug
On Jan 3, 2009, at 7:31 PM, Eoghan Murray wrote:
On Jan 3, 2:44 am, Michael Bayer mike...@zzzcomputing.com wrote:
I've made fixes to corresponding_column() to resolve this issue, and
in the process uncovered (and also solved) a whole class of problems
in that method which was, to my great surprise, also impacting some
very nested Query objects that *don't* use union() or anything
compound. This change is in trunk r5578.
Brilliant, this has done the trick.
Incidentally, I had to hack the ids in order to get the O-R mapper to
pick up two rows with the same id:
u = union(
MyE.__table__.select(),
select([MyE.id - 100, MyE.f_2.label('f_1'),
MyE.f_1.label('f_2'), MyE.date])
)
Ideally I'd like to select NULL/None for the id in the second SELECT
as the objects do not correspond to any rows which exist in the DB
(they are readonly).
the ORM can't represent a mapped instance with a totally null primary
key, or two of the same instance with the same primary key. so if you
wanted those rows you'd have to select individual columns.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Renaming Columns and Union - possible bug
On Dec 22 2008, 7:10 pm, Eoghan Murray eoghanomur...@gmail.com
wrote:
On Dec 22, 4:16 pm, Gaetan de Menten gdemen...@gmail.com wrote:
I'm not sure what you are trying to do, but MyE.f_1 and MyE.f_2 are
not column objects. f_1_id and f_2_id are.
Sorry, I edited down my example from a bigger version and made a
mistake — the following entiity class def might make better sense:
class MyE(Entity):
id = Field(Integer, primary_key=True)
foreign_1 = ManyToOne('OtherE', colname='f_1')
foreign_2 = ManyToOne('OtherE', colname='f_2')
date = Field(Date)
I've stepped through the source and it seems that the adapter is
mapping the second foreign column to the first foreign column
(sqlalchemy/orm/query.py 1736)
adapter.columns
{
Column('f_1', String(length=50, convert_unicode=False,
assert_unicode=None), ForeignKey('othere.id'), table=mye,
nullable=False): Column('f_1', String(length=50,
convert_unicode=False, assert_unicode=None), ForeignKey('othere.id'),
table=%(156419884 anon)s, nullable=False),
Column('id', Integer(), table=mye, primary_key=True,
nullable=False): Column('id', Integer(), table=%(156419884 anon)s,
primary_key=True, nullable=False),
Column('f_2', String(length=50, convert_unicode=False,
assert_unicode=None), ForeignKey('othere.id'), table=mye,
nullable=False): Column('f_1', String(length=50,
convert_unicode=False, assert_unicode=None), ForeignKey('othere.id'),
table=%(156419884 anon)s, nullable=False)
}
I know this isn't much to go on, but anyone have any ideas?
Thanks,
Eoghan
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Renaming Columns and Union - possible bug
On Jan 2, 10:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
It would help if you could illustrate with accurate code - the UNION
above does not have consistent numbers of columns in each select() and
I think what you're trying to do is reverse f_1 and f_2 in the second
select() statement which is also not illustrated above.
I am able to reproduce your results with the script at the end of this
email.
Thanks for taking the time to create a coherent example that works
standalone.
I didn't know how to do this as I'm only familiar with using
SQLAlchemy via Elixir and TurboGears.
Its a fun little problem for which I haven't thought of a solution as
of yet. your union delivers both f_1 and f_2 from its second and
third columns.
What I am trying to do is select columns labelled 'f_1' and 'f_2' in
the 2nd and 3rd columns, i.e.
u = union(
MyE.__table__.select(),
select([MyE.id, MyE.f_2.label('f_1'), MyE.f_1.label
('f_2'), MyE.date])
)
This gives:
print u.corresponding_column(MyE.__table__.c.f_2)
f_1
Which is the cause of the problem?
The SQL for this also leaves out the f_1 column in the outer select:
print sess.query(MyE).select_from(u).order_by(MyE.date).all()
SELECT anon_1.id AS anon_1_id, anon_1.f_1 AS anon_1_f_1, anon_1.date
AS anon_1_date
FROM (SELECT mine.id AS id, mine.f_1 AS f_1, mine.f_2 AS f_2,
mine.date AS date
FROM mine UNION SELECT mine.id AS id, mine.f_2 AS f_1, mine.f_1 AS
f_2, mine.date AS date
FROM mine) AS anon_1 ORDER BY anon_1.date
Strangely, if I reorder the columns in the second half of the union:
u = union(
MyE.__table__.select(),
select([MyE.id, MyE.f_1.label('f_2'), MyE.f_2.label
('f_1'), MyE.date])
)
I get the correct:
print u.corresponding_column(MyE.__table__.c.f_2)
f_2
And the correct outer SELECT:
SELECT anon_1.id AS anon_1_id, anon_1.f_1 AS anon_1_f_1, anon_1.f_2 AS
anon_1_f_2, anon_1.date AS anon_1_date
But the inner UNION doesn't work (postgres at least), as it seems to
compute the union based on column position, rather than column label.
If you ask the union, hey which one of your columns
corresponds to table.c.f_1 , the answer is ambiguous since its both.
I could change corresponding_column to raise an error for this
ambiguity, but that wouldnt fix your problem. The thing you expect
here is that the columns in the *first* select of the union should
take precedence over the subsequent ones. A naive change in that
regard breaks tests which do in fact place columns in subsequent
selects which need to be counted so something else will have to be
figured out. probably some extra guesswork in
CompoundSelect._populate_column_collection to detect this kind of thing.
At the moment I'm replacing the outer SELECT with a SELECT * which
gives the intended results.
Why do all the really intricate problems seem to come from the elixir
camp ? :)
Now I feel subversive ;)
Eoghan
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Renaming Columns and Union - possible bug
On Mon, Dec 22, 2008 at 17:06, Eoghan Murray eoghanomur...@gmail.com wrote:
The following example uses an elixir class:
class MyE(Entity):
id = Field(Integer, primary_key=True)
f_1 = ManyToOne('OtherE')
f_2 = ManyToOne('OtherE')
date = Field(Date)
MyE.query.select_from(union(MyE.table.select(),
select([MyE.id, MyE.f_1.label('f_2'),
MyE.f_2.label('f_1')]))).\
order_by([MyE.date])
I'm not sure what you are trying to do, but MyE.f_1 and MyE.f_2 are
not column objects. f_1_id and f_2_id are.
The following *might* work:
MyE.query.select_from(
union(MyE.table.select(),
select([MyE.id, MyE.f_1_id.label('f_2_id'),
MyE.f_2_id.label('f_1_id')]))).\
order_by([MyE.date])
This produces the following SQL:
SELECT anon_1.id AS anon_1_id, anon_1.f_1 AS anon_1_f_1, anon_1.date
AS anon_1_date
FROM (
SELECT mye.id AS id, mye.f_1 AS f_1, mye.f_2 AS f_2, mye.date AS date
FROM mye UNION ALL
SELECT mye.id AS id, mye.f_2 AS f_1, mye.f_1 AS f_2, mye.date AS date
FROM mye) AS anon_1 ORDER BY anon_1.date
Which strangely omits the anon_1_f_2 column and so doesn't populate
the mapper correctly (f_2 is populated with the contents of f_1)
Is this a bug?
I've upgraded SQLAlchemy to 0.5.0rc4 and also elixir to 0.6.1 but it
still appears.
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
