Lazy count on relations

[Former user]

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.

[Former user]

submitted by [e-mail]

[Mike Bayer]

this is also docuemnted in the API docs for Query:

session.query(func.count(Dog.id)).with_parent(my_owner, 'dogs').value()

[Former user]

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.

[Mike Bayer]

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.