relation, primaryjoin, uselist=False How to LIMIT to 1?
g00fy
on Mapper() i have relation() i use primaryjoin, and uselist=False,
but i also want to have LIMIT=1 on my join
it would be much faster!!
SA should add the limit itself when uselist=False
Michael Bayer
uselist=False is intended for a one-to-one relation where there is
only one child row referencing the parent row. It's not used to limit
the size of a collection that is otherwise many elements.
Since it seems you're wrestling with a large collection, your best
option may be to use a "dynamic" loader which allows any query
criterion to be used with an ordinary mapped attribute. This is
described in: http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_largecollections_dynamic
Another option would include setting up your primaryjoin to issue
criterion which matches the exact row you're looking for. Here's an
example:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata =MetaData(engine)
item = Table('item', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('parent.id'))
)
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
)
metadata.create_all()
engine.execute("insert into parent values(1)")
engine.execute("insert into parent values(2)")
engine.execute("insert into item values(1, 1)")
engine.execute("insert into item values(2, 1)")
engine.execute("insert into item values(3, 1)")
engine.execute("insert into item values(4, 1)")
engine.execute("insert into item values(5, 2)")
engine.execute("insert into item values(6, 2)")
engine.execute("insert into item values(7, 2)")
class Parent(object):
pass
class Item(object):
pass
mapper(Parent, parent, properties={
'item':relation(Item, uselist=False,
primaryjoin
=
item
.c
.id
=
=
select
([func
.max
(item
.c
.id
)]).where
(item.c.parent_id==parent.c.id).correlate(parent).as_scalar(),
viewonly=True
)
})
mapper(Item, item)
sess = create_session()
p2 = sess.query(Parent).get(2)
assert p2.item.id == 7
g00fy
mapper(Warehouse, warehouse_table,properties = {
'translation': relation(
WarehouseTranslation,
lazy = False,
uselist = False,
primaryjoin=
and_(
warehouse_table.c.id ==
warehouse_translation_table.c.warehouse_id,
warehouse_translation_table.c.language_id==common.get_language(),
# I want limit 1 here
),
),
.... rest relations
an aditional subselect will just make it work slower.
On 17 Paź, 21:57, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Oct 17, 2008, at 3:32 PM, g00fy wrote:
>
>
>
> > hi,
> > on Mapper() i have relation() i use primaryjoin, and uselist=False,
> > but i also want to have LIMIT=1 on my join
> > it would be much faster!!
> > SA should add the limit itself when uselist=False
>
> uselist=False is intended for a one-to-one relation where there is
> only one child row referencing the parent row. It's not used to limit
> the size of a collection that is otherwise many elements.
>
> Since it seems you're wrestling with a large collection, your best
> option may be to use a "dynamic" loader which allows any query
> criterion to be used with an ordinary mapped attribute. This is
Michael Bayer
On Oct 17, 2008, at 4:22 PM, g00fy wrote:
>
> I can't make this work with my relation:
>
>
> mapper(Warehouse, warehouse_table,properties = {
> 'translation': relation(
> WarehouseTranslation,
> lazy = False,
> uselist = False,
> primaryjoin=
> and_(
> warehouse_table.c.id ==
> warehouse_translation_table.c.warehouse_id,
>
> warehouse_translation_table.c.language_id==common.get_language(),
> # I want limit 1 here
> ),
> ),
> .... rest relations
> an aditional subselect will just make it work slower.
can you see how placing a LIMIT=1 there, especially if you're using
eager loading, will totally break even the most basic operation ?
Such as session.query(Warehouse).all() - you'll only get the first
Warehouse object. The LIMIT idea, without a subquery, can only work
at all in the general case if you're using lazy loading (and
relation() should be configured to work in the genral case). If you
want one query that loads Warehouse objects each with just the first
matching WarehouseTranslation, you have to use a subquery in any case,
even if LIMIT is used. That's just plain SQL.
However, if you really want to load exactly one Warehouse and exactly
one WarehouseTranslation, this is not SQLA default behavior and is
very unique; but its allowed, you just need to be explicit and
construct a query which states that exactly. The example I gave would
use a query like:
p2 = sess.query(Parent).join(Parent.items).\
options(contains_eager(Parent.items))[0]