Count number of rows in a many-to-many relationship (SQLAlchemy)

Question

I have a many-to-many relationship between say blog entries and tags. Now I want to know how many entries a specific tag has.

Imagine the following models (simplified):

rel_entries_tags = Table('rel_entries_tags', Base.metadata,
  Column('entry_id', Integer, ForeignKey('entries.id')),
  Column('tag_id', Integer, ForeignKey('tags.id'))
)

class Entry(Base):
  __tablename__ = 'entries'

  id = Column(Integer, primary_key=True)
  title = Column(String(80))
  text = Column(Text)

  tags = relationship('Tag', secondary=rel_entries_tags, backref=backref('entries'))

  def __init__(self, title, text):
    self.title = title
    self.text = text
    self.tags = tags    

class Tag(Base):
  __tablename__ = 'tags'

  id = Column(Integer, primary_key=True)
  name = Column(String(80), unique=True, nullable=False)

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

My approach to count the amount of entries for a tag is len(db_session.query(Tag).get(1).entries). The problem is that when it gets db_session.query(Tag).get(1).entries SQLAlchemy selects all the entries with all their columns for a tag, however, I want only the amount of the entries, not the entries themselves. Is there a more optimal approach for this problem?

Thanks.

score 19 · Accepted Answer · answered Jul 19 '12 at 20:01

19

session.query(Entry).join(Entry.tags).filter(Tag.id==1).count()

or if you have a Tag already

session.query(Entry).with_parent(mytag, "entries").count()

answered Jul 19 '12 at 20:01

zzzeek

72,307
23
193
185

4

+1: and if you need this often, you can create a property: `@property\n def entries_cnt(self): \n return Session.object_session(self).query(Entry).with_parent(self, "entries").count() ` – van Jul 20 '12 at 08:13
Thanks for this answer. However, the produced SQL statement is `SELECT count(*) AS count_1 FROM (SELECT order_line.id AS order_line_id, order_line.order_id AS order_line_order_id FROM order_line WHERE %(param_1)s = order_line.order_id) AS anon_1` In other words - instead of one `SELECT count(*) FROM order_line WHERE order_line.order_id = %(param_1)s` we get an inner SELECT. In my case, it's not one-to-many (Order has many OrderLine). – Guy Aug 02 '16 at 13:23
do a query(func.count('*')) up front. the [count() documentation](http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query.count#sqlalchemy.orm.query.Query.count) refers to this. – zzzeek Aug 05 '16 at 21:03
It should also be OK to use `session.query(Entry).with_parent(mytag).count()`. [The `property` parameter can be omitted](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_parent.params.property) – nalzok Jul 17 '17 at 07:05

Count number of rows in a many-to-many relationship (SQLAlchemy)

1 Answers1

Linked