Lazy count on relations
Let's say I have the following mapping:
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.sql import func
engine = create_engine('sqlite://')
metadata = MetaData()
metadata.bind = engine
Session = scoped_session(sessionmaker(bind = engine, autoflush = True,
autocommit = False))
# TABLES
owners_table = Table('owners', metadata,
Column('owner_id', Integer, primary_key=True)
)
dogs_table = Table('dogs', metadata,
Column('dog_id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey(owners_table.columns.owner_id))
)
metadata.create_all()
engine.execute(owners_table.insert(), [{'owner_id':1}]({'owner_id':1}))
engine.execute(owners_table.insert(), [{'owner_id':2}]({'owner_id':2}))
engine.execute(owners_table.insert(), [{'owner_id':3}]({'owner_id':3}))
engine.execute(dogs_table.insert(), ['dog_id':1}]({'owner_id':1,))
engine.execute(dogs_table.insert(), ['dog_id':2}]({'owner_id':1,))
engine.execute(dogs_table.insert(), ['dog_id':3}]({'owner_id':2,))
# POJOS
class Owner(object):
pass
class Dog(object):
pass
# MAPPERS
owners2dogs_relation = relation(Dog,
backref = 'owner')
Session.mapper(Owner, owners_table, properties = {'dogs' : owners2dogs_relation})
Session.mapper(Dog, dogs_table)
s = Session()
Now I selected an owner, and I want to know how many dogs he have. I don't want to know the identity of the dogs, I just want their number. Meaning - I don't want to load them.
I would be thrilled to do something like:
s.lazy_count(my_owner, Owner.dogs)
This function will check if my_owner dogs are already loaded. If so, it will return the len of the list, otherwise it will issue a smart count query, that won't load the dogs.
It will make my life easier, and will also allow me to change my implementation to a Many-to-many relationship, without even changing my queries.
Comments (4)
-
Account Deleted -
repo owner - changed status to wontfix
this is also docuemnted in the API docs for
Query
:session.query(func.count(Dog.id)).with_parent(my_owner, 'dogs').value()
-
Account Deleted Forgot to thank you about it.
Also, is there a way to check if a lazy attribute is already loaded other than checkhing if it appears in the object dict? Because if not, there should be, because I don't think it's trivial to look at the dict, and also it may be a problem when changing the attribute name, because checking the dict will always return false, although the attribute doesn't even exist (it exists in a different name)?
It relates to my ticket, because as I said if the dogs of my_owner are already loaded, I don't want to query the DB and prefer using len(my_owner.dogs), and your solution just check the db.
-
repo owner why isn't it trivial to look in dict ? too much typing? thats what defs are for ?
I also don't understand what "changing the attribute name" means. mappers don't allow ad-hoc modifications of mapped attribute names.
- Log in to comment
submitted by kobipe3@gmail.com