PREDICATE - FILTER - SCOPEDATABASE CONCEPT/TERMsAI ASSISTs

UNDERSTANDING PREDICATE, QUERY FILTERING, AND QUERY SCOPE
(USING MULTIPLE RELATED-TABLES)

"how data is retrieved and structured LOGICALLY"

This guide explains the concepts using multiple RELATED tables (users, orders, products) so you can see how predicate, filtering, and scope work across relationships (JOIN).

EXAMPLE DATASET: RELATED TABLES

USERS TABLE

id	name	status	role
1	Andi	active	admin
2	Budi	inactive	user
3	Citra	active	user
4	Dedi	active	admin

ORDERS TABLE

id	user_id	product_id	status	total
101	1	201	paid	150000
102	2	202	pending	50000
103	3	203	paid	200000
104	4	204	cancelled	75000

PRODUCTS TABLE

id	name	price	stock
201	Laptop	10000000	5
202	Mouse	150000	0
203	Keyboard	300000	10
204	Monitor	2000000	2

PREDICATE (CONDITION)

users.status = "active"

users.role = "admin"

orders.status = "paid"

products.stock > 0

  

👉 These predicates define conditions across multiple related tables.

QUERY FILTERING (PROCESS WITH JOIN)

SELECT users.name, orders.id, products.name, orders.total

FROM users

JOIN orders ON users.id = orders.user_id

JOIN products ON products.id = orders.product_id

WHERE users.status = "active"

  AND orders.status = "paid"

  AND products.stock > 0;

  

RESULT (ACTIVE USERS WITH PAID ORDERS & AVAILABLE PRODUCTS):

user_name	order_id	product_name	total
Andi	101	Laptop	150000
Citra	103	Keyboard	200000

Explanation:

• users.status = "active" → only active users
• orders.status = "paid" → only paid orders
• products.stock > 0 → only available products

QUERY SCOPE (REUSABLE FILTERING WITH RELATION)

FUNCTION scopeActiveUsers(query):

    RETURN query WHERE users.status = "active"

FUNCTION scopePaidOrders(query):

    RETURN query WHERE orders.status = "paid"

FUNCTION scopeAvailableProducts(query):

    RETURN query WHERE products.stock > 0

  

USING MULTIPLE SCOPES

query = SELECT users.name, orders.id, products.name, orders.total

        FROM users

        JOIN orders ON users.id = orders.user_id

        JOIN products ON products.id = orders.product_id

query = scopeActiveUsers(query)

query = scopePaidOrders(query)

query = scopeAvailableProducts(query)

RESULT = EXECUTE(query)

  

👉 Same result as filtering, but now reusable and modular.

BIG PICTURE FLOW

Predicate (multi-table) 
→ Filtering (JOIN) 
→ Scope 
→ Reuse 
→ Result

  

FINAL CONCLUSION

• Predicate: logical conditions across tables
• Query Filtering: applying conditions using JOIN + WHERE
• Query Scope: reusable filtering logic across relationships

SIMPLE INTUITION

• Predicate = rules (even across tables)
• Filtering = selecting related data
• Scope = reusable multi-table filter