多表关联SQL查询：获取带关联信息且按有效日期排序的事件

Got it, let's build the query you need. The goal is to retrieve all events linked to their category, associated promoters, and only include events that have upcoming dates (greater than today), sorted by the earliest upcoming date.

Approach Breakdown #

1. Link Events to Category: Since each event maps to exactly one category, a simple INNER JOIN between events and categories works.
2. Filter Upcoming Dates: Use a join to event_date and dates, with a condition to only include dates where startdate is after today. We'll also use an EXISTS clause to ensure we only keep events that have at least one upcoming date.
3. Aggregate Promoters & Dates: To avoid duplicate event rows (from multiple promoters/dates), we'll use subqueries with JSON aggregation functions to bundle all promoters and upcoming dates into arrays for each event.
4. Sort by Date: Order results by the earliest upcoming date of each event.

Example for PostgreSQL #

PostgreSQL uses json_agg to aggregate related data into JSON arrays:

SELECT 
    e.id,
    e.title,
    c.name AS category,
    -- Aggregate all promoters for the event
    (SELECT json_agg(p) 
     FROM event_promoter ep 
     JOIN promoters p ON ep.promoter_id = p.id 
     WHERE ep.event_id = e.id) AS promoters,
    -- Aggregate only upcoming dates
    (SELECT json_agg(d) 
     FROM event_date ed 
     JOIN dates d ON ed.date_id = d.id 
     WHERE ed.event_id = e.id AND d.startdate > CURRENT_DATE) AS upcoming_dates
FROM events e
INNER JOIN categories c ON e.category_id = c.id
-- Ensure we only include events with at least one upcoming date
WHERE EXISTS (
    SELECT 1 
    FROM event_date ed 
    JOIN dates d ON ed.date_id = d.id 
    WHERE ed.event_id = e.id AND d.startdate > CURRENT_DATE
)
-- Sort by the earliest upcoming date of each event
ORDER BY (
    SELECT MIN(d.startdate) 
    FROM event_date ed 
    JOIN dates d ON ed.date_id = d.id 
    WHERE ed.event_id = e.id AND d.startdate > CURRENT_DATE
) ASC;

Example for MySQL #

MySQL uses JSON_ARRAYAGG and JSON_OBJECT to structure aggregated data:

SELECT 
    e.id,
    e.title,
    c.name AS category,
    -- Aggregate promoters into a JSON array of objects
    (SELECT JSON_ARRAYAGG(
        JSON_OBJECT('id', p.id, 'name', p.name, 'avatar', p.avatar)
     ) FROM event_promoter ep 
     JOIN promoters p ON ep.promoter_id = p.id 
     WHERE ep.event_id = e.id) AS promoters,
    -- Aggregate upcoming dates into a JSON array of objects
    (SELECT JSON_ARRAYAGG(
        JSON_OBJECT('id', d.id, 'startdate', d.startdate, 'enddate', d.enddate)
     ) FROM event_date ed 
     JOIN dates d ON ed.date_id = d.id 
     WHERE ed.event_id = e.id AND d.startdate > CURDATE()) AS upcoming_dates
FROM events e
INNER JOIN categories c ON e.category_id = c.id
-- Filter events with upcoming dates
WHERE EXISTS (
    SELECT 1 
    FROM event_date ed 
    JOIN dates d ON ed.date_id = d.id 
    WHERE ed.event_id = e.id AND d.startdate > CURDATE()
)
-- Sort by earliest upcoming date
ORDER BY (
    SELECT MIN(d.startdate) 
    FROM event_date ed 
    JOIN dates d ON ed.date_id = d.id 
    WHERE ed.event_id = e.id AND d.startdate > CURDATE()
) ASC;

Key Notes #

• Avoiding Duplicates: Using subqueries for aggregation prevents duplicate event rows that would happen if we joined all tables directly.
• Date Functions: CURRENT_DATE (PostgreSQL) and CURDATE() (MySQL) get today's date—adjust if your database uses a different function (e.g., GETDATE() for SQL Server).
• NULL Handling: If a promoter has no avatar, the JSON will include null for that field, which is standard behavior.

内容的提问来源于stack exchange，提问作者natas