Changes between Initial Version and Version 1 of GetCompatibleComponents

Timestamp:

12/29/25 01:00:47 (23 hours ago)

Author:

233051

Comment:

--

GetCompatibleComponents

@@ +1 @@
+== 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);
+}}}