79707842

Date: 2025-07-20 08:11:56
Score: 2
Natty:
Report link

Good day! (Sorry, my eng not very good)

Here is the solution using a subquery:

# subquery in SQL lang:
# SELECT children.parent_id, MIN(children.id) AS min_child_id
# FROM children
# GROUP BY children.parent_id
subq = (
    select(
        Child.parent_id,
        func.min(Child.id).label('min_child_id') # 
    )
    .group_by(Child.parent_id)
    .subquery()
)


# query in SQL lang:
# SELECT parents.id, ...
# FROM parents
# LEFT OUTER JOIN subquery AS sq ()
#   ON parents.id = sq.paparents_id
# LEFT OUTER JOIN children 
#   ON children.id = sq.min_child_id
#
results = (
    session.query(Parent, Child)
    .outerjoin(subq, Parent.id == subq.c.parent_id)
    .outerjoin(Child, Child.id == subq.c.min_child_id)
    .all()
)

Also below I will attach a screenshot of how the table connection works.(same color - connection keys)

union tables

Reasons:
  • Blacklisted phrase (1): Good day
  • Probably link only (1):
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Low reputation (1):
Posted by: Sindik