Dynamic Constraints - DeciDB 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 DeciDB 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) >= 2 WHEN category = 'A' AND
    SUM(x) <= 3 WHEN category = 'B' 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) >= 2 WHEN category = 'A' — At least 2 items from category A must be selected. The postfix WHEN filters which rows the aggregate sums over, so only category-A rows contribute to this count.

3

SUM(x) <= 3 WHEN category = 'B' — 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	1
4	Standard Item B0	B	130	1
5	Standard Item B1	B	120	0
6	Standard Item B2	B	110	0
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): three from category A (Premium A1 at 150, A2 at 140, A3 at 100), one from category B (Standard B0 at 130), and one from category C (Basic C1 at 80). This satisfies the minimum 2 category-A items and stays well within the maximum 3 category-B items. There are several alternative selections that also reach 600 (e.g. A1+A2+B1+B2+C1) — the solver returns one optimum among them. 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.