Sort by Count of Many to Many Relationship - SQLAlchemy

Question

I am using Flask-SQLAlchemy to to query my Postgres database.

I am currently trying to query for suggestions of titles with the following query:

res = Title.query.filter(Titles.name.ilike(searchstring)).limit(20)

So far so good.

Now I would like to order the results by the number of "subscribers" each Title object has.

I am aware of the following SO question: SQLAlchemy ordering by count on a many to many relationship however its solution did not work for me.

I am receiving the following error:

ProgrammingError: (ProgrammingError) column "publishers_1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...itles_name, count(titles_users.user_id) AS total, publishers...

(Publisher has a one-to-many relationship with Title models)

I understand that the answer has something to do with subqueries

Below is a simplified example of my two models.

# Many-to-Many relationship for user and titles
titles_users = db.Table('titles_users',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('title_id', db.Integer, db.ForeignKey('titles.id'))
)

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    # ids
    id = db.Column(db.Integer, primary_key=True)
    # Attributes
    email = db.Column(db.String(255), unique=True)
    full_name = db.Column(db.String(255))

    pull_list = db.relationship(
        'Title',
        secondary=titles_users,
        backref=db.backref('users', lazy='dynamic'),
        lazy='joined'
    )


class Title(db.Model):
    __tablename__ = 'titles'
    #: IDs
   id = db.Column(db.Integer(), primary_key=True)
   #: Attributes
   name = db.Column(db.String(255))

Can you tell us why solution in linked SO question didn't work for you? Were you getting any errors? — Audrius Kažukauskas, Jul 04 '14 at 12:26

score 4 · Answer 1 · answered Jul 07 '14 at 16:30

Code below would work for the model you describe:

q = (db.session.query(Title, func.count(names_users.c.user_id).label("total"))
        .filter(Title.name.ilike(searchstring))
        .outerjoin(names_users).group_by(Title).order_by('total DESC')
    )
for x in q:
    print(x)

Your error, however, includes some other data like publisher or like. So if above is not helpful, you should add more information to your question.

Sort by Count of Many to Many Relationship - SQLAlchemy

1 Answers1

Linked