Constraining by Associations (REQUIRE)

The problem REQUIRE solves

WHERE inside a WITH block filters the returned associated objects. But what if you want to filter the primary object based on its associated objects, without changing the returned list?

That's exactly what REQUIRE does.

REQUIRE COUNT: constraint on the number

-- Companies with exactly 2 decision-maker contacts
SELECT name, domain
FROM company
WITH ASSOCIATED contact ON LABELS 'decision maker' AS decision_makers
  SELECT firstname, email
  REQUIRE COUNT EQUAL 2

-- Contacts with between 1 and 3 active deals
SELECT email, firstname
FROM contact
WITH ASSOCIATED deal AS active_deals
  SELECT dealname, amount
  WHERE dealstage != 'closedwon'
  REQUIRE COUNT BETWEEN 1 , 3

-- Companies with more than 5 contacts
SELECT name, domain
FROM company
WITH ASSOCIATED contact AS contacts
  SELECT email
  REQUIRE COUNT GREATER THAN 5

The operators for REQUIRE COUNT are the same as for WHERE: =, !=, >, >=, <, <=, BETWEEN.

REQUIRE ANY / ALL / NONE: constraint on values

ANY — at least one associated object satisfies the condition

-- Companies with at least one deal above €10,000
SELECT name, domain
FROM company
WITH INNER ASSOCIATED deal AS big_deals
  SELECT dealname, amount
  WHERE dealstage = 'closedwon'
  REQUIRE ANY amount GREATER THAN 10000
  TAKE 3 ORDER BY amount DESC

ALL — all associated objects satisfy the condition

-- Contacts whose all deals are won
SELECT email, firstname
FROM contact
WITH ASSOCIATED deal AS deals
  SELECT dealstage
  REQUIRE COUNT GREATER THAN 0
  REQUIRE ALL dealstage EQUAL 'closedwon'

NONE — no associated object satisfies the condition

-- Contacts with no deal below €5,000
SELECT email, firstname
FROM contact
WITH ASSOCIATED deal AS deals
  SELECT dealstage, amount
  REQUIRE NONE amount LESS THAN 5000

Combining multiple REQUIRE clauses

Multiple REQUIRE clauses on the same WITH block are combined with an implicit AND. All conditions must be satisfied:

SELECT email, firstname
FROM contact
WITH ASSOCIATED deal AS deals
  SELECT dealstage, amount
  REQUIRE COUNT GREATER THAN 0          -- has at least one deal
  REQUIRE ALL dealstage EQUAL 'closedwon'  -- all won
  REQUIRE NONE amount LESS THAN 5000    -- none below €5,000

The key difference: WHERE vs REQUIRE

This is a fundamental distinction in RevOpsQL:

Illustrated example:

-- Case A: WHERE removes deals from the list
WITH ASSOCIATED deal AS deals
  SELECT dealname, amount
  WHERE amount >= 5000
-- Result: the deals list only contains deals ≥ €5,000
-- The company is still there, even if all its deals are < €5,000

-- Case B: REQUIRE filters the entire company
WITH ASSOCIATED deal AS deals
  SELECT dealname, amount
  REQUIRE NONE amount LESS THAN 5000
-- Result: the company is excluded if it has ONE deal < €5,000
-- But the deals list returns ALL deals (not just those ≥ €5,000)

Decision rule: Use WHERE for "I want to see only these associated objects". Use REQUIRE for "I only want primary objects that satisfy this condition on their associated objects".

Evaluation order within a WITH block

For each WITH block, the evaluation order is always:

1. ON LABELS     → filters on the link type
2. WHERE         → filters the returned associated objects
3. REQUIRE       → filters primary objects
4. TAKE ORDER BY → sorts and limits the returned associated objects