Dynamic Constraints - PackDB Examples

THE PROBLEM

An inventory manager selects items for a display, but the rules come from a configuration table — the maximum number of items and the budget are stored externally rather than hard-coded. Additionally, category-specific rules apply: at least 2 items from category A and at most 3 from category B. This example shows how PackDB constraints can reference scalar subqueries (for dynamic bounds) and use CASE expressions (for category-level counting).

SAMPLE DATA

CREATE TABLE Config (id INTEGER, max_items INTEGER, budget INTEGER);
INSERT INTO Config VALUES (1, 5, 600);

CREATE TABLE Items (
    item_id INTEGER, name VARCHAR, category VARCHAR, value INTEGER
);
INSERT INTO Items VALUES
    (1, 'Premium Item A1', 'A', 150), (2, 'Premium Item A2', 'A', 140),
    (3, 'Premium Item A3', 'A', 100), (4, 'Standard Item B0', 'B', 130),
    (5, 'Standard Item B1', 'B', 120), (6, 'Standard Item B2', 'B', 110),
    (7, 'Standard Item B3', 'B', 90), (8, 'Basic Item C1', 'C', 80),
    (9, 'Basic Item C2', 'C', 70);

THE QUERY

SELECT item_id, name, category, value, x as selected
FROM Items
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x) <= (SELECT max_items FROM Config WHERE id = 1) AND
    SUM(x * CASE WHEN category = 'A' THEN 1 ELSE 0 END) >= 2 AND
    SUM(x * CASE WHEN category = 'B' THEN 1 ELSE 0 END) <= 3 AND
    SUM(x * value) <= (SELECT budget FROM Config WHERE id = 1)
MAXIMIZE SUM(x * value);

QUERY BREAKDOWN

1

SUM(x) <= (SELECT max_items FROM Config WHERE id = 1) — Total selected items cannot exceed the limit from the Config table (5 in this case).

2

SUM(x * CASE WHEN category = 'A' THEN 1 ELSE 0 END) >= 2 — At least 2 items from category A must be selected.

3

SUM(x * CASE WHEN category = 'B' THEN 1 ELSE 0 END) <= 3 — At most 3 items from category B.

4

SUM(x * value) <= (SELECT budget FROM Config WHERE id = 1) — Total value within budget (600).

5

MAXIMIZE SUM(x * value) — Maximize the total value of selected items.

RESULT

item_id	name	category	value	selected
1	Premium Item A1	A	150	1
2	Premium Item A2	A	140	1
3	Premium Item A3	A	100	0
4	Standard Item B0	B	130	0
5	Standard Item B1	B	120	1
6	Standard Item B2	B	110	1
7	Standard Item B3	B	90	0
8	Basic Item C1	C	80	1
9	Basic Item C2	C	70	0

INTERPRETATION

The solver selects 5 items totaling exactly 600 (the budget limit): two from category A (Premium A1 at 150, Premium A2 at 140), two from category B (Standard B1 at 120, Standard B2 at 110), and one from category C (Basic C1 at 80). This satisfies the minimum 2 category-A items and stays within the maximum 3 category-B items. The key feature here is that the constraints reference the Config table dynamically — changing the budget or max_items in Config would change the optimal selection without modifying the query.