PostgreSQL 一张表多个字段关联另一张表

event_catalog 表

event 表

sql

SELECT event.event_uuid, event.event_case_id, event.event_status, event.event_catalog_1, event.event_catalog_2, event.event_catalog_3, event.event_title, event.event_content, event.event_source, event.event_purpose, event.event_sentiment, event.event_reply_content, event.event_is_private, event.event_data_source_id, event.event_type_id, event.event_satisfication, event.create_time, event.update_time, event.complete_time, actor.actor_uuid, actor.actor_name, actor.actor_gender, actor.actor_tel, actor.actor_email, actor.actor_id, actor.actor_address, actor.create_time AS create_time_1, actor.update_time AS update_time_1, place.place_uuid, place.place_name, place.place_formatted_name, place.place_address, place.place_region_id, place.place_lat, place.place_lng, place.place_type_id, place.place_area, place.place_contact, place.place_phone, place.create_time AS create_time_2, place.update_time AS update_time_2, data_source.data_source_id, data_source.data_source_name, data_source.data_source_department, data_source.data_source_enabled, data_source.data_source_start_time, data_source.create_time AS create_time_3, data_source.update_time AS update_time_3, event_type.event_type_id AS event_type_id_1, event_type.event_type_name, event_type.create_time AS create_time_4, event_type.update_time AS update_time_4, region.region_id, region.region_name, region.create_time AS create_time_5, region.update_time AS update_time_5, event_catalog_1.event_catalog_id, event_catalog_1.event_catalog_name, event_catalog_1.event_catalog_type, event_catalog_1.create_time AS create_time_6, event_catalog_1.update_time AS update_time_6, event_catalog_1.event_catalog_parent_id, event_catalog_1.event_catalog_id_str, event_catalog_1.event_catalog_data_source_id, event_catalog_2.event_catalog_id AS event_catalog_id_1, event_catalog_2.event_catalog_name AS event_catalog_name_1, event_catalog_2.event_catalog_type AS event_catalog_type_1, event_catalog_2.create_time AS create_time_7, event_catalog_2.update_time AS update_time_7, event_catalog_2.event_catalog_parent_id AS event_catalog_parent_id_1, event_catalog_2.event_catalog_id_str AS event_catalog_id_str_1, event_catalog_2.event_catalog_data_source_id AS event_catalog_data_source_id_1, event_catalog_3.event_catalog_id AS event_catalog_id_2, event_catalog_3.event_catalog_name AS event_catalog_name_2, event_catalog_3.event_catalog_type AS event_catalog_type_2, event_catalog_3.create_time AS create_time_8, event_catalog_3.update_time AS update_time_8, event_catalog_3.event_catalog_parent_id AS event_catalog_parent_id_2, event_catalog_3.event_catalog_id_str AS event_catalog_id_str_2, event_catalog_3.event_catalog_data_source_id AS event_catalog_data_source_id_2, calendar.calendar_uuid, calendar.calendar_type, calendar.calendar_name, calendar.calendar_period_type, calendar.calendar_start_time, calendar.calendar_end_time, calendar.del_flag, calendar.create_time AS create_time_9, calendar.update_time AS update_time_9, event_source.event_source_id, event_source.event_source_name, event_source.create_time AS create_time_10, event_source.update_time AS update_time_10
FROM event 
JOIN event_calendar_r ON event.event_uuid = event_calendar_r.event_calendar_event_uuid 
JOIN calendar ON calendar.calendar_uuid = event_calendar_r.event_calendar_calendar_uuid 
JOIN event_actor_r ON event.event_uuid = event_actor_r.event_uuid 
JOIN actor ON actor.actor_uuid = event_actor_r.actor_uuid 
LEFT OUTER JOIN event_place_r ON event.event_uuid = event_place_r.event_uuid 
LEFT OUTER JOIN place ON place.place_uuid = event_place_r.place_uuid 
LEFT OUTER JOIN data_source ON data_source.data_source_id = event.event_data_source_id 
LEFT OUTER JOIN event_type ON event_type.event_type_id = event.event_type_id 
LEFT OUTER JOIN region ON region.region_id = place.place_region_id 
JOIN event_source ON event_source.event_source_id = event.event_source 

LEFT OUTER JOIN event_catalog AS event_catalog_1 ON event_catalog_1.event_catalog_id = event.event_catalog_1 
LEFT OUTER JOIN event_catalog AS event_catalog_2 ON event_catalog_2.event_catalog_id = event.event_catalog_2 
LEFT OUTER JOIN event_catalog AS event_catalog_3 ON event_catalog_3.event_catalog_id = event.event_catalog_3

WHERE event.create_time >= '2021-02-21T16:00:00.000Z' 
AND event.create_time <= '2024-03-22T15:59:59.999Z'
AND calendar.del_flag = 0 
AND event.event_catalog_1 IN (1573) 
AND event.event_catalog_2 IN (1789) 
AND event.event_catalog_3 IN (8242) 
AND event.event_type_id IN (3) 
AND event.event_catalog_1 IS NOT NULL 
AND event.event_catalog_2 IS NOT NULL 
AND event.event_catalog_3 IS NOT NULL 
AND calendar.calendar_name IN ('中秋节、国庆节')

sqlachemy

from sqlalchemy.orm import aliased

table_event_catalog_1 = aliased(models.EventCatalog)
table_event_catalog_2 = aliased(models.EventCatalog)
table_event_catalog_3 = aliased(models.EventCatalog)
stmt = select(
    models.Event,
    models.Actor,
    models.Place,
    models.DataSource,
    models.EventType,
    models.Region,
    table_event_catalog_1,
    table_event_catalog_2,
    table_event_catalog_3,
    models.Calendar,
    models.EventSource,
).select_from(models.Event)

stmt = (
    stmt.join(models.EventCalendarR)
        .join(models.Calendar)
        .join(models.EventActorR)
        .join(models.Actor)
        .join(models.EventPlaceR, isouter=True)
        .join(models.Place, isouter=True)
        .join(models.DataSource, isouter=True)
        .join(models.EventType, isouter=True)
        .join(models.Region, isouter=True)
        .join(models.EventSource)
        .join(
        table_event_catalog_1,
        table_event_catalog_1.event_catalog_id == models.Event.event_catalog_1,
        isouter=True,
    )
        .join(
        table_event_catalog_2,
        table_event_catalog_2.event_catalog_id == models.Event.event_catalog_2,
        isouter=True,
    )
        .join(
        table_event_catalog_3,
        table_event_catalog_3.event_catalog_id == models.Event.event_catalog_3,
        isouter=True,
    )
        .filter(and_(True, *expressions))
)

results = db_session.execute(stmt).all()