How to query MySQL table with OR condition on two columns while prioritizing one column

[Mike71]

I’m building a product recommendation system using a relationship table that stores connected item IDs.

My database has a simple two-column structure where both fields store item identifiers:

CREATE TABLE IF NOT EXISTS `item_relations` (
  `primary_id` int(11) NOT NULL,
  `related_id` int(11) NOT NULL
);

INSERT INTO `item_relations` (`primary_id`, `related_id`) VALUES
(3, 8),
(3, 12),
(8, 9),
(8, 11),
(11, 3),
(11, 9);

I need to fetch 3 related items for a given product ID, but I want to prioritize results where the target ID appears in the first column (primary_id) over matches in the second column (related_id).

SELECT * FROM item_relations WHERE primary_id={$product_id} OR related_id={$product_id}
ORDER BY ??? 
LIMIT 3

What’s the best way to structure the ORDER BY clause to achieve this priority?

[FlyingLeaf]

Use a CASE statement in your ORDER BY clause to prioritize results where the product ID appears in the primary_id column. Just assign different sorting weights based on which column contains your product ID:

SELECT * FROM item_relations 
WHERE primary_id = {$product_id} OR related_id = {$product_id}
ORDER BY CASE WHEN primary_id = {$product_id} THEN 0 ELSE 1 END
LIMIT 3

This’ll show primary_id matches first, then related_id matches. Simple and works great for recommendation systems.

[emmad]

You could add a calculated field to show the relationship direction while sorting. Makes the query more readable and gives you extra data for your recommendation logic:

SELECT *, 
       CASE WHEN primary_id = {$product_id} THEN related_id ELSE primary_id END as recommended_item,
       CASE WHEN primary_id = {$product_id} THEN 'primary' ELSE 'secondary' END as relation_type
FROM item_relations 
WHERE primary_id = {$product_id} OR related_id = {$product_id}
ORDER BY (primary_id = {$product_id}) DESC
LIMIT 3

The boolean expression in ORDER BY converts to 1 for true and 0 for false, so DESC puts primary matches first. The extra fields help you see which items are recommended and why - useful for debugging your recommendation algorithm later.

[AdventurousHiker17]

try using UNION instead of OR - run one query for primary_id matches, then another for related_id matches. MySQL will naturally return the primary matches first without any CASE statements:

(SELECT * FROM item_relations WHERE primary_id = {$product_id})
UNION
(SELECT * FROM item_relations WHERE related_id = {$product_id})
LIMIT 3

cleaner and performs well.