Is it possible to join a table with itself twice? (Using SQLAlchemy or SQL)

I have a following model in SQLAlchemy:

    class SomeEvent(db.Model):
        id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
        created_on = db.Column(db.DateTime())
        type = db.Column(db.String(20))
        event_target = db.Column(db.String(10))
        group = db.Column(db.String(20))

Is there any way to produce a query that will return a result consisting of all records of one type + records of two other types (if these two records have same event_target else just records of a single type) of the same group ordered by created_on date?

What I’ve managed to come up with:

        Single_type_event = aliased(SomeEvent)
        Paired_type_event_1 = aliased(SomeEvent)
        Paired_type_event_2 = aliased(SomeEvent)

        columns = [
            Single_type_event.id.label('single_type_event_id'),
            Single_type_event.type.label('single_type_event_type'),
            Single_type_event.event_target.label('single_type_event_target'),
            Paired_type_event_1.id.label('paired_type_event_1_id'),
            Paired_type_event_1.type.label('paired_type_event_1_type'),
            Paired_type_event_1.event_target.label('paired_type_event_1_target'),
            Paired_type_event_2.id.label('paired_type_event_2_id'),
            Paired_type_event_2.type.label('paired_type_event_2_type'),
            Paired_type_event_2.event_target.label('paired_type_event_2_target'),
        ]

        query = (db.session.query(*columns)
                .outerjoin(SomeEvent, (
                    (SomeEvent.group == 'some_group') & 
                    (SomeEvent.id == Single_type_event.id) |
                    (SomeEvent.id == Paired_type_event_1.id) |
                    (SomeEvent.id == Paired_type_event_2.id)
                ))
                .outerjoin(Single_type_event, (
                    (SomeEvent.id == Single_type_event.id) &
                    (SomeEvent.event_target == Single_type_event.event_target) &
                    (SomeEvent.type == 'type_1')
                ))
                .outerjoin(Paired_type_event_1, (
                    (SomeEvent.id == Paired_type_event_1.id) &
                    (SomeEvent.event_target == Paired_type_event_1.event_target) &
                    (SomeEvent.type == 'type_2')
                ))
                .outerjoin(Paired_type_event_2, (
                    (SomeEvent.id == Paired_type_event_2.id) &
                    (SomeEvent.event_target == Paired_type_event_2.event_target) &
                    (SomeEvent.type == 'type_3')
                ))
                .order_by(SomeEvent.created_on.asc())
        )

But of course it doesn’t work, because I’m lacking the understanding of how to do this right, if it’s even possible =(

Example of the query results that I would like to achieve(._asdict() applied):

1. If a record with a given event_target has type == 'type_1':

    {'single_type_event_id': 'some_id',
    'single_type_event_type': 'type_1',
    'single_type_event_target': 'target_1',
    'paired_type_event_1_id': None,
    'paired_type_event_1_type': None,
    'paired_type_event_1_target': None,
    'paired_type_event_2_id': None,
    'paired_type_event_2_type': None,
    'paired_type_event_2_target': None}

2. If there is only a record with type == 'type_2' for a given event_target (still it could have a type_1 record earlier but it should be in a distinct row like the one before):

    {'single_type_event_id': None,
    'single_type_event_type': None,
    'single_type_event_target': None,
    'paired_type_event_1_id': 'some_id',
    'paired_type_event_1_type': 'type_2',
    'paired_type_event_1_target': 'target_1',
    'paired_type_event_2_id': None,
    'paired_type_event_2_type': None,
    'paired_type_event_2_target': None}

3. And, finally, if there are records of both event types for a given event_target (there shouldn’t be distinct rows for each type – only this combined one):

    {'single_type_event_id': None,
    'single_type_event_type': None,
    'single_type_event_target': None,
    'paired_type_event_1_id': 'some_id_1',
    'paired_type_event_1_type': 'type_2',
    'paired_type_event_1_target': 'target_1',
    'paired_type_event_2_id': 'some_id_2',
    'paired_type_event_2_type': 'type_3',
    'paired_type_event_2_target': 'target_1'}

I would like to have all these results having the same group and ordered by created_on (for the last example it should be ordered by the paired_type_event_1 date).

An abstract example: a person_1(event_target) is living in a town_a(group). He has a record of when he was born(type == 'birth') and this is his single_type_event (first case of the results from above). This person_1 also has a record that he started school(type == 'enrollment'), but he doesn’t have a record of graduation. This is his paired_type_event_1 (second case of the results). If this person had a graduation record (type == 'graduation') then it would have been presented in the same row with the enrollment record. Assuming that this table is actually a paper record book for the town_a these records should be organized in the following order: born (a single row) -> enrolled (another row) (born (a single row) -> enrolled+graduated (another single row) for a person who graduated)

I know that it looks like a mess and I have some solid doubts about this, but if it’s possible to achieve in SQLAlchemy or using raw SQL I would be very thankful for the guidance!

About Steffy Alen

administrator