Symfony中三表Doctrine映射及复选框循环展示数据问题

Hey there! I see you're looking to populate your location_location_amenity (T3) junction table with every possible combination of entries from location (T1) and location_amenity (T2). This is a classic case where we can leverage a cross join to generate the Cartesian product of the two tables—perfect for your scenario where every location should be linked to every amenity.

Basic Solution (Insert All Combinations) #

If your T3 table is empty and you just need to create all possible pairs, use this SQL query:

INSERT INTO location_location_amenity (location_id, location_amenity_id)
SELECT l.id, la.id
FROM location l
CROSS JOIN location_amenity la;

This will take every row from location and pair it with every row from location_amenity, which exactly matches the sample T3 data you provided (2 locations × 2 amenities = 4 rows).

Avoiding Duplicate Entries #

If T3 might already have some records and you want to skip duplicates (to follow DRY and prevent redundant data), add a NOT EXISTS check:

INSERT INTO location_location_amenity (location_id, location_amenity_id)
SELECT l.id, la.id
FROM location l
CROSS JOIN location_amenity la
WHERE NOT EXISTS (
    SELECT 1
    FROM location_location_amenity t3
    WHERE t3.location_id = l.id 
      AND t3.location_amenity_id = la.id
);

This ensures you only insert pairs that aren't already present in T3, keeping your data clean and adhering to DRY principles by avoiding manual, repetitive inserts.

Why This Works #

Cross joins are designed explicitly for generating all combinations between two datasets—no need to write individual INSERT statements for each pair. This approach is scalable too: if you add more locations or amenities later, running this query again (with the duplicate check) will automatically add the new combinations without extra work.

内容的提问来源于stack exchange，提问作者Suhail Saud Khan