0

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

  1. id
  2. name
  3. ect..

Table 2: MessageData:

  1. id
  2. data (A json blob that might or might not contain an field called company_id)
  3. 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.

1 Answer 1

0

I had to change my main query. Edited above

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.