How to count related objects from a many to many relations using subquery

[Mario César]

Hello All,

I am trying to count related objects, I have an Option (understand
option as a party) and Candidate Table, related with an many to many
relation, Above of this message y attach a python script trying to do
that, my issue is in following lines

subquery = session.query('option_senators', func.count('*').label
('senators_count')).group_by('option_senators.option_id').subquery()
query = session.query(Option)
query.outerjoin((subquery, Option.id == subquery.c.option_id)) #
<--- Here it raise the error
query.order_by(Option.name)
query.all()

In my understanding, I have to do an outerjoin with the Option table
and a subquery that holds something like this:

SELECT option_senators.option_id, count( * ) AS senators_count
FROM option_senators
GROUP BY option_senators.option_id

The result I expect is having the following set:

id|name|created|updated|senators_count
1|Option X|2009-09-11 13:07:37.604377|2009-09-11 13:07:37.604377|
100

Sure I am missing something, in the moment it tried doing the join,
raises:

Traceback (most recent call last):
File "mm.py", line 60, in <module>
query.outerjoin((subquery, Option.id == subquery.c.option_id))
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/util.py", line 649, in __getattr__
raise AttributeError(key)
AttributeError: option_id

How can accomplish Getting the count of the related senators for each
Option?

Here is the script I am using to test

#!/usr/bin/python
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.sql import func

from datetime import datetime

Base = declarative_base()
metadata = Base.metadata

option_senators = Table('option_senators', metadata,
Column('option_id', Integer, ForeignKey('options.id')),
Column('candidate_id', Integer, ForeignKey('candidates.id')),
Column('departament', Integer),
)

class Candidate(Base):
__tablename__ = 'candidates'

id = Column(Integer, primary_key=True)
first_name = Column(String(255))
last_name = Column(String(255))

def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name

class Option(Base):
__tablename__ = 'options'

id = Column(Integer, primary_key=True)
name = Column(String(255))
senators = relation('Candidate', secondary=option_senators,
backref="senator_option")
created = Column(DateTime, default=datetime.now)
updated = Column(DateTime, default=datetime.now)

def __init__(self, name):
self.name = name

if __name__ == '__main__':
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///hola.bd', echo=True)
#engine = create_engine('mysql://votodb:votodb@localhost/votodb',
echo=True)
metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

option = Option('Option X')
for n in range(100):
option.senators.append(Candidate('Jhon %s' % n, 'Doe %s' % n))

session.add(option)
session.commit()

subquery = session.query('option_senators', func.count('*').label
('senators_count')).group_by('option_senators.option_id').subquery()
query = session.query(Option)
query.outerjoin((subquery, Option.id == subquery.c.option_id))
query.order_by(Option.name)

query.all()

[Conor]

I believe what you want is:
q = session.query(Option, func.count())
q = q.outerjoin((option_senators, option_senators.c.option_id ==
Option.id))
q = q.group_by(Option.id)
q = q.order_by(Option.name)

which will generate this SQL:
SELECT [...], count(*) AS count_1
FROM options LEFT OUTER JOIN option_senators ON
option_senators.option_id = options.id GROUP BY options.id ORDER BY
options.name

Note that query method (such as outerjoin and order_by) do not modify
the query: they return a modified copy.

-Conor

[Mario César]

On Sep 11, 5:03 pm, Conor <conor.edward.da...@gmail.com> wrote:
> I believe what you want is:
> q = session.query(Option, func.count())
> q = q.outerjoin((option_senators, option_senators.c.option_id ==
> Option.id))
> q = q.group_by(Option.id)
> q = q.order_by(Option.name)
>
> which will generate this SQL:
> SELECT [...], count(*) AS count_1
> FROM options LEFT OUTER JOIN option_senators ON
> option_senators.option_id = options.id GROUP BY options.id ORDER BY
> options.name

Thank you, I was not understanding well.

:-) Works great