[sqlalchemy] Re: ordering list: list order not updated on delete (orm)
Hi,
just for the records: I've came up myself with an alternative solution
to store
an ordered list. The basic idea is to calculate order keys from a set
of
characters such that I can always insert items in front of the list
and at the
end of the list and also between any two items. I never need to change
any
existing ordering key. You only need the keys for the two neighbors
(or just
the first or last key in the list when inserting at the beginning or
end). The key
creation is fairly efficient to tasks like always inserting items at
the end of
the list or at the beginning of the list or for example always at the
second
position in the list. A random insertion is fine too. The efficiency
in all
those very different situations is based on some randomness in the new
key
creation processes, but this is the whole interesting thing here.
André
order_key.py - (the beef)
# -*- encoding: utf-8 -*-
# The MIT License
#
# Copyright (c) 2009 André Wobst
#
# Permission is hereby granted, free of charge, to any person
obtaining a copy
# of this software and associated documentation files (the
"Software"), to deal
# in the Software without restriction, including without limitation
the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or
sell
# copies of the Software, and to permit persons to whom the Software
is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be
included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT
SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR
OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
DEALINGS IN
# THE SOFTWARE.
"""This module generates strings to be used to define an order when
sorted as
strings. We call those strings 'keys'. All keys are composed of
printable ascii
chars. (The alphabet is actually defined in key_chars.) It is ensured,
that you
can always create a 'smaller' or 'larger' key than any key generated
by the
functions in this module. Additionally, you can always generate a key,
which is
in between two given keys."""
import random
# Note that we use some randomization to get good performance not only
when
# generating middle_keys but also when generating before_keys and
after_keys
# frequently. The randomization is fairly "auto-adjusting" by
depending on the
# key length.
key_chars = "".join(chr(i) for i in range(33,127))
middle_char = key_chars[len(key_chars)//2]
key_char_index = dict((c, i) for i, c in enumerate(key_chars))
def init_key():
"""Returns an initial order key to be used for the first item."""
return middle_char
def before_key(key):
"""Returns an order key which is before the passed key."""
new_key = []
for i, c in enumerate(key):
if c is not key_chars[0] and (i == len(key)-1 or not
random.randint(0, len(key))):
new_key.append(key_chars[key_char_index[c]-1])
if i == len(key)-1:
new_key.append(middle_char)
break
else:
new_key.append(c)
else:
raise RuntimeError("could not insert a key before '%s'" % key)
new_key = "".join(new_key)
assert new_key < key
return new_key
def after_key(key):
"""Returns an order key which is after the passed key."""
new_key = []
for c in key:
if c is not key_chars[-1] and not random.randint(0, len(key)):
new_key.append(key_chars[key_char_index[c]+1])
break
else:
new_key.append(c)
else:
new_key.append(middle_char)
new_key = "".join(new_key)
assert key < new_key
return new_key
def middle_key(key1, key2):
"""Returns an order key which is between the two passed keys. The
two keys
passed to this function must be ordered."""
assert key1 < key2
new_key = []
for i, (c1, c2) in enumerate(zip(key1, key2)):
if c1 == c2:
new_key.append(c1)
elif key_char_index[c1]+1 == key_char_index[c2]:
new_key.append(c1)
new_key.extend(after_key(key1[i+1:])) # note that
after_key doesn't fail for an empty key
break
else:
new_key.append(key_chars[(key_char_index[c1] +
key_char_index[c2])//2])
break
else:
if len(key1) > len(key2):
new_key.extend(after_key(key1[len(key2):]))
elif len(key2) > len(key1):
new_key.extend(before_key(key2[len(key1):]))
else:
raise RuntimeError("identical keys?!")
new_key = "".join(new_key)
assert key1 < new_key < key2
return new_key
if __name__ == "
[sqlalchemy] Re: ordering list: list order not updated on delete (orm)
wobsta wrote:
>
> On 4 Jul., 17:05, Michael Bayer wrote:
>> remove the item using remove(). What's the "items must be in a list
>> rule", the not nullable foreign key ? thats what "delete-orphan"
>> cascade is for, its in the tutorial and reference documentation.
>
> Thanks, this works for the simple case in my first example. However,
> consider inserting the item in two ordered lists. If you remove it by
> the list method in one list, the position information is properly
> updated for *this* list. But not for the other list. See the following
> example:
that's true, if you have a child item that can be an "orphan" from two
separate lists, and you trigger delete orphan on one of them, the other
collection has no idea anything has happened until its refreshed.
the ORM doesn't attempt to replicate the whole database in Python. Once
you commit() the session, all collections and such are expired and you'll
see the correct data as it gets reloaded. Although in this case I would
probably just take the extra step of detaching l2 from i3. delete-orphan
only implies item removal management for the single relation() on which
it's set.
>
>
> # -*- encoding: utf-8 -*-
>
> from sqlalchemy import create_engine, MetaData, Table, Column,
> Integer, Unicode, ForeignKey, UniqueConstraint
> from sqlalchemy.orm import sessionmaker, mapper, relation
> from sqlalchemy.ext.orderinglist import ordering_list
>
> metadata = MetaData()
>
> stock_table = Table("stock", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", Unicode, unique=True))
>
> list_table = Table("list", metadata,
>Column("id", Integer, primary_key=True),
>Column("name", Unicode, unique=True))
>
> item_table = Table("item", metadata,
>Column("id", Integer, primary_key=True),
>Column("name", Unicode, unique=True),
>Column("stockpos", Integer),
>Column("stock_id", Integer, ForeignKey("stock.id"),
> nullable=False),
>Column("listpos", Integer),
>Column("list_id", Integer, ForeignKey("list.id"),
> nullable=False),
>UniqueConstraint("stock_id", "name"),
>UniqueConstraint("list_id", "name"))
>
>
> class Stock(object):
>
> def __init__(self, name):
> self.name = name
>
> def __repr__(self):
> return "" % self.name
>
>
> class List(object):
>
> def __init__(self, name):
> self.name = name
>
> def __repr__(self):
> return "" % self.name
>
>
> class Item(object):
>
> def __init__(self, name, stock, list):
> self.name = name
> self.stock = stock
> self.list = list
>
> def __repr__(self):
> return " %s>" % (self.name, self.stock, self.stockpos, self.list, self.listpos)
>
>
> mapper(Stock, stock_table,
>properties={"items": relation(Item,
> backref="stock",
> order_by=[item_table.c.stockpos],
> collection_class=ordering_list
> ("stockpos"),
> cascade="save-
> update,merge,delete,delete-orphan")})
> mapper(List, list_table,
>properties={"items": relation(Item,
> backref="list",
> order_by=[item_table.c.listpos],
> collection_class=ordering_list
> ("listpos"),
> cascade="save-
> update,merge,delete,delete-orphan")})
> mapper(Item, item_table)
>
>
> engine = create_engine("postgres:///list", echo=True)
> metadata.bind = engine
> stock_table.create()
> list_table.create()
> item_table.create()
>
> Session = sessionmaker(engine)
> session = Session()
> s = Stock(u"items")
> l1 = List(u"a")
> l2 = List(u"b")
> i1 = Item(u"i1", s, l1)
> i2 = Item(u"i2", s, l1)
> i3 = Item(u"i3", s, l2)
> i4 = Item(u"i4", s, l2)
> session.add(s)
> session.commit()
> # up to this point everything is fine
> s.items.remove(i3)
> session.commit()
> print i4 # listpos should be 0, but it is 1 (i.e. not updated)
> session.close()
> engine.dispose()
>
> >
>
--~--~-~--~~~---~--~~
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: ordering list: list order not updated on delete (orm)
On 4 Jul., 17:05, Michael Bayer wrote:
> remove the item using remove(). What's the "items must be in a list
> rule", the not nullable foreign key ? thats what "delete-orphan"
> cascade is for, its in the tutorial and reference documentation.
Thanks, this works for the simple case in my first example. However,
consider inserting the item in two ordered lists. If you remove it by
the list method in one list, the position information is properly
updated for *this* list. But not for the other list. See the following
example:
# -*- encoding: utf-8 -*-
from sqlalchemy import create_engine, MetaData, Table, Column,
Integer, Unicode, ForeignKey, UniqueConstraint
from sqlalchemy.orm import sessionmaker, mapper, relation
from sqlalchemy.ext.orderinglist import ordering_list
metadata = MetaData()
stock_table = Table("stock", metadata,
Column("id", Integer, primary_key=True),
Column("name", Unicode, unique=True))
list_table = Table("list", metadata,
Column("id", Integer, primary_key=True),
Column("name", Unicode, unique=True))
item_table = Table("item", metadata,
Column("id", Integer, primary_key=True),
Column("name", Unicode, unique=True),
Column("stockpos", Integer),
Column("stock_id", Integer, ForeignKey("stock.id"),
nullable=False),
Column("listpos", Integer),
Column("list_id", Integer, ForeignKey("list.id"),
nullable=False),
UniqueConstraint("stock_id", "name"),
UniqueConstraint("list_id", "name"))
class Stock(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "" % self.name
class List(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "" % self.name
class Item(object):
def __init__(self, name, stock, list):
self.name = name
self.stock = stock
self.list = list
def __repr__(self):
return "" % (self.name, self.stock, self.stockpos, self.list, self.listpos)
mapper(Stock, stock_table,
properties={"items": relation(Item,
backref="stock",
order_by=[item_table.c.stockpos],
collection_class=ordering_list
("stockpos"),
cascade="save-
update,merge,delete,delete-orphan")})
mapper(List, list_table,
properties={"items": relation(Item,
backref="list",
order_by=[item_table.c.listpos],
collection_class=ordering_list
("listpos"),
cascade="save-
update,merge,delete,delete-orphan")})
mapper(Item, item_table)
engine = create_engine("postgres:///list", echo=True)
metadata.bind = engine
stock_table.create()
list_table.create()
item_table.create()
Session = sessionmaker(engine)
session = Session()
s = Stock(u"items")
l1 = List(u"a")
l2 = List(u"b")
i1 = Item(u"i1", s, l1)
i2 = Item(u"i2", s, l1)
i3 = Item(u"i3", s, l2)
i4 = Item(u"i4", s, l2)
session.add(s)
session.commit()
# up to this point everything is fine
s.items.remove(i3)
session.commit()
print i4 # listpos should be 0, but it is 1 (i.e. not updated)
session.close()
engine.dispose()
--~--~-~--~~~---~--~~
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: ordering list: list order not updated on delete (orm)
On Jul 4, 2009, at 2:21 AM, wobsta wrote: > > Hi, > > in my following example I don't know how to properly delete items in > an ordered list. I can't call items.remove (as I would violate the > "items must be in a list rule"). remove the item using remove(). What's the "items must be in a list rule", the not nullable foreign key ? thats what "delete-orphan" cascade is for, its in the tutorial and reference documentation. --~--~-~--~~~---~--~~ 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: Ordering results of a WHERE x in y query by y
Thanks. But using a CASE clause becomes objectionable in exactly those cases
where I would want to have the DB do the sorting — i.e. where the table is
big enough that just sorting the result set in python code using array index
(rows.sort(key=lambda row: values.index(row[0]))) would be a Bad Thing
(since the key function is O(n)).
But then, sorting on a reversed enumeration dict in python is
algorithmically the same as the temp table approach. Something like:
rows = session.query(...).all()
value_to_index = dict((v,k) for (k,v) in enumerate(values))
rows.sort(key=lambda value: value_to_index[value])
so I suppose that's the cleanest solution here, unless one really prefers to
make the DB do the sorting.
I believe all of these approaches will gracefully handle the case where
values are not unique (the order will just be arbitrary within each group
with the same value).
Regards,
- Gulli
On Thu, Feb 26, 2009 at 9:40 AM, Ants Aasma wrote:
>
> import sqlalchemy
>
> def index_in(col, valuelist):
>return sqlalchemy.case([(value,idx) for idx,value in enumerate
> (valuelist)], value=col)
>
> session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in
> (C.someattr, valuelist))
>
> Don't try to do this with huge lists of items.
>
> On Feb 25, 5:53 pm, Gunnlaugur Briem wrote:
> > Hi all,
> >
> > having a x IN y query, with y supplied as input to the query:
> >
> > session.query(C).filter(C.someattr.in_(valuelist))
> >
> > is there a way to tell SQLAlchemy to order the results according to
> > valuelist? I.e. not by the natural order of someattr, but by the
> > arbitrary order seen in valuelist? E.g.:
> >
> > session.add(C(someattr='Abigail'))
> > session.add(C(someattr='Benjamin'))
> > session.add(C(someattr='Carl'))
> > valuelist = ['Benjamin', 'Abigail']
> > q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever
> > (valuelist))
> > q.all()
> > # returns [C('Benjamin'), C('Abigail')]
> >
> > The solution I can think of is to create a temporary table with
> > sess.execute('create temp table ...'), insert the valuelist into that
> > temp table along with a sequence index, join to that temporary table
> > and order by its index. Is there a less kludgy way?
> >
> > Regards,
> >
> > - Gulli
> >
>
--~--~-~--~~~---~--~~
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: Ordering results of a WHERE x in y query by y
import sqlalchemy
def index_in(col, valuelist):
return sqlalchemy.case([(value,idx) for idx,value in enumerate
(valuelist)], value=col)
session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in
(C.someattr, valuelist))
Don't try to do this with huge lists of items.
On Feb 25, 5:53 pm, Gunnlaugur Briem wrote:
> Hi all,
>
> having a x IN y query, with y supplied as input to the query:
>
> session.query(C).filter(C.someattr.in_(valuelist))
>
> is there a way to tell SQLAlchemy to order the results according to
> valuelist? I.e. not by the natural order of someattr, but by the
> arbitrary order seen in valuelist? E.g.:
>
> session.add(C(someattr='Abigail'))
> session.add(C(someattr='Benjamin'))
> session.add(C(someattr='Carl'))
> valuelist = ['Benjamin', 'Abigail']
> q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever
> (valuelist))
> q.all()
> # returns [C('Benjamin'), C('Abigail')]
>
> The solution I can think of is to create a temporary table with
> sess.execute('create temp table ...'), insert the valuelist into that
> temp table along with a sequence index, join to that temporary table
> and order by its index. Is there a less kludgy way?
>
> Regards,
>
> - Gulli
--~--~-~--~~~---~--~~
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: Ordering query for a list
afaik, this is like traversing a tree, just maybe worse (much bigger
length). i would either load them all somehow (unless they are
zillions), or create a parasitic named groupings, say list1, list2,
list3 and link them via m2m. e.g. list1 links to a1,a2,a4; list 2
links to a6,a5,a7 etc. no idea if these would help in your case
(legacy db)...
svil
On Wednesday 07 January 2009 22:56:22 MikeCo wrote:
> This may not be an SA specific question, but maybe there is an
> answer here.
>
> I have a table that contains data that is actually a linked list.
> Each record has a previd column that is the id of the previous
> record in the list. The code to maintain the list exists and works
> well (a legacy non-SA application).
>
> The problem is how to efficiently retrieve the records in sorted
> order.
>
> Sample code:
>
> # experiment with querying linked list stored in database
> # test data for table x
> # (id, nam, previd)
> # previd creates a lnked list of names ('one', 'two', 'three',
> 'four') xdata = ((1, 'four' ,3),
> (2, 'one' ,0),
> (3, 'three',4),
> (4, 'two' ,2),)
>
> from sqlalchemy import (Column, Integer, String, ForeignKey,
> create_engine, MetaData)
> from sqlalchemy.orm import relation, backref, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine('sqlite:///')
> metadata = MetaData(bind=engine)
> Base = declarative_base(metadata=metadata)
> Session = sessionmaker()
>
> class X(Base):
> __tablename__ = 'x'
> id = Column(Integer, primary_key = True)
> name = Column(String)
> previd = Column(Integer, ForeignKey('x.id'))
> nextx = relation('X', uselist=False,
> backref=backref('prevx', uselist=False,
> remote_side='X.id')) def __init__(self, id, name, previd):
> self.id=id
> self.name=name
> self.previd=previd
> def __repr__(s):
> return "-- %s %s %s--" % (s.id,s.name,s.previd)
>
> engine.echo=False
> metadata.create_all()
>
> sess = Session()
> for x in xdata:
> xobj = X(x[0], x[1], x[2])
> sess.add(xobj)
> sess.commit()
> sess.expunge_all()
>
> print '# retrieve unsorted'
> query = sess.query(X)
> for r in query:
> print r
> sess.expunge_all()
>
> print '# retrieve sorted'
> query = sess.query(X).filter(X.previd == 0)
> r = query.first()
> while r:
> print r
> r=r.nextx
>
> The sorted retrieval technique used here issues a separate SELECT
> for each row in the list. Is there a way to construct the query to
> retrieve all rows with one SELECT? I'm not sure this can be done
> with plain SQL, but if it is possible we should be able to do it
> with SA too.
>
> --
> Mike
>
>
>
--~--~-~--~~~---~--~~
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: Ordering null dates
Only for the record: I just noticed that another simple workaround is ordering by something like "start_date is not null, start_date, end_date is null, end_date". SA could also implement "nullsfirst()/ nullslast()" that way if the database engine does not support "nulls first/nulls last". --~--~-~--~~~---~--~~ 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: Ordering null dates
On Jun 9, 2008, at 12:56 PM, Christoph Zwerschke wrote:
>
> Michael Bayer wrote:
>> I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0
>> ELSE x . We have case() construct for that.
>
> Yes, but that still leaves me with having to code "infinity" some way.
>
>> For your version, use func.coalesce(start,
>> literal_column("timestamp '-
>> infinity'")) .
>
> Thank you. literal_column was exactly what I was looking for.
>
> Though I still would like to have "nulls first/last" as well, this
> construct is also useful when checking date ranges with "between".
yeah we have the ticket, sooner or later unless someone patches :)
--~--~-~--~~~---~--~~
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: Ordering null dates
Michael Bayer wrote:
> I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0
> ELSE x . We have case() construct for that.
Yes, but that still leaves me with having to code "infinity" some way.
> For your version, use func.coalesce(start, literal_column("timestamp '-
> infinity'")) .
Thank you. literal_column was exactly what I was looking for.
Though I still would like to have "nulls first/last" as well, this
construct is also useful when checking date ranges with "between".
Thanks again for your quick and helpful answer, as always...
-- Christoph
--~--~-~--~~~---~--~~
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: Ordering null dates
On Jun 9, 2008, at 11:34 AM, Christoph Zwerschke wrote:
>
> I need to order a table by start and end dates, where null values
> should
> be interpreted as "prior to all values" for start dates and "later
> than
> all values" for end dates.
>
> This could be realized with "nulls first", "nulls last", but it seems
> this did not make it into SQLAlchemy yet (ticket #723).
>
> A workaround (for PostgreSQL) would be something like this:
>
> order_by coalesce(start, timestamp '-infinity'),
> coalesce(end_date, timestamp 'infinity')
>
> But I don't find how to construct this with SQLAlchemy. Any ideas? Any
> better solutions?
I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0
ELSE x . We have case() construct for that.
For your version, use func.coalesce(start, literal_column("timestamp '-
infinity'")) .
--~--~-~--~~~---~--~~
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: ordering
On May 30, 2008, at 12:25 PM, Geoff wrote: > > Hi! > > I've noticed that a very simple query has an ordering applied to it > even though I haven't asked for one. Is there a way to stop it doing > that? > > the query: > Session.query(User).set_shard(shard).filter_by(uuid=uuid).all() > order_by(None) on Query, order_by=None on mapper(), order_by=None on relation(), or upgrade to 0.5; in 0.5 all the default "order_by" stuff is removed. --~--~-~--~~~---~--~~ 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: ordering
You can add .order_by(None) to the query to remove the default ordering. On Fri, May 30, 2008 at 12:25 PM, Geoff <[EMAIL PROTECTED]> wrote: > > Hi! > > I've noticed that a very simple query has an ordering applied to it > even though I haven't asked for one. Is there a way to stop it doing > that? > > the query: > Session.query(User).set_shard(shard).filter_by(uuid=uuid).all() > > 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: Ordering results ina self-referential join
OK you got it in r4673, sorry youre hitting all these (kinda weird)
On May 6, 2008, at 7:20 PM, Michael Bayer wrote:
>
> thats really weird. I dont have time to check this now but i added
> ticket 1027 to confirm.
>
>
> On May 6, 2008, at 5:22 PM, Moshe C. wrote:
>
>> I couldn't create a simple test case, but I have analyzed the cause
>> of
>> the problem.
>>
>> The order_by() method is sent a list as an argument, but the argument
>> 'criterion' becomes a tuple
>> because of the "def order_by(self, *criterion)" syntax.
>>
>> in the case of "if self._aliases_tail:" , 'criterion' becomes a list
>> again, but if _aliases_tail is None it remains a tuple.
>>
>> Now the cause of the problem is that on the first call to order_by(),
>> self._aliases_tail exists, and on the 2nd call, following the
>> reset_joinpoint() call, it is None. Therefore the '_order_by member'
>> is initialized as a list, and later a tuple is attempted to be
>> concatenated and hence the failure.
>>
>> The calling code from my source looks like this:
>>
>> myquery = Node.query()
>> myquery = myquery.join('parent', aliased=True)
>>
>> myquery = myquery.order_by(Node.c.name) #
>> _aliases_tail exists for this call
>> myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) #
>> _aliases_tail is None for this call
>
>
> >
--~--~-~--~~~---~--~~
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: Ordering results ina self-referential join
thats really weird. I dont have time to check this now but i added
ticket 1027 to confirm.
On May 6, 2008, at 5:22 PM, Moshe C. wrote:
> I couldn't create a simple test case, but I have analyzed the cause of
> the problem.
>
> The order_by() method is sent a list as an argument, but the argument
> 'criterion' becomes a tuple
> because of the "def order_by(self, *criterion)" syntax.
>
> in the case of "if self._aliases_tail:" , 'criterion' becomes a list
> again, but if _aliases_tail is None it remains a tuple.
>
> Now the cause of the problem is that on the first call to order_by(),
> self._aliases_tail exists, and on the 2nd call, following the
> reset_joinpoint() call, it is None. Therefore the '_order_by member'
> is initialized as a list, and later a tuple is attempted to be
> concatenated and hence the failure.
>
> The calling code from my source looks like this:
>
> myquery = Node.query()
> myquery = myquery.join('parent', aliased=True)
>
> myquery = myquery.order_by(Node.c.name) #
> _aliases_tail exists for this call
> myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) #
> _aliases_tail is None for this call
--~--~-~--~~~---~--~~
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: Ordering results ina self-referential join
I couldn't create a simple test case, but I have analyzed the cause of
the problem.
The order_by() method is sent a list as an argument, but the argument
'criterion' becomes a tuple
because of the "def order_by(self, *criterion)" syntax.
in the case of "if self._aliases_tail:" , 'criterion' becomes a list
again, but if _aliases_tail is None it remains a tuple.
Now the cause of the problem is that on the first call to order_by(),
self._aliases_tail exists, and on the 2nd call, following the
reset_joinpoint() call, it is None. Therefore the '_order_by member'
is initialized as a list, and later a tuple is attempted to be
concatenated and hence the failure.
The calling code from my source looks like this:
myquery = Node.query()
myquery = myquery.join('parent', aliased=True)
myquery = myquery.order_by(Node.c.name) #
_aliases_tail exists for this call
myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) #
_aliases_tail is None for this call
On May 6, 10:25 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 6, 2008, at 3:08 PM, Moshe C. wrote:
>
>
>
>
>
> > Both methods cause a crash (yes, on 0.4.5) .
>
> > converting a tuple to a list in sqlalchemy/orm/query.py fixes it for
> > one of the methods, for the other you need to do the opposite, convert
> > a list to a tuple.
>
> > File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in
> > list_sources
> > myquery =
> > myquery.reset_joinpoint().order_by(model.Source.c.popularity)
> > File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in
> > starargs_as_list
> > return func(self, *to_list(args[0], []), **kwargs)
> > File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in
> > order_by
> > q._order_by = q._order_by + criterion
> > TypeError: can only concatenate list (not "tuple") to list
>
> I cant reproduce that at all, even sending purposely wacky arguments
> to the previous order_by().can you please provide a test case ?
--~--~-~--~~~---~--~~
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: Ordering results ina self-referential join
On May 6, 2008, at 3:08 PM, Moshe C. wrote: > > Both methods cause a crash (yes, on 0.4.5) . > > converting a tuple to a list in sqlalchemy/orm/query.py fixes it for > one of the methods, for the other you need to do the opposite, convert > a list to a tuple. > > File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in > list_sources > myquery = > myquery.reset_joinpoint().order_by(model.Source.c.popularity) > File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in > starargs_as_list > return func(self, *to_list(args[0], []), **kwargs) > File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in > order_by > q._order_by = q._order_by + criterion > TypeError: can only concatenate list (not "tuple") to list > I cant reproduce that at all, even sending purposely wacky arguments to the previous order_by().can you please provide a test case ? --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
Both methods cause a crash (yes, on 0.4.5) .
converting a tuple to a list in sqlalchemy/orm/query.py fixes it for
one of the methods, for the other you need to do the opposite, convert
a list to a tuple.
File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in
list_sources
myquery =
myquery.reset_joinpoint().order_by(model.Source.c.popularity)
File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in
starargs_as_list
return func(self, *to_list(args[0], []), **kwargs)
File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in
order_by
q._order_by = q._order_by + criterion
TypeError: can only concatenate list (not "tuple") to list
On May 6, 8:45 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 6, 2008, at 1:43 PM, Moshe C. wrote:
>
>
>
> > Hi,
>
> > Node is an orm mapped class, which is self-referential.
>
> > myquery = Node.query()
> > myquery = myquery.join('parent', aliased=True)
> > myquery = myquery.filter(Node.c.desc.like('%something'))
> > myquery = myquery.order_by(Node.c.name)
>
> > The last line orders by the 'name' of the 2nd joined table.
>
> > How can I add another order_by (after the one above) that orders by
> > some column of the first instance of the table?
>
> place another order_by() either before the join(), or after calling
> reset_joinpoint(). Make sure you're on 0.4.5.
--~--~-~--~~~---~--~~
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: Ordering results ina self-referential join
On May 6, 2008, at 1:43 PM, Moshe C. wrote:
>
> Hi,
>
> Node is an orm mapped class, which is self-referential.
>
> myquery = Node.query()
> myquery = myquery.join('parent', aliased=True)
> myquery = myquery.filter(Node.c.desc.like('%something'))
> myquery = myquery.order_by(Node.c.name)
>
> The last line orders by the 'name' of the 2nd joined table.
>
> How can I add another order_by (after the one above) that orders by
> some column of the first instance of the table?
>
place another order_by() either before the join(), or after calling
reset_joinpoint(). Make sure you're on 0.4.5.
--~--~-~--~~~---~--~~
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: Ordering by related column's related column
On Feb 4, 11:03 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Feb 4, 2008, at 3:46 PM, Utku Altinkaya wrote: > > > > > I get it, the result with joins for eager loading has nothing to do > > with sorting. So I have to join them to base selected set to use. But > > I feel like selecting twice, is there a peformance penalty here? > > > properties = > > properties > > .select_from > > (data > > .properties_table > > .join(data.addresses_table.join(data.districts_table))) > > yeah if you are joining there, and also joining for the eager load, it > doesnt perform as well. so consider using order_by=None on the parent > mapper or query.order_by(None). Thanks for help and patience, I'm really starting believe you are not a single person but a huge organization under the name of Michael Bayer :) --~--~-~--~~~---~--~~ 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: Ordering by related column's related column
On Feb 4, 2008, at 3:46 PM, Utku Altinkaya wrote: > > > > > I get it, the result with joins for eager loading has nothing to do > with sorting. So I have to join them to base selected set to use. But > I feel like selecting twice, is there a peformance penalty here? > > properties = > properties > .select_from > (data > .properties_table > .join(data.addresses_table.join(data.districts_table))) yeah if you are joining there, and also joining for the eager load, it doesnt perform as well. so consider using order_by=None on the parent mapper or query.order_by(None). --~--~-~--~~~---~--~~ 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: Ordering by related column's related column
On Feb 4, 8:13 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Feb 4, 2008, at 11:29 AM, Utku Altinkaya wrote: > > > > > Greetings, > > > I have Users class with relation to Address which have relation to > > Cities, all are eager loaded. I want to sort the Users query with the > > name field of the cities table. But if I order by City.name the cities > > table is joined to the actual query. How can I explain it to use the > > cities.name field in the join statement ? Or any different are welcome > > also. > > two ways. > > historically we've told people not to rely upon eager loading for > ordering or join criterion: > > http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN... > > while that FAQ entry seems kind of finalish, you can achieve some > basic ordering against the eager load if you set order_by=None on the > parent mapper (or set it to None with your Query; > query.order_by(None)), and then set order_by as desired on each > relation() (which can also be None, such as on your Address > ordering). that way the ordering of the eagerly loaded collection > affects the ordering overall. I get it, the result with joins for eager loading has nothing to do with sorting. So I have to join them to base selected set to use. But I feel like selecting twice, is there a peformance penalty here? properties = properties.select_from(data.properties_table.join(data.addresses_table.join(data.districts_table))) SELECT anon_1.properties_address_id AS anon_1_properti es_address_id , anon_1.properties_id AS anon_1_properties_id , anon_1.properties_ctimeAS anon_1_properties_ctime , anon_1. properties_utime AS anon_1_properties_utime , anon_1.properties_category AS anon_1_properties_category, anon_1.properties_default_image_id AS anon_1_properties_default_image_id, towns_1.id AS towns_1_id, towns_1.city_idAS towns_1_city_id , towns_1.name AS towns_1_name , towns_1.latAS towns_1_lat , towns_1.lngAS towns_1_lng , cities_1.id A S cities_1_id , cities_1.name AS cities_1_name , cities_1.latAS cities_1_lat , cities_1.lngAS cities_1_lng , cities_1.acod e AS cities_1_acode , districts_1.id AS districts_1_id , districts_1.town_id AS districts_1_town_id, districts_1.nameAS d istricts_1_name , districts_1.lat AS districts_1_lat, districts_1.lng AS districts_1_lng, addresses_1.id AS addresses_1_i d, addresses_1.city_id AS addresses_1_city_id, addresses_1.town_id AS addresses_1_town_id, addresses_1.district_id AS ad dresses_1_district_id , addresses_1.address AS addresses_1_address, addresses_1.phone AS addresses_1_phone , addresses_1.p hone2 AS addresses_1_phone2 , addresses_1.phone_cell AS addresses_1_phone_cell , addresses_1.fax AS addresses_1_fax, addre sses_1.latAS addresses_1_lat, addresses_1.lng AS addresses_1_lng FROM (SELECT properties.address_id AS properties_address_id , properties.id AS properties_id , properties.ctimeAS propert ies_ctime , properties.utimeAS properties_utime , properties.status AS properties_status , properties.kind AS properties_k ind , properties.category AS properties_category , properties.priceAS properties_price , properties.price_curAS propertie s_price_cur , properties.deposit AS properties_deposit, properties.deposit_cur AS pro
[sqlalchemy] Re: Ordering by related column's related column
On Feb 4, 2008, at 11:29 AM, Utku Altinkaya wrote: > > Greetings, > > > I have Users class with relation to Address which have relation to > Cities, all are eager loaded. I want to sort the Users query with the > name field of the cities table. But if I order by City.name the cities > table is joined to the actual query. How can I explain it to use the > cities.name field in the join statement ? Or any different are welcome > also. > two ways. historically we've told people not to rely upon eager loading for ordering or join criterion: http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN while that FAQ entry seems kind of finalish, you can achieve some basic ordering against the eager load if you set order_by=None on the parent mapper (or set it to None with your Query; query.order_by(None)), and then set order_by as desired on each relation() (which can also be None, such as on your Address ordering). that way the ordering of the eagerly loaded collection affects the ordering overall. --~--~-~--~~~---~--~~ 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: Ordering by field in related object
Thanks a lot!! Grin the Django query object actually allows for this sort of ordering so i just figured SqlAlchemy should allow it as well :/ But never mind now I know the trick it's super easy to adjust for it ;) --~--~-~--~~~---~--~~ 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: Ordering by field in related object
Since I am new to SA just want if that means that even if we have an eager
load on a 1:N relationships we should still do an explicit JOIN if the query
involves columns from both side of relations?
On 4/9/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
> eagerly loaded relationships are not part of the main Query criterion/
> select. the eager loads are always tacked on secondary to the inner
> query. the main goal being that if you had lazy or eagerly loaded
> relationships, in both cases youd get the identical result. so any
> tables that you add to the Query criterion are completely distinct
> from their possible appearance in an eager loaded relationship (it
> has to be this way, otherwise eager loads would change the result of
> the query..eager loads are meant to be an optimization only). thats
> why the StoryStats' table is getting added in to the inner query.
>
> so the approaches to take are:
>
> 1. explicitly join against StoryStats:
>
> session.query(Story).join('storystatrelation').order_by
> (StoryStats.c.rating)
>
> 2. create whatever query you want and load its instances via instances
> ():
>
> s = story_table.outerjoin(story_stats_table).select
> (order_by=story_stats_table.c.rating)
> session.query(Story).options(contains_eager
> ('storystatrelation')).instances(s.execute())
>
> 3. a little less dynamic, specify order_by in the eagerly loaded
> relation() in the mapper setup, and specify None for the query
> ordering (this is more of a trick).
>
> mapper(Story, story_table, properties={
> 'storystatrelation':relation(StoryStats,
> story_stats_table,
> lazy=False, order_by=[story_stats_table.c.rating])
> })
>
> session.query(Story).order_by(None)
>
>
> On Apr 8, 2007, at 5:39 PM, Norjee wrote:
>
> >
> > It seems I don't understand how i can order the results of a query.
> > Assume that i have two object Story and StoryStats. Each Story has
> > one
> > StoryStats, mapped by ForeignKey. The relation is eagerloaded
> > (lazy=False)
> > (The actual model is a tad more complicated, but the idea is the
> > same)
> >
> > When i now try to select Stories, ordering by create_date goes fine,
> > e.g.
> > session.query(Story).order_by(Story.c.create_date)
> >
> >
> > But ordering by the realated StoryStats goes awry :/
> > session.query(Story).order_by(StoryStats.c.rating), only a singe
> > Story
> > is returned
> > Now errors are thrown however.
> >
> >
> > Is there something I'm missing here? (I know I probably could do
> > session.query(StoryStats).order_by(StoryStats.c.rating), but that
> > kind
> > of defeats the purpose as the ordering is dynamic)
> >
> >
> > >
>
>
> >
>
--~--~-~--~~~---~--~~
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: Ordering by field in related object
eagerly loaded relationships are not part of the main Query criterion/
select. the eager loads are always tacked on secondary to the inner
query. the main goal being that if you had lazy or eagerly loaded
relationships, in both cases youd get the identical result. so any
tables that you add to the Query criterion are completely distinct
from their possible appearance in an eager loaded relationship (it
has to be this way, otherwise eager loads would change the result of
the query..eager loads are meant to be an optimization only). thats
why the StoryStats' table is getting added in to the inner query.
so the approaches to take are:
1. explicitly join against StoryStats:
session.query(Story).join('storystatrelation').order_by
(StoryStats.c.rating)
2. create whatever query you want and load its instances via instances
():
s = story_table.outerjoin(story_stats_table).select
(order_by=story_stats_table.c.rating)
session.query(Story).options(contains_eager
('storystatrelation')).instances(s.execute())
3. a little less dynamic, specify order_by in the eagerly loaded
relation() in the mapper setup, and specify None for the query
ordering (this is more of a trick).
mapper(Story, story_table, properties={
'storystatrelation':relation(StoryStats, story_stats_table,
lazy=False, order_by=[story_stats_table.c.rating])
})
session.query(Story).order_by(None)
On Apr 8, 2007, at 5:39 PM, Norjee wrote:
>
> It seems I don't understand how i can order the results of a query.
> Assume that i have two object Story and StoryStats. Each Story has
> one
> StoryStats, mapped by ForeignKey. The relation is eagerloaded
> (lazy=False)
> (The actual model is a tad more complicated, but the idea is the
> same)
>
> When i now try to select Stories, ordering by create_date goes fine,
> e.g.
> session.query(Story).order_by(Story.c.create_date)
>
>
> But ordering by the realated StoryStats goes awry :/
> session.query(Story).order_by(StoryStats.c.rating), only a singe
> Story
> is returned
> Now errors are thrown however.
>
>
> Is there something I'm missing here? (I know I probably could do
> session.query(StoryStats).order_by(StoryStats.c.rating), but that
> kind
> of defeats the purpose as the ordering is dynamic)
>
>
> >
--~--~-~--~~~---~--~~
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: Ordering by field in related object
The generated query is = SELECT verhalen_verhaal.body AS verhalen_verhaal_body, verhalen_verhaal.update_date AS verhalen_verhaal_update_date, verhalen_verhaal.user_id AS verhalen_verhaal_user_id, verhalen_verhaal.review_date AS verhalen_verhaal_review_date, verhalen_verhaal.title AS verhalen_verhaal_title, verhalen_verhaal.ip AS verhalen_verhaal_ip, verhalen_verhaal.author AS verhalen_verhaal_author, verhalen_verhaal.enabled AS verhalen_verhaal_enabled, verhalen_verhaal.author_mail AS verhalen_verhaal_author_mail, verhalen_verhaal.slug AS verhalen_verhaal_slug, verhalen_verhaal.status AS verhalen_verhaal_status, verhalen_verhaal.create_date AS verhalen_verhaal_create_date, verhalen_verhaal.inleiding AS verhalen_verhaal_inleiding, verhalen_verhaal.author_slug AS verhalen_verhaal_author_slug, verhalen_verhaal.may_addify AS verhalen_verhaal_may_addify, verhalen_verhaa_4076.score AS verhalen_verhaa_4076_score, verhalen_verhaa_4076.keer_gelezen AS verhalen_verhaa_4076_kee_f168, verhalen_verhaa_4076.id AS verhalen_verhaa_4076_id, verhalen_verhaal.verhaalstats_id AS verhalen_verhaal_verhaal_9484, verhalen_verhaal.id AS verhalen_verhaal_id FROM ( SELECT verhalen_verhaal.id AS verhalen_verhaal_id, verhalen_verhaalstats.score AS verhalen_verhaalstats_score FROM verhalen_verhaal, verhalen_verhaalstats ORDER BY verhalen_verhaalstats.score DESC LIMIT 30 ) AS tbl_row_count, verhalen_verhaal LEFT OUTER JOIN verhalen_verhaalstats AS verhalen_verhaa_4076 ON verhalen_verhaa_4076.id = verhalen_verhaal.verhaalstats_id WHERE verhalen_verhaal.id = tbl_row_count.verhalen_verhaal_id ORDER BY tbl_row_count.verhalen_verhaalstats_score DESC , verhalen_verhaa_4076.id = For what it's worth it seems the generated subquery is wrong, in that it lacks a where clause, it is: = SELECT verhalen_verhaal.id AS verhalen_verhaal_id, verhalen_verhaalstats.score AS verhalen_verhaalstats_score FROM verhalen_verhaal, verhalen_verhaalstats ORDER BY verhalen_verhaalstats.score DESC LIMIT 30 = But when i change it to: = SELECT verhalen_verhaal.id AS verhalen_verhaal_id, verhalen_verhaalstats.score AS verhalen_verhaalstats_score FROM verhalen_verhaal, verhalen_verhaalstats WHERE verhalen_verhaalstats.id = verhalen_verhaal.verhaalstats_id ORDER BY verhalen_verhaalstats.score DESC LIMIT 30 = It looks much better ;). Of course I'm clueless as to what to change to my model to generate that query :/ --~--~-~--~~~---~--~~ 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: Ordering with the AssociationProxy
Performing an ordered result was simple enough. In the
proxied_association.py example, only the following mapper() call was
changed:
mapper(Order, orders,
properties={'itemassociations':relation(OrderItem, cascade="all,
delete-orphan", lazy=True, order_by=items.c.description)})
--~--~-~--~~~---~--~~
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: Ordering with the AssociationProxy
theres an example in the distro, in examples/association/proxied_association.py that shows off the creator function as well as the **kwargs idea. its used to provide a customized method of creating the association object. also make sure you understand how to use association objects without this proxying interface first, since AssociationProxy is a completely optional shortcut. --~--~-~--~~~---~--~~ 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: Ordering with the AssociationProxy
Okay thanks, I'll work some more on it and post my solution. By the way, in looking at the proxied_association.py example, I have another question. When is the creator (callable) function required? If a proxied assocation can be created without a creator function, what additional abilities does supplying a creator provide? The example in the wiki docs doesn't use a creator. And I'm trying to get a better handle on all of this. Thanks again, Paul Kippes --~--~-~--~~~---~--~~ 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: Ordering with the AssociationProxy
this is doable with the proper combination of eager loading between association and remote instance, and the order_by option on the remote relation(). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
