Item Filtering - PackDB Examples

THE PROBLEM

You have an inventory of items across categories A, B, and C with varying priorities and weights. You want to select the highest-priority items from specific categories while keeping total weight under 100 units. This example demonstrates combining standard SQL filtering (WHERE with IN and BETWEEN) with PackDB's optimization — the WHERE clause narrows the candidate set, and DECIDE optimizes over what remains.

SAMPLE DATA

CREATE TABLE Items (
    id INTEGER, name VARCHAR, priority INTEGER,
    weight INTEGER, category VARCHAR
);
INSERT INTO Items VALUES
    (1, 'Item Alpha', 5, 15, 'A'),
    (2, 'Item Beta', 2, 25, 'A'),
    (3, 'Item Gamma', 4, 20, 'B'),
    (4, 'Item Delta', 1, 30, 'D'),
    (5, 'Item Epsilon', 5, 10, 'C'),
    (6, 'Item Zeta', 3, 40, 'B');

THE QUERY

SELECT id, name, priority, weight, category, x as selected
FROM Items
WHERE category IN ('A', 'B', 'C')
  AND priority BETWEEN 1 AND 5
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * weight) <= 100
MAXIMIZE SUM(x * priority);

QUERY BREAKDOWN

1

WHERE category IN ('A', 'B', 'C') — Standard SQL filter: only items in categories A, B, or C are considered (Item Delta in category D is excluded before optimization).

2

AND priority BETWEEN 1 AND 5 — Further SQL filtering: only items with priority 1–5.

3

DECIDE x IS BOOLEAN — Binary decision: include (x=1) or exclude (x=0) each candidate item.

4

SUM(x * weight) <= 100 — Total weight of selected items must not exceed 100.

5

MAXIMIZE SUM(x * priority) — Pick the combination with the highest total priority.

RESULT

id	name	priority	weight	category	selected
1	Item Alpha	5	15	A	1
3	Item Gamma	4	20	B	1
5	Item Epsilon	5	10	C	1

INTERPRETATION

After WHERE filtering removes Item Delta (category D), five items remain. The solver selects Alpha (priority 5, weight 15), Gamma (priority 4, weight 20), and Epsilon (priority 5, weight 10) for a total priority of 14 and total weight of 45. Item Beta (priority 2) and Zeta (priority 3) were excluded because their lower priorities make them less valuable, and adding Zeta (weight 40) would bring the total to 85 without meaningfully increasing priority compared to the selected set.