0

I'm trying to write a query using the Peewee ORM. The query should return all names of Users which have at least one message associated with them. I currently try this as follows:

usersWithAtLeastOneMessage = User.select().where((fn.Count(User.messages) > 0)
for u in usersWithAtLeastOneMessage: 
    print u.name

in which my models look like this:

class User(db.Model):
    name = CharField()

class Message(db.Model):
    user = ForeignKeyField(User, related_name='messages')
    text = TextField()

This however, gives me the following error: OperationalError: misuse of aggregate function Count().

I'm totally lost here though. Does anybody know how I can fix this? All tips are welcome!

kramer65
  • 50,427
  • 120
  • 308
  • 488

2 Answers2

0

Try using the following statement, which should do what you're looking for.

User.select().where(fn.Exists(Message.select().where(Message.user == User.id)))
nKn
  • 13,691
  • 9
  • 45
  • 62
coleifer
  • 24,887
  • 6
  • 60
  • 75
0

If you want to filter by aggregates, you need to

a) add a valid group_by() clause

b) do the filtering in a having() clause

like in

User.select().group_by(User).having((fn.Count(User.messages) > 0)
Martin B.
  • 1,928
  • 12
  • 24