[sqlalchemy] Re: order_by with explicit column name messes up subqueryload
BTW, aliased() and alias() don't work on a label() either. I tried passing the label object straight into the order_by() as well to no avail. I'm all out of ideas. On Jan 9, 3:47 am, Yuen Ho Wong wyue...@gmail.com wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? Thanks in advance! -- 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: order_by with explicit column name messes up subqueryload
Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label(distance, some_complicated_geoalchemy_function_call(columns...)) q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by(distance).offset(0).limit(20).all() On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? any chance you can save me some time and attach a complete, succinct .py example here ? subqueryload removes the ORDER BY from the query, provided LIMIT/OFFSET aren't present, unconditionally. It doesn't care that it's a string or not. http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/orm/strategies -- 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: order_by with explicit column name messes up subqueryload
Here are the models:
class Merchant(Base):
__tablename__ = merchant
id = Column(Integer, autoincrement=True, primary_key=True)
location = GeometryColumn(Point, nullable=False)
class SearchOption(Base):
__tablename__ = searchoption
id = Column(Integer, autoincrement=True, primary_key=True)
parent_id = Column(Integer, ForeignKey(id,
onupdate=CASCADE,
ondelete=CASCADE))
parent = relationship(SearchOption, uselist=False,
remote_side=[id],
backref=backref(children))
discriminator = Column(type, Enum(style, origin, price,
food,
flavor, occasion,
variety, mood,
name=searchoptiontype))
__mapper_args__ = {polymorphic_on: discriminator}
displayname = Column(Unicode(64), nullable=False)
class StyleOption(SearchOption):
__mapper_args__ = {polymorphic_identity: style}
class OriginOption(SearchOption):
__mapper_args__ = {polymorphic_identity: origin}
class FoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: food}
class FlavorOption(SearchOption):
__mapper_args__ = {polymorphic_identity: flavor}
class OccasionOption(SearchOption):
__mapper_args__ = {polymorphic_identity: occasion}
class VarietyOption(SearchOption):
__mapper_args__ = {polymorphic_identity: variety}
class MoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: mood}
product_searchoption_table = Table(product_searchoption, metadata,
Column(product_id,
Integer,
ForeignKey(product.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True),
Column(searchoption_id,
Integer,
ForeignKey(searchoption.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True))
class Product(Base): # tune full-text search
__tablename__ = product
id = Column(Integer, autoincrement=True, primary_key=True)
origin = relationship(OriginOption, uselist=False,
secondary=product_searchoption_table)
style = relationship(StyleOption, uselist=False,
secondary=product_searchoption_table)
pricerange = relationship(PriceOption, uselist=False,
secondary=product_searchoption_table)
foods = relationship(FoodOption,
secondary=product_searchoption_table)
flavors = relationship(FlavorOption,
secondary=product_searchoption_table)
occasions = relationship(OccasionOption,
secondary=product_searchoption_table)
moods = relationship(MoodOption,
secondary=product_searchoption_table)
varieties = relationship(VarietyOption,
secondary=product_searchoption_table)
merchant_id = Column(Integer, ForeignKey(merchant.id,
onupdate=CASCADE,
ondelete=CASCADE),
nullable=False,)
merchant = relationship(Merchant, uselist=False,
backref=backref(products))
Hope this helps.
On Jan 9, 4:16 am, Yuen Ho Wong wyue...@gmail.com wrote:
Except that LIMIT and OFFSET are present in my query, gnarly isn't
it ? :P
d = label(distance,
some_complicated_geoalchemy_function_call(columns...))
q = session.query(Product, Merchant.location, d)\
.join(Merchant, Product.merchant_id == Merchant.id)\
.filter(Product.numinstock 0)\
.options(subqueryload_all(Product.origin, Product.style,
Product.foods, Product.flavors, Product.occasions, Product.moods,
Product.varieties))
q = q.order_by(distance).offset(0).limit(20).all()
On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote:
Hi,
I have a rather complicated problem and I was wondering if you guys
could help.
So I have a query, session.query(Product, Merchant, d), where Product
is 1-to-many with Merchant, and d is the distance from some lat long.
d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy
function calls.
Product has a number of collections in which I would like to load
using subqueryload_all() as well, and the result is ordered by
distance as in order_by(distance), where distance is the name of
the label d.
My problem is, since I'm supplying the Query object with an explicit
order_by() name, when I use subqueryload(), the order_by() name is put
into the subquery as is, because SQLAlchemy doesn't know any better
with a plain string.
Re: [sqlalchemy] Re: order_by with explicit column name messes up subqueryload
On Jan 8, 2012, at 3:16 PM, Yuen Ho Wong wrote: Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label(distance, some_complicated_geoalchemy_function_call(columns...)) q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by(distance).offset(0).limit(20).all() LIMIT and OFFSET require an ORDER BY. Ordering is non-deterministic without ORDER BY, so with LIMIT/OFFSET it means your results are non-deterministic as well. In fact you can't even use LIMIT/OFFSET on some databases without ORDER BY present. Without being able to run this it would appear you just need to order_by(d) here. But the distance label should also be rendered into the subqueryload, should just work that way too.Or try order_by(func.some_complex_geoalchemy_function(...)). You can also roll the subqueryload manually (a recipe is at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading ). If you have further problems please produce a .py file that actually runs - what we need here is just print q to print the SQL generated by the query, doesn't need to actually generate tables. You can omit all the extraneous model objects and column too. -- 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: order_by with property of related table
Hi, you need to join explicitly on A.b: SESSION.query(A).join(A.b).order_by(B.name) Full example: http://pastebin.com/uMqEa6Cr Regards, - Gulli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1KPEOTrno04J. 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: order_by(datetime)
Hi Michael, Thanks for your quick reply. I understand. I am using SQLite as the engine. I have not found the functions needed in the doc. I will leave this sort option open and stick with SQlite for a while. Frans Op 1/10/2011 4:03 PM, Michael Bayer schreef: you'd need to use SQL functions that break the datetime into its component parts, and order by them. such as: order_by(func.datepart(MONTH, my_date_col), func.datepart(DAY, my_date_col), func.datepart(YEAR, my_date_col)) datepart routines vary by database backend with very little cross compatibility - you'd have to consult the documentation for your database on the recommended way to break dates up into components. On Jan 10, 2011, at 5:35 AM, F.A.Pinkse wrote: Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: order_by on a relationship() ?
forget it, I missed this in the docs :
Note that when using joined eager loaders with relationships, the
tables used by the eager load’s join are anonymously aliased. You can
only order by these columns if you specify it at the relationship()
level. To control ordering at the query level based on a related table,
you join() to that relationship, then order by it
On 06/23/2010 11:16, Julien Cigar wrote:
Hello all,
always with the following:
orm.mapper(Human, table.human)
orm.mapper(Content, table.content,
polymorphic_on = table.content.c.content_type_id,
properties = {
'owner' : orm.relationship(
Human,
lazy = 'joined',
innerjoin = True,
)
}
)
Is there a way to order_by on the 'owner' property of the Content mapper
directly without having to join the related class (Human in this case)
again ?
I thought something like :
Content.query.filter(Content.container_id==789).\
order_by(Content.owner.login)
but it doesn't work : AttributeError: Neither 'InstrumentedAttribute'
object nor 'Comparator' object has an attribute 'login'
It works with
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
but then Human is joined two times, one for the explicit .join() and one
for the 'owner' relationship (... JOIN human ON human.id =
content.owner_id JOIN human AS human_1 ON human_1.id = content.owner_id
...)
Also, if I put lazy = 'select' in place of lazy = 'joined' for the above
relationship() and that I do :
Content.query.join(Human).\
filter(Content.container_id==789).\
order_by(Human.login)
and then if I access the 'owner' property of one of those selected
objects SQLAlchemy issues a SELECT again, .. why ? Is there a way to
tell SQLAlchemy that the .join(Human) of the above query is in fact the
'owner' property of Content .. ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
attachment: jcigar.vcf
[sqlalchemy] Re: order_by() and count()
I got it:
from sqlalchemy.sql import func
stmt = session.query(Prod.store_id,
func.count('*').label('prod_count')).group_by(Prod.store_id).subquery()
obj_q = session.query(Store, stmt.c.prod_count).outerjoin(
(stmt,
Store.id==stmt.c.store_id)).order_by(stmt.c.prod_count.desc())
Voil`a!
Cheers,
Sandor
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: order_by and group_by won't work as I want.
On Friday 19 December 2008 19:53:03 有末 清華 wrote: Hi. Well, I want to output the HTML code from database. And the HTML code should be order by 'cost' and group_by 'category' The database table is like below. --- ID CategoryNameCost 0 foodbanana $1 1 foodapple $2 2 bookfoo $15 3 bookfoobar $10 4 something qwert $5 5 something poiuy $7 6 anythingasdf$8 --- this is order_by (category,cost), no grouping. grouping will be: food... book... something ... anything... one per group --~--~-~--~~~---~--~~ 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: Order_by
I found the solution using Joins. Thanks anyways. This is it: query = session.query(A).select_from(join(A, B)).order_by(B.some_attr) cheers --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related table
On Dec 5, 2007, at 1:56 PM, David Gardner wrote:
I have three tables a(a query of a really), b, c
a has a 1-many relationship with b
c has a 1-many relationship with b
What I would like to do is in my mapper for table c, is sort the order
of rows from b by a.name.
I don't know how to do this or if it is possible. What I have looks
like:
sql_a = select([table_a], table_a.c.col1='some
value').alias('a_query')
mapper(B, b_table, properties = {
'A' : relation(sql_a, lazy=False,
primaryjoin=(sql_a.c.id==table_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[C.A.name, c_table.c.value1,
c_table.c.value2]),
})
This gets me an AttributeError: 'InstrumentedAttribute' object has no
attribute 'name'
this error is becuase C.A is not a gateway to the columns on the A
mapper.
I have also tried:
order_by=[A.name
and
order_by=[sql_a.c.name
both get me this: ProgrammingError: (ProgrammingError) missing
FROM-clause entry for table sql_a.name
this error is because the relation from C-B has no relationship to
As table at all, and it is not used in the generated query. to have
As table be a part of it in all cases, youd have to map A's column
onto B's mapper, which is fairly messy:
mapper(B, select([b_table, a.c.name], b_table.c.a_id==a.c.id))
you can try creating this mapper as a non-primary mapper and using
it for just the C-B relation:
s = select([b_table, a.c.name], b_table.c.a_id==a.c.id)
mapper(C, ctable, properties={
'bs':relation(mapper(B, s, non_primary=True), order_by=[s.c.name])
})
I havent tried the above myself so see if it works for you.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related table
Michael thanks for the help, this is how I was able to get it working.
Probably isn't the most efficient, but it works, I couldn't implement it
the way you proposed because I still need to be able to do a_row = b_row.A
-
sql_b = select([b_table, sql_a.c.name], b_table.c.a_id =
sql_a.c.id).alias('b_query')
mapper(b, sql_b, properties = {
'A' : relation(A, lazy=False, primaryjoin=(sql_a.c.id==sql_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[sql_b.c.name, sql_c.c.value1,
sql_c.c.value2]),
})
Michael Bayer wrote:
On Dec 5, 2007, at 1:56 PM, David Gardner wrote:
I have three tables a(a query of a really), b, c
a has a 1-many relationship with b
c has a 1-many relationship with b
What I would like to do is in my mapper for table c, is sort the order
of rows from b by a.name.
I don't know how to do this or if it is possible. What I have looks
like:
sql_a = select([table_a], table_a.c.col1='some
value').alias('a_query')
mapper(B, b_table, properties = {
'A' : relation(sql_a, lazy=False,
primaryjoin=(sql_a.c.id==table_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[C.A.name, c_table.c.value1,
c_table.c.value2]),
})
This gets me an AttributeError: 'InstrumentedAttribute' object has no
attribute 'name'
this error is becuase C.A is not a gateway to the columns on the A
mapper.
I have also tried:
order_by=[A.name
and
order_by=[sql_a.c.name
both get me this: ProgrammingError: (ProgrammingError) missing
FROM-clause entry for table sql_a.name
this error is because the relation from C-B has no relationship to
As table at all, and it is not used in the generated query. to have
As table be a part of it in all cases, youd have to map A's column
onto B's mapper, which is fairly messy:
mapper(B, select([b_table, a.c.name], b_table.c.a_id==a.c.id))
you can try creating this mapper as a non-primary mapper and using
it for just the C-B relation:
s = select([b_table, a.c.name], b_table.c.a_id==a.c.id)
mapper(C, ctable, properties={
'bs':relation(mapper(B, s, non_primary=True), order_by=[s.c.name])
})
I havent tried the above myself so see if it works for you.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ORDER_BY always in SELECT statements?
On Oct 1, 2007, at 8:20 AM, [EMAIL PROTECTED] wrote: Hi Michael, thanks for the help! The documentation mentions that this option over-rides the per-engine configuration but I couldn't find a create_engine option to set this. Any reference would be greatly appreciated! uhhh there is no per-engine configuration of ORDER BY, those must be some old docs youre reading. ORDER BY is only defaulted in the ORM at the mapper() and relation() level. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORDER_BY always in SELECT statements?
On Sep 28, 2007, at 3:00 PM, [EMAIL PROTECTED] wrote: Hi, While trying to debug my script I set echo=True and checked the SQL statements that are generated. I noticed that all of the SELECTs issued to the DB have the ORDER_BY clause -- even though I didn't explicitly specify order_by() nor do I care about the order. Is this normal? Is there any way to turn this off? im assuming youre talking about mappers. specify order_by=None in the mapper() (and in relation()s) to turn off the default ordering. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related object attribute?
On 4/19/07, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 19, 2007, at 9:39 AM, Gaetan de Menten wrote:
By the way, lately I've been wishing SQLAlchemy would add a column
(and possibly its table) automatically to the select clause if I do an
order by a column which is not in the currently selected columns.
I mean that you could write:
query(System).select(System.c.lastseen self.this.week,
order_by=[client.c.name])
and it would figure out that the client.c.name is not in the
selection, and would add it (or rather would add the join you describe
above).
I would agree up to the point that the table gets added, which is
what happens if you add columns to the SELECT clause of a select; the
table gets appended to the FROM clause.
Good.
but i dont agree in creating
JOIN objects automatically with no explicit specification that that's
whats desired (as usual, i am open to all sorts of explicit methods
of specifications...although we have plenty for specifying join on a
relationship at this point).
the query above I would express generatively as:
query(System).filter(System.c.lastseen self.this.week).order_by
(client.c.name).join('clients').list()
Maybe it would also be handy to have join() accept a Table as well as
a Class argument, and have it figure out the auto-thing in those
cases as well. all that is fine with me (since theres no other
meaning you could get from join(SomeOtherClass) ).
Good too. But would this work in all cases? Even if the class is
mapped several times, mapped to an arbitrary selectable and so on?
cant we just agree that adding the join automatically is an egregious
case of implicit over explicit ?
Yes, you are perfectly right, and I fully agree the autojoin part
was a pretty stupid suggestion.
considering that it is *so*
*easy* to join on a relationship now:
query(MyClass).join('somerelation').select(table2.c.name=='foo')
Yes, [and here I was going to say] but it doesn't solve my order by a
related column as a mapper option problem but then suddenly realized
that what I want is already possible.
Mapping my class against the join between system and client, than
using order_by=client.c.name should work, right? Sorry for the
trouble.
Note that it doesn't change the fact that what you agreed to above
still seem like a good idea.
--
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related object attribute?
On Apr 18, 2007, at 12:21 AM, Chris Shenton wrote: I'm using SQLAlchemy with Pylons and query my 'system' table and order by their client_id field like: from er.models import System, Vendor, Client sys = self.session.query(System).select(System.c.lastseen self.this_week, order_by= [System.c.client_id, System.c.lastseen]) it would look like: query(System).select(System.c.lastseen self.this.week, from_obj= [system_table.join(client)], order_by=[client.c.name]) or alternatively query(System).select(and_(System.c.lastseen self.this.week, system_table.c.client_id==client.c.client_id), order_by=[client.c.name]) i.e. you arent doing any kind of column selection here, you just need the cols to be in the order by. there is a way to get extra columns in the SELECT clause of a mapper query in the most recent version of SA but thats not what youre looking for here. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by
e.g. order_by = [desc(table1.mycol)] Disrupt07 napsal(a): In my table I have a column with type Boolean. When using order_by on this column I am getting the results as follows: False False True True True ... True I want them the other way round (the True first, then the False). How can I change the order? Thanks --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by
@ml Thanks --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by computed on wrong table with many-to-many
Michael Bayer wrote:
use 'sites':relation(Site, backref=backref('attachments',
order_by=attachment_table.c.name)) for now.
Ok, should I open a ticket for that ?
On Mar 30, 5:50 am, Alexandre CONRAD [EMAIL PROTECTED] wrote:
Hello,
I have a problem with order_by on a many-to-many relationship (using
assign_mapper):
--
attachment_table = Table('attachments', meta,
Column('id', Integer, primary_key=True),
Column('file', Binary, nullable=False),
Column('name', Unicode(40), nullable=False),
Column('type', Unicode(30)),
Column('date', DateTime, default=datetime.now),
Column('size', Integer, nullable=False),
Column('description', Unicode(40)),
)
attachments_has_sites = Table('attachments_has_sites', meta,
Column('id_attachment', None, ForeignKey('attachments.id'),
primary_key=True),
Column('id_site', None, ForeignKey('sites.id'), primary_key=True),
)
class Attachment(object):
pass
attachment_mapper = assign_mapper(ctx, Attachment, attachment_table,
properties={
'file':deferred(attachment_table.c.file),
'sites':relation(Site, backref=attachments,
secondary=attachments_has_sites, cascade=save-update),
},
order_by=attachment_table.c.name,
)
--
So I have a Site object where I can ask for it's attachments:
s = model.Site.get(1)
attachment_list = s.attachments
But it fires the following QUERY:
SELECT attachments.name AS attachments_name, attachments.description AS
attachments_description, attachments.date AS attachments_date,
attachments.type AS attachments_type, attachments.id AS attachments_id,
attachments.size AS attachments_size
FROM attachments, attachments_has_sites
WHERE %s = attachments_has_sites.id_site AND attachments.id =
attachments_has_sites.id_attachment ORDER BY
attachments_has_sites.id_attachment
the ORDER BY is computed against the weak table (secondary)
attachments_has_sites.
Regards,
--
Alexandre CONRAD
---
Texte inséré par Platinum 2007:
S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien suivant pour
le reclasser : http://127.0.0.1:6083/Panda?ID=pav_32965SPAM=true
---
--
Alexandre CONRAD
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by computed on wrong table with many-to-many
use 'sites':relation(Site, backref=backref('attachments',
order_by=attachment_table.c.name)) for now.
On Mar 30, 5:50 am, Alexandre CONRAD [EMAIL PROTECTED] wrote:
Hello,
I have a problem with order_by on a many-to-many relationship (using
assign_mapper):
--
attachment_table = Table('attachments', meta,
Column('id', Integer, primary_key=True),
Column('file', Binary, nullable=False),
Column('name', Unicode(40), nullable=False),
Column('type', Unicode(30)),
Column('date', DateTime, default=datetime.now),
Column('size', Integer, nullable=False),
Column('description', Unicode(40)),
)
attachments_has_sites = Table('attachments_has_sites', meta,
Column('id_attachment', None, ForeignKey('attachments.id'),
primary_key=True),
Column('id_site', None, ForeignKey('sites.id'), primary_key=True),
)
class Attachment(object):
pass
attachment_mapper = assign_mapper(ctx, Attachment, attachment_table,
properties={
'file':deferred(attachment_table.c.file),
'sites':relation(Site, backref=attachments,
secondary=attachments_has_sites, cascade=save-update),
},
order_by=attachment_table.c.name,
)
--
So I have a Site object where I can ask for it's attachments:
s = model.Site.get(1)
attachment_list = s.attachments
But it fires the following QUERY:
SELECT attachments.name AS attachments_name, attachments.description AS
attachments_description, attachments.date AS attachments_date,
attachments.type AS attachments_type, attachments.id AS attachments_id,
attachments.size AS attachments_size
FROM attachments, attachments_has_sites
WHERE %s = attachments_has_sites.id_site AND attachments.id =
attachments_has_sites.id_attachment ORDER BY
attachments_has_sites.id_attachment
the ORDER BY is computed against the weak table (secondary)
attachments_has_sites.
Regards,
--
Alexandre CONRAD
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by and alias
Manlio Perillo ha scritto:
[...]
op = (a.c.x / a.c.y).label('z')
query = select([op], order_by=[op], use_labels=True)
The solution is quite simple (and very elegant):
s = select([op], use_labels=True)
s.order_by(s.c.z)
Regards Manlio Perillo
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by and alias
Manlio Perillo wrote:
try:
conn = db.connect()
i = a.insert()
conn.execute(i, x=6, y=5)
op = (a.c.x / a.c.y).label('z')
query = select([op], order_by=[op], use_labels=True)
result = conn.execute(query).fetchall()
for r in result:
print r
finally:
metadata.drop_all()
at the point of metadata.drop_all(), conn is still checked out from the
connection pool (and also 'result' holds a reference to it, even if
'conn' was not explicit). the drop_all uses a different connection
from the pool, and therefore the table is still locked since 'conn'
just operated upon it.
two solutions: conn.close() first, or
metadata.drop_all(connectable=conn)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
