10·
20 days agoOh god, that’s worse than I’ve seen where a SQL query joining 10 tables aliased all of the tables as a, b, c, d, e, f, g, h, i, j.
It was a mess, and as a new dev on the project, trying to figure out which where clause was for which table and how things worked was a fucking nightmare. Trying to keep a dictionary of letters to real table names in your head as you looked at the query was very taxing. In the end, I just fixed it all to stop using aliases. Or to use short abbreviations.
Here’s a mock example:
SELECT
j.delivery_eta,
c.cat_desc,
a.part_number,
h.region_label,
f.wh_loc,
e.emp_last,
g.state_flag,
b.mfg_title,
i.ship_track_code,
d.order_sum,
a.created_on,
j.last_scanned_at,
e.emp_first,
c.cat_code,
g.state_level
FROM parts AS a
INNER JOIN manufacturers AS b
ON a.manufacturers_id = b.id
INNER JOIN categories AS c
ON a.categories_id = c.id
INNER JOIN orders AS d
ON a.orders_id = d.id
INNER JOIN employees AS e
ON d.employees_id = e.id
INNER JOIN warehouses AS f
ON a.warehouses_id = f.id
INNER JOIN inv_state AS g
ON a.inv_state_id = g.id
INNER JOIN regions AS h
ON f.regions_id = h.id
INNER JOIN shipments AS i
ON d.shipments_id = i.id
INNER JOIN logistics AS j
ON i.logistics_id = j.id
WHERE
(b.mfg_title LIKE '%Corp%' OR b.mfg_title LIKE '%Global%')
AND c.cat_desc NOT IN ('Unknown', 'None', 'Legacy')
AND (d.order_sum > 1000 OR d.order_sum BETWEEN 250 AND 275)
AND e.emp_last ILIKE '%berg'
AND (f.wh_loc IN ('A1', 'Z9', 'M3') OR f.wh_loc IS NULL)
AND g.state_flag IN ('ACT', 'PENDING')
AND h.region_label NOT LIKE 'EXT-%'
AND (i.ship_track_code IS NOT NULL AND i.ship_track_code <> '')
AND (j.delivery_eta < NOW() + INTERVAL '90 days' OR j.last_scanned_at IS NULL)
AND (a.part_number ~ '^[A-Z0-9]+$' OR a.part_number IS NULL)
AND (
(c.cat_code = 'X1' AND g.state_level > 2)
OR
(e.emp_first ILIKE 'J%' AND d.orders_id IS NOT NULL)
);
Checks out.