[sqlalchemy] order_by(None) for joinedload subclause queries?
Hi,
We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.
We have a query that uses joinedloads to fully populate some Recipe objects
using a single query. The query creates a large SQL statement that takes 20
seconds to execute - too long. Here's the rendered SQL statement on
Pastebin. http://pastebin.com/raw.php?i=ETNDAeAa
The rendered SQL has an ORDER BY clause that Postgres explain says is the
source of 99% of the time spent on this query. This appears to come from
the relationship in the ORM model, which has an order_by clause.
However, we don't care about the order the results are returned for this
query - we only care about the order when looking at a single object. If I
remove the ORDER BY clause at the end of the rendered SQL statement, the
query executes in less than a second - perfect.
We tried using .order_by(None) on the query, but that seems to have no
effect. The ORDER BY seems to be related to the joinedloads, because if
change the joinedloads to lazyloads, they go away. But we need the
joinedloads for speed.
How can I get SQLAlchemy to omit the ORDER BY clauses?
FYI, here's the query:
missing_recipes =
cls.query(session).filter(Recipe.id.in_(missing_recipe_ids)) if
missing_recipe_ids else []
Here's an excerpt from the ORM class:
class Recipe(Base, TransactionalIdMixin, TableCacheMixin,
TableCreatedModifiedMixin):
__tablename__ = 'recipes'
authors = relationship('RecipeAuthor',
cascade=OrmCommonClass.OwnedChildCascadeOptions,
single_parent=True,
lazy='joined', order_by='RecipeAuthor.order',
backref='recipe')
scanned_photos = relationship(ScannedPhoto, backref='recipe',
order_by=ScannedPhoto.position)
utensils = relationship(CookingUtensil, secondary=lambda:
recipe_cooking_utensils_table)
utensil_labels = association_proxy('utensils', 'name')
Our query() method looks something like this (some more joinedloads
omitted):
@classmethod
def query(cls, session):
query = query.options(
joinedload(cls.ingredients).joinedload(RecipeIngredient.ingredient),
joinedload(cls.instructions),
joinedload(cls.scanned_photos),
joinedload(cls.tags),
joinedload(cls.authors),
)
cheers
adam
--
Adam Feuer
a...@cookbrite.com
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] order_by(None) for joinedload subclause queries?
Adam Feuer a...@cookbrite.com wrote:
Hi,
We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.
We have a query that uses joinedloads to fully populate some Recipe objects
using a single query. The query creates a large SQL statement that takes 20
seconds to execute - too long. Here's the rendered SQL statement on Pastebin.
http://pastebin.com/raw.php?i=ETNDAeAa
The rendered SQL has an ORDER BY clause that Postgres explain says is the
source of 99% of the time spent on this query. This appears to come from the
relationship in the ORM model, which has an order_by clause.
However, we don't care about the order the results are returned for this
query - we only care about the order when looking at a single object. If I
remove the ORDER BY clause at the end of the rendered SQL statement, the
query executes in less than a second - perfect.
We tried using .order_by(None) on the query, but that seems to have no
effect. The ORDER BY seems to be related to the joinedloads, because if
change the joinedloads to lazyloads, they go away. But we need the
joinedloads for speed.
How can I get SQLAlchemy to omit the ORDER BY clauses?
you either need to take order_by off the relationship(), probably the best idea
here if ordering isn’t important, or otherwise skip joinedload(), write out the
joins yourself and use contains_eager()
(http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager).
FYI, here's the query:
missing_recipes =
cls.query(session).filter(Recipe.id.in_(missing_recipe_ids)) if
missing_recipe_ids else []
Here's an excerpt from the ORM class:
class Recipe(Base, TransactionalIdMixin, TableCacheMixin,
TableCreatedModifiedMixin):
__tablename__ = 'recipes'
authors = relationship('RecipeAuthor',
cascade=OrmCommonClass.OwnedChildCascadeOptions,
single_parent=True,
lazy='joined', order_by='RecipeAuthor.order',
backref='recipe')
scanned_photos = relationship(ScannedPhoto, backref='recipe',
order_by=ScannedPhoto.position)
utensils = relationship(CookingUtensil, secondary=lambda:
recipe_cooking_utensils_table)
utensil_labels = association_proxy('utensils', 'name')
Our query() method looks something like this (some more joinedloads omitted):
@classmethod
def query(cls, session):
query = query.options(
joinedload(cls.ingredients).joinedload(RecipeIngredient.ingredient),
joinedload(cls.instructions),
joinedload(cls.scanned_photos),
joinedload(cls.tags),
joinedload(cls.authors),
)
cheers
adam
--
Adam Feuer
a...@cookbrite.com mailto:a...@cookbrite.com
--
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 sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] order_by(None) for joinedload subclause queries?
On Tue, Dec 30, 2014 at 4:09 PM, Michael Bayer mike...@zzzcomputing.com wrote: you either need to take order_by off the relationship(), probably the best idea here if ordering isn’t important, or otherwise skip joinedload(), write out the joins yourself and use contains_eager() ( http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager ). Michael, Thanks for the ideas, we'll try them. Not sure I can remove the order_by relationship because we need the order other places... if I remove that, I will have to add sorting in each of the other places. But it might be worth it. I think the manual joins and contains_eager may be better for us. Will that work because the manual joins and contains_eager doesn't automatically trigger adding the ORDER BY clauses? cheers adam -- Adam Feuer a...@cookbrite.com -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] order_by(None) for joinedload subclause queries?
Adam Feuer a...@cookbrite.com wrote: On Tue, Dec 30, 2014 at 4:09 PM, Michael Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: you either need to take order_by off the relationship(), probably the best idea here if ordering isn’t important, or otherwise skip joinedload(), write out the joins yourself and use contains_eager() (http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager). Michael, Thanks for the ideas, we'll try them. Not sure I can remove the order_by relationship because we need the order other places... if I remove that, I will have to add sorting in each of the other places. But it might be worth it. I think the manual joins and contains_eager may be better for us. Will that work because the manual joins and contains_eager doesn't automatically trigger adding the ORDER BY clauses? joinedload() is sort of a macro that creates the joins and other modifications to the query (such as ORDER BY the relationship), applies aliases to each of those parts so that there’s no chance of them conflicting with anything on the query, and then routes the columns from those extra FROM clauses into collections and related objects.contains_eager() does just the very last part of that.The first two parts, writing the joins and orderings and potentially aliasing them (or not), is up to you in that case, so you retain full control over how the query is rendered. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] order_by(None) for joinedload subclause queries?
On Tue, Dec 30, 2014 at 8:24 PM, Michael Bayer mike...@zzzcomputing.com wrote: The first two parts, writing the joins and orderings and potentially aliasing them (or not), is up to you in that case, so you retain full control over how the query is rendered. Michael, Cool, thanks for the explanation. That sounds like what we want. I'll try it tomorrow. cheers adam -- Adam Feuer a...@cookbrite.com -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] order_by column in adjoining table
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'),
nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'),
nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order_by column in adjoining table
you'd need to make a relationship() that explicitly joins out among all four
tables so that the .Time column is available. To accomplish that you'd
probably want to make a non primary mapper to join to, it would look something
like this:
sel = select([TrafficInput,
Traffic.Time]).select_from(join(TagCaseLink,
TrafficInput).join(TrafficDevice).join(Traffic)).alias()
m = mapper(TrafficInput, sel)
TagList.trafficInputs = relationship(m, order_by=sel.c.Time)
It's probably not how I'd go as it's too complicated. I'd either change the
schema to work more naturally (that you need to join across four tables to get
a certain ordering is a bit of a red flag) or possibly just order in memory,
assuming those relationships all tend to get loaded.
On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order_by column in adjoining table
oh that should be mapper(TrafficInput, sel, non_primary=True)
On Jul 26, 2013, at 10:41 AM, Michael Bayer mike...@zzzcomputing.com wrote:
you'd need to make a relationship() that explicitly joins out among all four
tables so that the .Time column is available. To accomplish that you'd
probably want to make a non primary mapper to join to, it would look
something like this:
sel = select([TrafficInput,
Traffic.Time]).select_from(join(TagCaseLink,
TrafficInput).join(TrafficDevice).join(Traffic)).alias()
m = mapper(TrafficInput, sel)
TagList.trafficInputs = relationship(m, order_by=sel.c.Time)
It's probably not how I'd go as it's too complicated. I'd either change the
schema to work more naturally (that you need to join across four tables to
get a certain ordering is a bit of a red flag) or possibly just order in
memory, assuming those relationships all tend to get loaded.
On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order_by hybrid property fails when specifed as string in a relationship
On Wednesday, June 19, 2013 9:47:08 PM UTC+1, Michael Bayer wrote: On Jun 19, 2013, at 4:19 PM, George Sakkis george...@gmail.comjavascript: wrote: It seems that hybrid properties are not allowed to be specified as strings for the order_by parameter of a relationship; attempting it fails with InvalidRequestError: Class ... does not have a mapped column named '...'. Is this a known limitation or a bug? Sample test case below. It's kind of a missing feature; here's a patch to make that work which will be for 0.8: http://www.sqlalchemy.org/trac/ticket/2761 Awesome, thanks for the instant response! George -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] order_by hybrid property fails when specifed as string in a relationship
It seems that hybrid properties are not allowed to be specified as strings
for the order_by parameter of a relationship; attempting it fails with
InvalidRequestError: Class ... does not have a mapped column named
'...'. Is this a known limitation or a bug? Sample test case below.
Thanks,
George
#
from sqlalchemy import Column, Integer, String, ForeignKey, case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
game_id = Column(Integer, ForeignKey('game.id'))
@hybrid_property
def fullname(self):
if self.firstname is not None:
return self.firstname + + self.lastname
else:
return self.lastname
@fullname.expression
def fullname(cls):
return case([
(cls.firstname != None, cls.firstname + + cls.lastname),
], else_=cls.lastname)
class Game(Base):
__tablename__ = 'game'
id = Column(Integer, primary_key=True)
name = Column(String(50))
if 0: # this works
users = relationship(User, order_by=User.fullname)
else: # this fails
users = relationship(User, order_by=User.fullname)
if __name__ == '__main__':
game = Game(name=tetris)
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order_by hybrid property fails when specifed as string in a relationship
On Jun 19, 2013, at 4:19 PM, George Sakkis george.sak...@gmail.com wrote: It seems that hybrid properties are not allowed to be specified as strings for the order_by parameter of a relationship; attempting it fails with InvalidRequestError: Class ... does not have a mapped column named '...'. Is this a known limitation or a bug? Sample test case below. It's kind of a missing feature; here's a patch to make that work which will be for 0.8: http://www.sqlalchemy.org/trac/ticket/2761 -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] order_by with explicit column name messes up subqueryload
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.
Re: [sqlalchemy] order_by with explicit column name messes up subqueryload
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.py#L754 -- 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.
Re: [sqlalchemy] order_by on model property
Ok, because the totalDue and totalPaid attributes are also SQLAlchemy
declarative model object properties, I converted them (and all other
similar property dependencies) to hybrid_properties and created the
associated @[property].expression methods for every hybrid_property
(though I think those @[property].expression methods are not needed
where SQLAlchemy is not converting a Python method like float() into an
SQL function--I'll test for that later). Now I get this error:
Module projects.model.main:189 in totalDue
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
TypeError: 'InstrumentedAttribute' object is not iterable
By writing line 189 on several lines, it's apparent self.workDone causes
the error. self.workDone is a relation:
class Project(DeclarativeBase):
# ...
workDone = relation('WorkDone') # one-to-many
# ...
@totalDue.expression
def totalDue(self):
'''Allow this property to be accessed at the class level'''
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
Do I need to convert that relation into a hybrid_property, or do
something else in order to use it in this order_by query? I'm beginning
to wonder if it's easier to deal with sorting by @properties by sorting
in Python after running the query--is that the case?
Tim
On 09/07/2011 03:19 PM, Michael Bayer wrote:
You'd use a hybrid for this case, and due to the usage of float() you'd
probably want to produce a separate @expression that doesn't rely on a Python
function.
Docs and examples for hybrid are at
http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html
Separate @expression:
http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior
The float() call in SQL would likely be using CAST, so take a look at
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast
for that.
On Sep 7, 2011, at 2:27 PM, Tim Black wrote:
What is the right way to use .order_by() to order by the values returned by
a model object property? My model object is like this:
class Project(DeclarativeBase):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
...
@property
def remainderDue(self):
return self.totalDue - float(self.totalPaid)
The query I'm trying to run is:
projects =
DBSession.query(model.Project).order_by(desc(model.Project.remainderDue))
This returns the following error:
Module sqlalchemy.sql.expression:1279 in _literal_as_text
ArgumentError: SQL expression object or string expected.
Tim
--
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.
--
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.
Re: [sqlalchemy] order_by on model property
On Sep 8, 2011, at 5:32 PM, Tim Black wrote:
Ok, because the totalDue and totalPaid attributes are also SQLAlchemy
declarative model object properties, I converted them (and all other
similar property dependencies) to hybrid_properties and created the
associated @[property].expression methods for every hybrid_property
(though I think those @[property].expression methods are not needed
where SQLAlchemy is not converting a Python method like float() into an
SQL function--I'll test for that later). Now I get this error:
Module projects.model.main:189 in totalDue
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
TypeError: 'InstrumentedAttribute' object is not iterable
By writing line 189 on several lines, it's apparent self.workDone causes
the error. self.workDone is a relation:
class Project(DeclarativeBase):
# ...
workDone = relation('WorkDone') # one-to-many
# ...
@totalDue.expression
def totalDue(self):
'''Allow this property to be accessed at the class level'''
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
Do I need to convert that relation into a hybrid_property, or do
something else in order to use it in this order_by query? I'm beginning
to wonder if it's easier to deal with sorting by @properties by sorting
in Python after running the query--is that the case?
When you're inside of @expression, everything you're doing is towards the goal
of producing a SQL expression construct that generates a string, which is sent
over the wire to the database where it's evaluated as part of a SQL string.
So you can't use any Python expressions that aren't supportable as a SQL
string, which includes list comprehensions and the sum() function. In this
specific case your query likely needs to join() out to a subquery that
calculates the sum using the SQL SUM function. The doc at
http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries shows the
general idea.
I'm beginning
to wonder if it's easier to deal with sorting by @properties by sorting
in Python after running the query--is that the case?
it depends very much on the specifics. If these are records that are already
to be loaded in memory, and you're dealing with small collections, then sure.
If its a huge report you're doing across thousands of rows, then maybe not.
Tim
On 09/07/2011 03:19 PM, Michael Bayer wrote:
You'd use a hybrid for this case, and due to the usage of float() you'd
probably want to produce a separate @expression that doesn't rely on a
Python function.
Docs and examples for hybrid are at
http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html
Separate @expression:
http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior
The float() call in SQL would likely be using CAST, so take a look at
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast
for that.
On Sep 7, 2011, at 2:27 PM, Tim Black wrote:
What is the right way to use .order_by() to order by the values returned by
a model object property? My model object is like this:
class Project(DeclarativeBase):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
...
@property
def remainderDue(self):
return self.totalDue - float(self.totalPaid)
The query I'm trying to run is:
projects =
DBSession.query(model.Project).order_by(desc(model.Project.remainderDue))
This returns the following error:
Module sqlalchemy.sql.expression:1279 in _literal_as_text
ArgumentError: SQL expression object or string expected.
Tim
--
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.
--
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.
--
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] order_by on model property
What is the right way to use .order_by() to order by the values returned by a model object property? My model object is like this: class Project(DeclarativeBase): __tablename__ = 'project' id = Column(Integer, primary_key=True) ... @property def remainderDue(self): return self.totalDue - float(self.totalPaid) The query I'm trying to run is: projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) This returns the following error: Module sqlalchemy.sql.expression:1279 in _literal_as_text ArgumentError: SQL expression object or string expected. Tim -- 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.
Re: [sqlalchemy] order_by on model property
You'd use a hybrid for this case, and due to the usage of float() you'd probably want to produce a separate @expression that doesn't rely on a Python function. Docs and examples for hybrid are at http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Separate @expression: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior The float() call in SQL would likely be using CAST, so take a look at http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast for that. On Sep 7, 2011, at 2:27 PM, Tim Black wrote: What is the right way to use .order_by() to order by the values returned by a model object property? My model object is like this: class Project(DeclarativeBase): __tablename__ = 'project' id = Column(Integer, primary_key=True) ... @property def remainderDue(self): return self.totalDue - float(self.totalPaid) The query I'm trying to run is: projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) This returns the following error: Module sqlalchemy.sql.expression:1279 in _literal_as_text ArgumentError: SQL expression object or string expected. Tim -- 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. -- 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] order_by with property of related table
Hi!
I have two tables: A and B defined something like that:
A:
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
Column('b_id', Integer, ForeignKey('b.id'))
B:
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
mapper of A is created with:
properties={
'b': relation(B, primaryjoin=A.b_id == B.id, lazy=False),
}
(lazy = False is important in this case)
How can I select all elements from A sorted by B.name? I checked that
I can't use
SESSION.query(A).order_by(B.name)
because it's not working - query(A) consist of JOIN with B table and B
table has alias B_1 in this query and sqlalchemy interprets
order_by(B.name) as ORDER BY B.name but there is no B alias in
query(A).
--
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] order_by(datetime)
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.
Re: [sqlalchemy] order_by(datetime)
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.
Re: [sqlalchemy] order_by: ArgumentError
On Tue, 16 Nov 2010 11:37:12 -0500
Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 16, 2010, at 6:16 AM, Enrico Morelli wrote:
On Mon, 15 Nov 2010 15:56:06 -0500
Michael Bayer mike...@zzzcomputing.com wrote:
its looking for a Column object.menus_table.c.weight instead of
'weight'.
Thanks, I modified the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(menus_table.c.weight.asc()).all()
but the error is the same:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
no , the mapping:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by=menus_table.c.weight),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
Thank you very much.
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
Re: [sqlalchemy] order_by: ArgumentError
On Mon, 15 Nov 2010 15:56:06 -0500
Michael Bayer mike...@zzzcomputing.com wrote:
its looking for a Column object.menus_table.c.weight instead of
'weight'.
Thanks, I modified the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(menus_table.c.weight.asc()).all()
but the error is the same:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
On Nov 15, 2010, at 10:03 AM, Enrico Morelli wrote:
Dear all,
I've a lot of applications using SA 0.5.6. Now I upgraded my
personal computer and now I can use SA 0.6.5 but my applications
stops to work.
I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
I try to search in google but I don't understand why I receive this
error. Someone can explain to me?
Thanks in advance
This is the table declaration:
menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)
This is the mapper declaration:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
Re: [sqlalchemy] order_by: ArgumentError
On Nov 16, 2010, at 6:16 AM, Enrico Morelli wrote:
On Mon, 15 Nov 2010 15:56:06 -0500
Michael Bayer mike...@zzzcomputing.com wrote:
its looking for a Column object.menus_table.c.weight instead of
'weight'.
Thanks, I modified the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(menus_table.c.weight.asc()).all()
but the error is the same:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
no , the mapping:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by=menus_table.c.weight),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
On Nov 15, 2010, at 10:03 AM, Enrico Morelli wrote:
Dear all,
I've a lot of applications using SA 0.5.6. Now I upgraded my
personal computer and now I can use SA 0.6.5 but my applications
stops to work.
I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
I try to search in google but I don't understand why I receive this
error. Someone can explain to me?
Thanks in advance
This is the table declaration:
menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)
This is the mapper declaration:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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] order_by: ArgumentError
Dear all,
I've a lot of applications using SA 0.5.6. Now I upgraded my personal
computer and now I can use SA 0.6.5 but my applications stops to work.
I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
I try to search in google but I don't understand why I receive this
error. Someone can explain to me?
Thanks in advance
This is the table declaration:
menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)
This is the mapper declaration:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
Re: [sqlalchemy] order_by: ArgumentError
its looking for a Column object.menus_table.c.weight instead of 'weight'.
On Nov 15, 2010, at 10:03 AM, Enrico Morelli wrote:
Dear all,
I've a lot of applications using SA 0.5.6. Now I upgraded my personal
computer and now I can use SA 0.6.5 but my applications stops to work.
I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'
I try to search in google but I don't understand why I receive this
error. Someone can explain to me?
Thanks in advance
This is the table declaration:
menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)
This is the mapper declaration:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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] order_by on a relationship() ?
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] order_by with func.count
Hello, I have this query: rsvp = session.query(Project.project, func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project, Project.id).order_by(Project.project) print rsvp SELECT project.project AS project_project, count(reservation.project_id) AS count_1 FROM reservation JOIN project ON project.id = reservation.project_id GROUP BY project.project, project.id ORDER BY project.project So far so good - but what if I want to order by column func.count(Reservation.project_id)? I can do this in SQL all right: SELECT project.project AS project_project, count(reservation.project_id) AS count_1 FROM reservation JOIN project ON project.id = reservation.project_id GROUP BY project.project, project.id ORDER BY count_1 DESC But how to do this in above sqla query? -- Regards, mk -- Premature optimization is the root of all fun. -- 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] order_by with func.count
Hello, OK I figured this out: rsvp = session.query(Project.project, Project.id, func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project, Project.id).order_by(desc(func.count(Reservation.project_id))).all() I'm not normally a vaseline man, but this is amazing: how did SQLA guess *correctly* what I wanted here? I love this toolkit! -- Regards, mk -- Premature optimization is the root of all fun. -- 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] order_by() and count()
hi, i have a typical one to many relationship between two tables: Table_A: contains, for example, Stores (id, store_name) Table_B: contains products, Prod, and which store the products belong to: (id, name, store_id) I need to create a query where i get all Store objects ordered by the number of products, e.g: store_3 34 (amount of products) store_1 23 (ditto) store_2 18 (ditto) any idea? 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] order_by and group_by won't work as I want.
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 --- I want to group them by 'Category' field and order them by 'Cost' field and output the HTML table like below --- table tr td0/td tdfoodtd tdbanana/td td$1/td /tr tr class=children td1/td tdfoodtd tdapple/td td$2/td /tr tr td4/td tdsomethingtd tdqwert/td td$5/td /tr tr class=children td5/td tdsomethingtd tdpoiuy/td td$7/td /tr tr td6/td tdanythingtd tdasdf/td td$8/td /tr tr td3/td tdbooktd tdfoobar/td td$10/td /tr tr class=children td2/td tdbooktd tdfoo/td td$15/td /tr /table --- So I wrote the code like below --- ... query = session.query(orm.Some) query = query.order_by(orm.Some.cost) query = query.group_by(orm.Some.category) ... --- Then I got a similar result as I want but not exact one. The result was like --- table tr td0/td tdfoodtd tdbanana/td td$1/td /tr tr class=children td1/td tdfoodtd tdapple/td td$2/td /tr tr td4/td tdsomethingtd tdqwert/td td$5/td /tr tr class=children td5/td tdsomethingtd tdpoiuy/td td$7/td /tr tr td6/td tdanythingtd tdasdf/td td$8/td /tr !-- The result below is wrong !!! foo is more expensive than foobar so it should be the children of the foobar. -- tr td2/td tdbooktd tdfoo/td td$15/td /tr tr class=children td3/td tdbooktd tdfoobar/td td$10/td /tr /table --- I know that foo.id foobar.id but I really want to order_by 'cost' Any idea? I need help... * sorry about my stupid english. I'm not native. --~--~-~--~~~---~--~~ 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] Order_by
Hi guys, I'm new to this list but I I'm using SA from a while ago. I'm wondering how to sort a query based on a child table attribute. Table A- Many to One - Table B. I need to make a query in A, then order those records by a property of B. query = session.query(A).order_by( B.something ) B has an B.a relation attribute to A. Any pointers on how to achieve this ? Thanks in advice ! --~--~-~--~~~---~--~~ 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] order_by on related table
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'
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
--~--~-~--~~~---~--~~
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] ORDER_BY always in SELECT statements?
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? Thanks in advance, Mark --~--~-~--~~~---~--~~ 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] order_by computed on wrong table with many-to-many
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] order_by strangeness
Hi,
I'm using SQLAlchemy 0.28 with Pylons 0.98, and when I define this
structure (abridged, so I'm not sure it will really compile...)
people_table = Table(people, metadata,
Column('id', Integer, primary_key=True),
Column('user_name', String),
Column('first_name', String),
Column('last_name', String),
Column('password', String)
)
phone_numbers_table = Table(phone_numbers, metadata,
Column('id', Integer, primary_key=True),
Column('person_id', Integer, ForeignKey('people.id')),
Column('is_alert_number', Boolean),
Column('type', String),
Column('number', String)
)
class Person(object):
pass
class PhoneNumber(object):
pass
person_mapper = mapper(Person, people_table,
properties = {
'phones' : relation(PhoneNumber, cascade=all, delete-orphan,
backref=person, order_by=desc(is_alert_number))
})
phone_mapper = mapper(PhoneNumber, phone_numbers_table)
and then do the following with a person:
for n in a_person.phones:
print n.number, n.is_alert_number
I get inconsistent results - at times just about anything can change -
usually the results are right but about one time in three the
is_alert_number is wrong, and occasionally not all the numbers are
listed or the order changes. Removing the order_by seems to fix
things.
I've looked through the bug list on Trac, and I've looked through the
group and nothing like this has jumped out at me. Are you interested
and should I try to get a real simplified test case?
cheers,
Geoff
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
