Advanced Filter Examples

Advanced Filter Examples

Practical examples of complex filtering using the advanced row filter features.

Table of Contents

• OR Logic Examples
• BETWEEN Operator Examples
• IN Operator Examples
• Combined Filter Examples
• Real-World Scenarios

OR Logic Examples

Example 1: Multiple Email Domains

Goal: Find users with Gmail or Yahoo emails

Filters:

email: contains "gmail"      [OR]
email: contains "yahoo"

Generated SQL:

WHERE (email LIKE '%gmail%' OR email LIKE '%yahoo%')

Results: Users with either gmail.com or yahoo.com addresses

---

Example 2: Flexible Status Matching

Goal: Find tasks that are active, pending, or in review

Filters:

status: equals "active"      [OR]
status: equals "pending"     [OR]
status: equals "review"

Generated SQL:

WHERE (status = 'active' OR status = 'pending' OR status = 'review')

Alternative: Use IN operator:

status: in "active, pending, review"

---

Example 3: Combined AND/OR Logic

Goal: Find high-priority tasks that are either active or pending

Filters:

status: equals "active"      [OR]
status: equals "pending"     [AND]
priority: >= 8

Generated SQL:

WHERE (status = 'active' OR status = 'pending') AND priority >= 8

Explanation: OR groups the first two conditions, then AND combines with priority

---

BETWEEN Operator Examples

Example 1: Age Range

Goal: Find users between 18 and 65 years old

Filters:

age: between
  Min: 18
  Max: 65

Generated SQL:

WHERE age BETWEEN 18 AND 65

Benefits: More efficient than age >= 18 AND age <= 65

---

Example 2: Price Range

Goal: Find products priced between $50 and $200

Filters:

price: between
  Min: 50.00
  Max: 200.00

Generated SQL:

WHERE price BETWEEN 50.00 AND 200.00

---

Example 3: Date Range

Goal: Find orders from November 2024

Filters:

created_at: between
  Min: 2024-11-01
  Max: 2024-11-30

Generated SQL:

WHERE created_at BETWEEN '2024-11-01' AND '2024-11-30'

---

Example 4: NOT BETWEEN

Goal: Find abnormal temperature readings (outside 60-80°F)

Filters:

temperature: not between
  Min: 60
  Max: 80

Generated SQL:

WHERE temperature NOT BETWEEN 60 AND 80

---

IN Operator Examples

Example 1: Specific User IDs

Goal: Find users with IDs 1, 5, 10, 25, 100

Filters:

id: in
  Values: 1, 5, 10, 25, 100

Generated SQL:

WHERE id IN (1, 5, 10, 25, 100)

---

Example 2: Multiple Status Values

Goal: Find tasks with specific statuses

Filters:

status: in
  Values: active, pending, review, testing

Generated SQL:

WHERE status IN ('active', 'pending', 'review', 'testing')

---

Example 3: Exclude Test Users

Goal: Find all users except test accounts

Filters:

email: not in
  Values: test@example.com, admin@example.com, demo@example.com

Generated SQL:

WHERE email NOT IN ('test@example.com', 'admin@example.com', 'demo@example.com')

---

Example 4: Multiple Categories

Goal: Find products in specific categories

Filters:

category: in
  Values: electronics, computers, smartphones, tablets

Generated SQL:

WHERE category IN ('electronics', 'computers', 'smartphones', 'tablets')

---

Combined Filter Examples

Example 1: Complex User Query

Goal: Find active Gmail users over 18 who registered recently

Filters:

email: contains "gmail"      [AND]
age: > 18                    [AND]
status: equals "active"      [AND]
created_at: >= 2024-01-01

Generated SQL:

WHERE email LIKE '%gmail%' 
  AND age > 18 
  AND status = 'active' 
  AND created_at >= '2024-01-01'

---

Example 2: E-commerce Product Filter

Goal: Find electronics or computers priced $100-$1000 in stock

Filters:

category: equals "electronics"   [OR]
category: equals "computers"     [AND]
price: between
  Min: 100
  Max: 1000                      [AND]
stock: > 0

Generated SQL:

WHERE (category = 'electronics' OR category = 'computers') 
  AND price BETWEEN 100 AND 1000 
  AND stock > 0

---

Example 3: User Segmentation

Goal: Find premium users from specific cities who are active

Filters:

subscription: equals "premium"   [AND]
city: in
  Values: New York, Los Angeles, Chicago  [AND]
status: equals "active"          [AND]
last_login: >= 2024-10-01

Generated SQL:

WHERE subscription = 'premium' 
  AND city IN ('New York', 'Los Angeles', 'Chicago') 
  AND status = 'active' 
  AND last_login >= '2024-10-01'

---

Example 4: Order Analysis

Goal: Find orders that are pending or processing, with high value, from last month

Filters:

status: equals "pending"         [OR]
status: equals "processing"      [AND]
total: >= 500.00                 [AND]
order_date: between
  Min: 2024-10-01
  Max: 2024-10-31

Generated SQL:

WHERE (status = 'pending' OR status = 'processing') 
  AND total >= 500.00 
  AND order_date BETWEEN '2024-10-01' AND '2024-10-31'

---

Real-World Scenarios

Scenario 1: Customer Support Triage

Goal: Find urgent support tickets from premium customers

Filters:

priority: in
  Values: urgent, critical       [AND]
customer_tier: equals "premium"  [AND]
status: not in
  Values: closed, resolved

SQL:

WHERE priority IN ('urgent', 'critical') 
  AND customer_tier = 'premium' 
  AND status NOT IN ('closed', 'resolved')

Use Case: Support dashboard to prioritize high-value customer issues

---

Scenario 2: Inventory Management

Goal: Find low-stock items in popular categories

Filters:

stock: between
  Min: 1
  Max: 10                        [AND]
category: in
  Values: electronics, smartphones, laptops  [AND]
status: equals "active"

SQL:

WHERE stock BETWEEN 1 AND 10 
  AND category IN ('electronics', 'smartphones', 'laptops') 
  AND status = 'active'

Use Case: Restock alerts for high-demand products

---

Scenario 3: User Engagement Analysis

Goal: Find recently registered users who haven't been active

Filters:

created_at: >= 2024-10-01        [AND]
last_login: is null              [OR]
last_login: < 2024-10-15

SQL:

WHERE created_at >= '2024-10-01' 
  AND (last_login IS NULL OR last_login < '2024-10-15')

Use Case: Target users for re-engagement campaigns

---

Scenario 4: Sales Performance

Goal: Find high-value sales from specific regions in Q4

Filters:

amount: >= 10000.00              [AND]
region: in
  Values: North, West, Central   [AND]
sale_date: between
  Min: 2024-10-01
  Max: 2024-12-31

SQL:

WHERE amount >= 10000.00 
  AND region IN ('North', 'West', 'Central') 
  AND sale_date BETWEEN '2024-10-01' AND '2024-12-31'

Use Case: Quarterly sales report for top-performing regions

---

Scenario 5: Content Moderation

Goal: Find flagged content that needs review

Filters:

flags: > 5                       [AND]
status: equals "published"       [AND]
category: not in
  Values: archived, deleted      [AND]
reviewed: is null

SQL:

WHERE flags > 5 
  AND status = 'published' 
  AND category NOT IN ('archived', 'deleted') 
  AND reviewed IS NULL

Use Case: Content moderation queue

---

Scenario 6: Subscription Management

Goal: Find expiring subscriptions for renewal reminders

Filters:

expires_at: between
  Min: 2024-11-01
  Max: 2024-11-15                [AND]
status: equals "active"          [AND]
plan: in
  Values: premium, enterprise    [AND]
auto_renew: equals 0

SQL:

WHERE expires_at BETWEEN '2024-11-01' AND '2024-11-15' 
  AND status = 'active' 
  AND plan IN ('premium', 'enterprise') 
  AND auto_renew = 0

Use Case: Proactive renewal reminders for high-value customers

---

Performance Tips

1. Use BETWEEN for Ranges

Bad:

age: >= 18  [AND]
age: <= 65

Good:

age: between
  Min: 18
  Max: 65

Reason: BETWEEN is more efficient and clearer

---

2. Use IN for Multiple Values

Bad:

status: equals "active"   [OR]
status: equals "pending"  [OR]
status: equals "review"

Good:

status: in
  Values: active, pending, review

Reason: IN is more efficient for multiple OR conditions on same column

---

3. Add Indexes for Filtered Columns

For frequently filtered columns, create indexes:

CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_products_price ON products(price);

Tool: Use the Index Analyzer tab to get recommendations

---

4. Limit IN Values

• Maximum: 100 values per IN filter
• For larger lists, consider restructuring query
• Use temporary tables for very large value lists

---

Best Practices

1. Start Simple: Begin with one or two filters, then add more
2. Test Incrementally: Add filters one at a time to verify results
3. Use Presets: Save commonly used filter combinations
4. Monitor Performance: Check query execution time in results
5. Index Smart: Add indexes for frequently filtered columns
6. Document Filters: Use preset descriptions to explain complex filters
7. URL Sharing: Bookmark filtered views for quick access
8. Clear Unused: Remove filters that aren't needed to simplify queries

---

Keyboard Shortcuts

• Escape - Clear individual filter (when focused on input)
• Enter - Apply filter (when typing in input)
• Alt+F - Focus filter bar (future enhancement)

---

Next Steps

• Row-Level-Filtering - Complete filtering documentation
• Query Console - For even more complex queries with raw SQL
• Index Analyzer - Optimize filter performance with indexes
• Table Operations - Manage filtered data

---

Need Help? See Troubleshooting or open an issue.