Get compatible components for the build

Actors

User, Admin

Scenario

1. In the forge, the user clicks on the type of component they want to add, and depending on what other components are already in the build only compatible ones will be displayed

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  cpu.socket, cpu.cores, cpu.threads, cpu.base_clock, cpu.boost_clock, cpu.tdp
FROM components c
JOIN cpu ON cpu.component_id = c.id
WHERE c.type = 'cpu'
  AND cpu.socket = $motherboardSocket
  AND cpu.tdp <= $coolerMaxTdpSupported
  AND EXISTS (
     SELECT 1 FROM cooler_cpu_sockets s
     WHERE s.cooler_id = $coolerId AND s.socket = cpu.socket
  )
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  m.socket, m.chipset, m.form_factor, m.ram_type, m.num_ram_slots, m.max_ram_capacity, m.pci_express_slots
FROM components c
JOIN motherboard m ON m.component_id = c.id
WHERE c.type = 'motherboard'
  AND m.socket = $cpuSocket
  AND m.ram_type = $ramType
  AND m.pci_express_slots >= $pciExpressSlotsUsed
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  co.type AS cooler_type, co.height, co.max_tdp_supported
FROM components c
JOIN cooler co ON co.component_id = c.id
WHERE c.type = 'cooler'
  AND co.max_tdp_supported >= $cpuTdp
  AND co.height <= $caseCoolerMaxHeight
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  g.vram, g.tdp, g.base_clock, g.boost_clock, g.chipset, g.length
FROM components c
JOIN gpu g ON g.component_id = c.id
WHERE c.type = 'gpu'
  AND g.length <= $caseGpuMaxLength
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  mem.type AS memory_type, mem.speed, mem.capacity, mem.modules
FROM components c
JOIN memory mem ON mem.component_id = c.id
WHERE c.type = 'memory'
  AND mem.type = $motherboardRamType
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  s.type AS storage_type, s.capacity, s.form_factor
FROM components c
JOIN storage s ON s.component_id = c.id
WHERE c.type = 'storage'
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  pc.cooler_max_height, pc.gpu_max_length
FROM components c
JOIN pc_case pc ON pc.component_id = c.id
WHERE c.type = 'case'
  AND pc.gpu_max_length >= $gpuLength
  AND pc.cooler_max_height >= $coolerHeight
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  ps.type AS psu_type, ps.wattage, ps.form_factor
FROM components c
JOIN power_supply ps ON ps.component_id = c.id
WHERE c.type = 'power_supply'
  AND ps.wattage >= ($existingTDP * 1.2)
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  nc.num_ports, nc.speed, nc.interface
FROM components c
JOIN network_card nc ON nc.component_id = c.id
WHERE c.type = 'network_card'
  AND ($pciExpressSlotsUsed + 1) <= $motherboardPciExpressSlots
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  na.wifi_version, na.interface, na.num_antennas
FROM components c
JOIN network_adapter na ON na.component_id = c.id
WHERE c.type = 'network_adapter'
  AND ($pciExpressSlotsUsed + 1) <= $motherboardPciExpressSlots
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

SELECT
  c.id, c.name, c.brand, c.price, c.img_url, c.type,
  sc.sample_rate, sc.bit_depth, sc.chipset, sc.interface, sc.channel
FROM components c
JOIN sound_card sc ON sc.component_id = c.id
WHERE c.type = 'sound_card'
  AND ($pciExpressSlotsUsed + 1) <= $motherboardPciExpressSlots
ORDER BY
  CASE WHEN $sort = 'price_asc'  THEN c.price END ASC,
  CASE WHEN $sort = 'price_desc' THEN c.price END DESC,
  c.price DESC
LIMIT COALESCE($limit, 100);

For example

This is the list of all CPUs

If the user selects the first motherboard (as in the image below)

The list of CPUs updates based on the Motherboard's CPU socket and shows the user only the compatible CPUs