JSON Query Builder

New JSON-based query builder functionality, that replaces the legacy custom where and custom order settings for filtering and sorting queries.

#Overview

The JSON Query Builder allows you to create structured, validated database queries using JSON instead of raw SQL. It's available in different areas of JReviews to help customize the database query results.

#Key Benefits

• Secure - No SQL injection vulnerabilities
• Validated - Automatic validation of operators and structure
• Portable - Works across different modules/pages
• Maintainable - Structured format easier to read and modify
• Feature-rich - Supports relations, functions, subqueries, dynamic tokens

#When to Use

Use JSON queries when you need to:

• Filter listings by custom criteria
• Order results by specific columns or ratings
• Create dynamic queries based on user context (user_id, listing_id)
• Query across related tables (custom fields, aggregates)
• Use date/time filtering (events, recent content)

#Platform Differences

Important: Column names differ between Joomla and WordPress. Use the correct columns for your platform.

#Understanding Listings Tables

JReviews listings are stored in your CMS's content tables:

• Joomla: Listings use the #__content table
• WordPress: Listings use the #__posts table (with post_type = 'jreviews')

Because of this, column names differ between platforms. The examples in this documentation are for listing objects.

Note: The #__ prefix represents your CMS's table prefix and is automatically replaced with the actual prefix (e.g., jos_, wp_, etc.).

#Main Table Columns (Listings)

These columns are from the main listings table and can be queried directly without a relation.

Concept	Joomla Column	WordPress Column	Notes
Listing ID	id	ID	Primary key
Author	created_by	post_author	User who created the listing
Created Date	created	post_date_gmt	Publication date (UTC)
Modified Date	modified	post_modified_gmt	Last edit date (UTC)
Published Status	state	post_status	1 = published (Joomla), 'publish' (WordPress)
Title	title	post_title	Listing title
Views	views	views	View count (aliased, works on both)
Category	catid	N/A	Category ID (Joomla only - WordPress uses taxonomy relations)

#Custom Fields (field_data relation)

Custom fields are identical across platforms:

• field_data.jr_city
• field_data.jr_price
• field_data.jr_eventdate
• etc.

#Aggregates (aggregates relation)

Rating and totals columns are identical across platforms:

• aggregates.user_rating
• aggregates.user_rating_count
• aggregates.user_rating_rank
• etc.

WordPress Users: When you see examples using Joomla columns (created_by, created, state), replace them with WordPress equivalents (post_author, post_date_gmt, post_status).

Joomla Users: Examples shown with Joomla columns work as-is.

#Basic Structure

Every JSON query has the same structure:

{
    "where": [ /* filtering conditions */ ],
    "order": [ /* sorting rules */ ]
}

Both where and order are optional. You can have just where, just order, or both.

#WHERE Conditions

#Simple Condition

Structure:

{
    "column": "column_name",
    "operator": "=",
    "value": "some_value"
}

Example - Filter published listings (Joomla):

{
    "where": [
        {
            "column": "state",
            "operator": "=",
            "value": 1
        }
    ]
}

Example - Filter published listings (WordPress):

{
    "where": [
        {
            "column": "post_status",
            "operator": "=",
            "value": "publish"
        }
    ]
}

#Multiple Conditions (AND)

Add multiple conditions to the where array. By default, they're combined with AND logic.

Example - Published listings with high views:

Show only listings that are published AND have more than 100 views.

{
    "where": [
        {
            "column": "state",
            "operator": "=",
            "value": 1
        },
        {
            "column": "views",
            "operator": ">",
            "value": 100
        }
    ]
}

Platform Note: For WordPress, replace state with post_status and use value 'publish' instead of 1.

#OR Conditions (Groups)

Use condition groups with logic: "OR" for OR logic:

Example - Listings from Los Angeles OR New York:

Show published listings located in either Los Angeles or New York.

Field Type: jr_city is a text field. Platform Note: For WordPress, replace state with post_status and use value 'publish' instead of 1.

{
    "where": [
        {
            "column": "state",
            "operator": "=",
            "value": 1
        },
        {
            "logic": "OR",
            "conditions": [
                {
                    "column": "field_data.jr_city",
                    "operator": "=",
                    "value": "Los Angeles"
                },
                {
                    "column": "field_data.jr_city",
                    "operator": "=",
                    "value": "New York"
                }
            ]
        }
    ]
}

#Supported Operators

#Comparison Operators

Operator	Description	Example Value
=	Equal to	"New York"
!=	Not equal to	0
<	Less than	100
<=	Less than or equal	100
>	Greater than	50
>=	Greater than or equal	50

#Pattern Matching

Operator	Description	Example Value
LIKE	SQL LIKE pattern	"%hotel%"

Use % as wildcard:

• "hotel%" - starts with "hotel"
• "%hotel" - ends with "hotel"
• "%hotel%" - contains "hotel"

#List Operators

Operator	Description	Value Type
IN	Value is in list	Array: [1, 2, 3]
NOT IN	Value not in list	Array: [4, 5, 6]

Example - Multiple specific IDs (Joomla):

Filter listings by specific IDs.

{
    "column": "id",
    "operator": "IN",
    "value": [10, 25, 42, 68]
}

Platform Note: For WordPress, replace id with ID. Important: IN operator only works with single-value fields. For multi-select fields (checkboxes, multi-select dropdowns), use LIKE with %*value*% pattern.

#Range Operator

Operator	Description	Value Type
BETWEEN	Value in range (inclusive)	Array: [min, max]
NOT BETWEEN	Value outside range	Array: [min, max]

Example - Price range $50-$200:

Field Type: jr_price is a number field. Works on: Both Joomla and WordPress.

{
    "column": "field_data.jr_price",
    "operator": "BETWEEN",
    "value": [50, 200]
}

#NULL Operators

Operator	Description	Value
IS NULL	Column is NULL	Not required
IS NOT NULL	Column is not NULL	Not required

Example - Has email address:

Field Type: jr_email is a text field. Works on: Both Joomla and WordPress.

{
    "column": "field_data.jr_email",
    "operator": "IS NOT NULL"
}

#Working with Relations

#Custom Fields (field_data)

Access custom field values using dot notation: field_data.field_name

Example - Filter by city:

Show only listings from Los Angeles.

Field Type: jr_city is a text field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_city",
            "operator": "=",
            "value": "Los Angeles"
        }
    ]
}

Example - Filter by price and featured status:

Show featured listings priced $100 or more.

Field Types: jr_price is a number field, featured is a yes/no field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_price",
            "operator": ">=",
            "value": 100
        },
        {
            "column": "field_data.featured",
            "operator": "=",
            "value": 1
        }
    ]
}

#Ratings and Totals (aggregates)

Access rating data using dot notation: aggregates.column_name

Example - High-rated listings:

Show listings with ratings of 4.5 or higher and at least 10 reviews.

Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "aggregates.user_rating",
            "operator": ">=",
            "value": 4.5
        },
        {
            "column": "aggregates.user_rating_count",
            "operator": ">=",
            "value": 10
        }
    ]
}

Example - Most reviewed listings:

Show listings with more than 50 reviews, sorted by review count.

Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "aggregates.user_rating_count",
            "operator": ">",
            "value": 50
        }
    ],
    "order": [
        {
            "column": "aggregates.user_rating_count",
            "direction": "desc"
        }
    ]
}

#ORDER BY

#Basic Ordering

Structure:

{
    "order": [
        {
            "column": "column_name",
            "direction": "asc"  // or "desc"
        }
    ]
}

Example - Newest first (Joomla):

{
    "order": [
        {
            "column": "created",
            "direction": "desc"
        }
    ]
}

Example - Newest first (WordPress):

{
    "order": [
        {
            "column": "post_date_gmt",
            "direction": "desc"
        }
    ]
}

#Multiple Sort Columns

Example - Sort by rating, then date:

Platform Note: For WordPress, replace created with post_date_gmt.

{
    "order": [
        {
            "column": "aggregates.user_rating",
            "direction": "desc"
        },
        {
            "column": "created",
            "direction": "desc"
        }
    ]
}

#Random Ordering

Example - Random listings:

{
    "order": [
        {
            "column": "RAND()",
            "direction": "asc"
        }
    ]
}

Use this for:

• Featured content rotation
• Random recommendations
• Varied homepage displays

#Date and Time Filtering

#Current Date/Time Functions

Function	Description	Returns
NOW()	Current date and time	2025-01-06 14:30:00
CURDATE()	Current date only	2025-01-06
CURRENT_DATE	Current date (no parens)	2025-01-06

Example - Future events:

Field Type: jr_eventdate is a date field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_eventdate",
            "operator": ">=",
            "value": "NOW()"
        }
    ]
}

#Date Arithmetic

Add or subtract days, months, years:

Function	Description	Example
DATE_SUB(NOW(), INTERVAL 7 DAY)	7 days ago	Recent listings
DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)	1 month ago	Monthly content
DATE_ADD(NOW(), INTERVAL 30 DAY)	30 days from now	Future events

Example - Listings from past 30 days (Joomla):

{
    "where": [
        {
            "column": "created",
            "operator": ">=",
            "value": "DATE_SUB(NOW(), INTERVAL 30 DAY)"
        }
    ]
}

Example - Listings from past 30 days (WordPress):

{
    "where": [
        {
            "column": "post_date_gmt",
            "operator": ">=",
            "value": "DATE_SUB(NOW(), INTERVAL 30 DAY)"
        }
    ]
}

Supported intervals:

• DAY, WEEK, MONTH, YEAR
• HOUR, MINUTE, SECOND

#Date Extraction

Extract parts of a date for filtering:

Function	Description	Returns
MONTH(column)	Month (1-12)	12
YEAR(column)	Year	2025
DAY(column)	Day of month (1-31)	6
WEEK(column)	Week of year (0-53)	1
DATE(column)	Date only (strip time)	2025-01-06

Example - Events this month:

Field Type: jr_eventdate is a date field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "MONTH(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_month"
        },
        {
            "column": "YEAR(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_year"
        }
    ]
}

Example - Listings created on specific date:

{
    "where": [
        {
            "column": "DATE(created)",
            "operator": "=",
            "value": "2025-01-01"
        }
    ]
}

#String Functions

#Case Conversion

Function	Description	Example
UPPER(column)	Convert to uppercase	UPPER(title)
LOWER(column)	Convert to lowercase	LOWER(field_data.jr_city)

Example - Case-insensitive search:

{
    "where": [
        {
            "column": "UPPER(title)",
            "operator": "LIKE",
            "value": "%HOTEL%"
        }
    ]
}

#Whitespace Functions

Function	Description
TRIM(column)	Remove leading/trailing spaces
LTRIM(column)	Remove leading spaces
RTRIM(column)	Remove trailing spaces

Example - Non-empty trimmed values:

Field Type: jr_description is a textarea field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "TRIM(field_data.jr_description)",
            "operator": "!=",
            "value": ""
        }
    ]
}

#Dynamic Tokens

Use tokens for context-aware queries. Tokens are replaced with actual values at runtime.

#Available Tokens

Token	Description	When Available	Example Value
user_id	Current logged-in user ID	Always	123
listing_id	Current listing detail page ID	Detail pages only	456
current_year	Current year	Always	2025
current_month	Current month (1-12)	Always	1
current_day	Current day of month (1-31)	Always	15

Note: Date tokens (current_year, current_month, current_day) are automatically available and always reflect the current server date. User and listing tokens are context-dependent.

#Using Tokens

Replace "value" with "token":

Example - Listings by current user (Joomla):

{
    "where": [
        {
            "column": "created_by",
            "operator": "=",
            "token": "user_id"
        }
    ]
}

Example - Listings by current user (WordPress):

{
    "where": [
        {
            "column": "post_author",
            "operator": "=",
            "token": "user_id"
        }
    ]
}

Example - Exclude current listing:

{
    "where": [
        {
            "column": "id",
            "operator": "!=",
            "token": "listing_id"
        }
    ]
}

Example - Events happening this month:

Filter events by the current month and year using date tokens.

Platform Note: For WordPress, replace created with post_date_gmt.

{
    "where": [
        {
            "column": "MONTH(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_month"
        },
        {
            "column": "YEAR(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_year"
        }
    ]
}

#Subqueries

Subqueries let you filter based on values from other tables.

#Scalar Subqueries (= operator)

Use when subquery returns a single value.

Example - More listings by same author (Joomla):

{
    "where": [
        {
            "column": "id",
            "operator": "!=",
            "token": "listing_id"
        },
        {
            "column": "created_by",
            "operator": "=",
            "subquery": {
                "select": "created_by",
                "from": "#__content",
                "where": [
                    {
                        "column": "id",
                        "operator": "=",
                        "token": "listing_id"
                    }
                ]
            }
        },
        {
            "column": "state",
            "operator": "=",
            "value": 1
        }
    ]
}

Example - More listings by same author (WordPress):

{
    "where": [
        {
            "column": "ID",
            "operator": "!=",
            "token": "listing_id"
        },
        {
            "column": "post_author",
            "operator": "=",
            "subquery": {
                "select": "post_author",
                "from": "#__posts",
                "where": [
                    {
                        "column": "ID",
                        "operator": "=",
                        "token": "listing_id"
                    }
                ]
            }
        },
        {
            "column": "post_status",
            "operator": "=",
            "value": "publish"
        }
    ]
}

#WHERE IN Subqueries

Use when subquery returns multiple values.

Example - User's favorited listings:

{
    "where": [
        {
            "column": "id",
            "operator": "IN",
            "subquery": {
                "select": "listing_id",
                "from": "#__jreviews_favorites",
                "where": [
                    {
                        "column": "user_id",
                        "operator": "=",
                        "token": "user_id"
                    }
                ]
            }
        },
        {
            "column": "state",
            "operator": "=",
            "value": 1
        }
    ]
}

#Subquery Structure

{
    "select": "column_to_return",
    "from": "#__table_name",
    "where": [
        /* conditions for the subquery */
    ]
}

Notes:

• Use #__ prefix for CMS table names (auto-replaced with actual prefix)
• Subqueries support all operators: =, !=, IN, NOT IN
• Subqueries can use tokens
• Keep subqueries simple for best performance

#Custom Field Types and Querying

When querying custom fields via the field_data relation, the field type determines which operator and pattern to use:

Field Type	Storage Format	Recommended Operator	Value Pattern	Example
Text	Plain text	=, LIKE	%value% for partial match	"jr_city" → "Los Angeles" or "%Angeles%"
Textarea	Plain text	LIKE	%value%	"jr_description" → "%restaurant%"
Single-Select	Single value	=, IN	Direct value	"jr_category" → "restaurants"
Radio Buttons	Single value	=, IN	Direct value	"jr_status" → "active"
Checkboxes	Serialized: *val1*val2*	LIKE	%*value*%	"jr_amenities" → "%*wifi*%"
Multi-Select	Serialized: *val1*val2*	LIKE	%*value*%	"jr_features" → "%*pool*%"
Number/Integer	Numeric	=, <, >, etc.	Numeric value	"jr_price" → 1000
Decimal	Numeric	=, <, >, etc.	Numeric value	"jr_rating" → 4.5
Date	Date/DateTime	=, <, >, etc.	Date string	"jr_eventdate" → "2024-01-01"
Yes/No	Boolean (0/1)	=	0 or 1	"featured" → 1

Key Rule: Always use LIKE with %*value*% pattern for multi-select fields (checkboxes, multi-select dropdowns). Use IN operator only for single-select fields.

#Multi-Select Field Examples

#Single Option Match

Find listings with "pool" in amenities.

Field Type: jr_amenities is a multi-select field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_amenities",
            "operator": "LIKE",
            "value": "%*pool*%"
        }
    ]
}

#Multiple Options (OR Logic)

Find listings with either "wifi" OR "parking".

Field Type: jr_amenities is a multi-select field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "logic": "OR",
            "conditions": [
                {
                    "column": "field_data.jr_amenities",
                    "operator": "LIKE",
                    "value": "%*wifi*%"
                },
                {
                    "column": "field_data.jr_amenities",
                    "operator": "LIKE",
                    "value": "%*parking*%"
                }
            ]
        }
    ]
}

#Multiple Options (AND Logic)

Find listings with both "wifi" AND "pool".

Field Type: jr_amenities is a multi-select field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_amenities",
            "operator": "LIKE",
            "value": "%*wifi*%"
        },
        {
            "column": "field_data.jr_amenities",
            "operator": "LIKE",
            "value": "%*pool*%"
        }
    ]
}

#Available Relations

The JSON query builder supports three relations for querying related data for listing objects:

Relation	Description	Available Columns
field_data	Custom field values for the listing (from jreviews_content table)	Custom field columns (e.g., jr_city, jr_price, featured), email, metakey, metadesc
aggregates	Rating and media statistics for the listing (from jreviews_listing_totals table)	user_rating, user_rating_count, user_rating_rank (Bayesian), user_criteria_rating, user_criteria_rating_count, user_comment_count, editor_rating, editor_rating_count, editor_rating_rank (Bayesian), editor_criteria_rating, editor_criteria_rating_count, editor_comment_count, media_count, video_count, photo_count, audio_count, attachment_count, media_count_user, video_count_user, photo_count_user, audio_count_user, attachment_count_user
user	Author/creator information for the listing (from CMS users table)	name, username, email (user's email)

Note:

• user_rating_rank and editor_rating_rank use Bayesian averaging, which provides more accurate rankings by considering both the rating value and the number of ratings.
• These relations are specific to listing objects. Main table columns come from #__content (Joomla) or #__posts (WordPress).

#Complete Examples

#Example 1: Featured Listings Only

Show only featured listings, sorted by highest rating first. Useful for homepage featured sections or premium listings.

Field Type: featured is a yes/no field (1 = yes, 0 = no). Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.featured",
            "operator": "=",
            "value": 1
        }
    ],
    "order": [
        {
            "column": "aggregates.user_rating",
            "direction": "desc"
        }
    ]
}

#Example 2: Recent High-Rated Listings

Show listings created in the past 90 days with ratings of 4.0 or higher and at least 5 reviews. Sorted by rating, then by date.

Platform Note: For WordPress, replace created with post_date_gmt.

{
    "where": [
        {
            "column": "created",
            "operator": ">=",
            "value": "DATE_SUB(NOW(), INTERVAL 90 DAY)"
        },
        {
            "column": "aggregates.user_rating",
            "operator": ">=",
            "value": 4.0
        },
        {
            "column": "aggregates.user_rating_count",
            "operator": ">=",
            "value": 5
        }
    ],
    "order": [
        {
            "column": "aggregates.user_rating",
            "direction": "desc"
        },
        {
            "column": "created",
            "direction": "desc"
        }
    ]
}

#Example 3: Upcoming Events This Month

Show future events happening this month, sorted by event date. Perfect for event calendars and upcoming activity lists.

Field Type: jr_eventdate is a date field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_eventdate",
            "operator": ">=",
            "value": "NOW()"
        },
        {
            "column": "MONTH(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_month"
        },
        {
            "column": "YEAR(field_data.jr_eventdate)",
            "operator": "=",
            "token": "current_year"
        }
    ],
    "order": [
        {
            "column": "field_data.jr_eventdate",
            "direction": "asc"
        }
    ]
}

#Example 4: Price Range with Multiple Locations

Find listings priced between $50-$150 in New York, Los Angeles, or Chicago. Sorted by price from lowest to highest.

Field Types: jr_city is a text field, jr_price is a number field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "logic": "OR",
            "conditions": [
                {
                    "column": "field_data.jr_city",
                    "operator": "=",
                    "value": "New York"
                },
                {
                    "column": "field_data.jr_city",
                    "operator": "=",
                    "value": "Los Angeles"
                },
                {
                    "column": "field_data.jr_city",
                    "operator": "=",
                    "value": "Chicago"
                }
            ]
        },
        {
            "column": "field_data.jr_price",
            "operator": "BETWEEN",
            "value": [50, 150]
        }
    ],
    "order": [
        {
            "column": "field_data.jr_price",
            "direction": "asc"
        }
    ]
}

#Migration from Legacy Custom Where/Order

Migrating from Custom Where/Order? See the migration guide in the release notes for 11 pattern-by-pattern conversion examples showing how to convert your legacy SQL queries to JSON format.

#Best Practices

#1. Use Specific Operators

✅ Good:

{"column": "catid", "operator": "IN", "value": [5, 6, 7]}

❌ Avoid:

{"column": "catid", "operator": "=", "value": 5},
{"column": "catid", "operator": "=", "value": 6},
{"column": "catid", "operator": "=", "value": 7}

#2. Filter Early, Order Last

Put filtering conditions in where, sorting in order. The query builder optimizes better this way.

#3. Use BETWEEN for Ranges

✅ Good:

{"column": "field_data.jr_price", "operator": "BETWEEN", "value": [50, 200]}

❌ Avoid:

{"column": "field_data.jr_price", "operator": ">=", "value": 50},
{"column": "field_data.jr_price", "operator": "<=", "value": 200}

#4. Index Your Custom Fields

For frequently queried custom fields, ensure they're indexed in your database for better performance.

#5. Limit Subquery Complexity

Keep subqueries simple. Complex nested subqueries can be slow.

✅ Good: Single-level subquery ❌ Avoid: Subqueries within subqueries

#6. Use Appropriate Date Functions

• Use NOW() for date+time comparisons
• Use CURDATE() for date-only comparisons
• Use DATE(column) to strip time from datetime columns

#7. Test Your Queries

Before deploying to production:

1. Test with small result sets first
2. Verify the results match your expectations
3. Check query performance with realistic data volumes

#Common Use Cases

#Use Case: "Related Listings"

Show similar listings excluding the current one, randomly ordered. Perfect for "You May Also Like" sections on detail pages.

{
    "where": [
        {
            "column": "id",
            "operator": "!=",
            "token": "listing_id"
        }
    ],
    "order": [
        {
            "column": "RAND()",
            "direction": "asc"
        }
    ]
}

Platform Note: For WordPress, replace id with ID. Note: Add additional filters (category, city, price range) to make listings more relevant.

#Use Case: "Top Rated This Month"

Show highly-rated listings created this month, sorted by rating then review count. Great for "Trending" or "Popular This Month" sections.

Platform Note: For WordPress, replace created with post_date_gmt.

{
    "where": [
        {
            "column": "MONTH(created)",
            "operator": "=",
            "token": "current_month"
        },
        {
            "column": "YEAR(created)",
            "operator": "=",
            "token": "current_year"
        },
        {
            "column": "aggregates.user_rating",
            "operator": ">=",
            "value": 4.0
        }
    ],
    "order": [
        {
            "column": "aggregates.user_rating",
            "direction": "desc"
        },
        {
            "column": "aggregates.user_rating_count",
            "direction": "desc"
        }
    ]
}

#Use Case: "User's Recent Listings"

Display listings created by the logged-in user in the past 90 days. Perfect for "My Listings" or user dashboard pages.

{
    "where": [
        {
            "column": "created_by",
            "operator": "=",
            "token": "user_id"
        },
        {
            "column": "created",
            "operator": ">=",
            "value": "DATE_SUB(NOW(), INTERVAL 90 DAY)"
        }
    ],
    "order": [
        {
            "column": "created",
            "direction": "desc"
        }
    ]
}

Platform Note: For WordPress, replace created_by with post_author and created with post_date_gmt.

#Use Case: "Featured + Random"

Show featured listings in random order each page load. Useful for rotating featured content on homepage or sidebars.

Field Type: featured is a yes/no field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.featured",
            "operator": "=",
            "value": 1
        }
    ],
    "order": [
        {
            "column": "RAND()",
            "direction": "asc"
        }
    ]
}

#Use Case: "Upcoming Events"

Show events happening in the next 30 days, sorted by event date. Perfect for event listings and calendars.

Field Type: jr_eventdate is a date field. Works on: Both Joomla and WordPress.

{
    "where": [
        {
            "column": "field_data.jr_eventdate",
            "operator": ">=",
            "value": "NOW()"
        },
        {
            "column": "field_data.jr_eventdate",
            "operator": "<=",
            "value": "DATE_ADD(NOW(), INTERVAL 30 DAY)"
        }
    ],
    "order": [
        {
            "column": "field_data.jr_eventdate",
            "direction": "asc"
        }
    ]
}

#Troubleshooting

#Query Returns No Results

Check:

1. Are you using the correct column names for your platform?
2. Are your custom field names spelled correctly?
3. Do the values exist in your database?
4. Are you filtering too strictly (too many conditions)?

#Validation Errors

Common issues:

• Missing column key in condition
• Missing value or token (except for IS NULL/IS NOT NULL)
• Invalid operator (must be uppercase: IN, not in)
• Wrong value type (array for IN/BETWEEN, scalar for others)

#Performance Issues

Optimize:

1. Add database indexes on frequently queried columns
2. Reduce the number of relations (prefer main table columns)
3. Simplify complex subqueries
4. Limit result sets with pagination
5. Avoid using RAND() on very large datasets

#Platform Differences Not Working

Remember to use platform-specific column names:

• created_by (Joomla) → post_author (WordPress)
• created (Joomla) → post_date_gmt (WordPress)
• state (Joomla) → post_status (WordPress)

#Validation Rules

The JSON structure is validated before applying the query. If the JSON is invalid, the query is skipped.

#Validation Rules

• Operators: Only these operators are allowed: =, !=, <, <=, >, >=, LIKE, IN, NOT IN, BETWEEN, NOT BETWEEN, IS NULL, IS NOT NULL
• Relations: Only these relations are allowed: field_data, aggregates, user
• Tokens: Only these tokens are allowed: user_id, listing_id, current_year, current_month, current_day
• IN/NOT IN Values: Must be a non-empty array
• BETWEEN/NOT BETWEEN Values: Must be an array with exactly 2 elements
• Required Keys: column is required for each condition
• Value/Token: Either value OR token is required (except for IS NULL/IS NOT NULL)
• Mutual Exclusivity: Cannot use both value and token in the same condition
• Logic: Must be either AND or OR (defaults to AND)