I think I almost have it, but my sub query seems to get a total count of every object. Not a count of the objects that contain a specific ID. Could someone help me fix it so it only counts the objects that all have the same ID? Note: This is made more complicated because the ID I want to check is in a JSONB.
I have two tables (I'm going to greatly simplfy what we are working with) Table 1: Company
- id
- name
- ect..
Table 2: MessageData:
- id
- data (A json blob that might or might not contain an field called company_id)
- ect..
I eventually want my query to return a data set like so:
ID : 00000000-0000-0000-0000-000000000000
Name: Random Company Name here
Message Count: 12
My current sub query looks something like this (based on this other stack overflow question SQL Query that relies on COUNT from another table? - SQLAlchemy)
session.query(MessageData.data['company_id'].label('company_id'), func.count('*').label('message_count')).group_by(MessageData.data['company_id']).subquery()
My main query is looking like this
Edit with solution: I had to add the outerjoin like in the example
session.query(Company.id, Company.name, subquery.c.message_count).outerjoin(subquery, subquery.c.company_id == Company.id).all()
But the message count always has the same value, which I assume is a count of every instance of MessageData, and not a count of just the ones that contain that Company's ID.