MEQL Reference

MEQL (Magentrix Entity Query Language) is a lightweight, SQL-like query language that enables developers and administrators to query entity data using familiar SQL syntax within the Magentrix platform. MEQL provides flexible data retrieval capabilities including filtering, sorting, aggregation, and pagination, supporting efficient data access through the Query Console in the IDE and Magentrix REST API version 3.

This query language supports essential SELECT operations with comprehensive WHERE clause capabilities, aggregate functions with GROUP BY, sorting, and pagination features, enabling developers to retrieve and analyze entity data during development activities, test business logic implementations, and integrate with external systems without requiring external database tools or custom implementations.

Query Structure and Syntax

Basic SELECT Statement

MEQL queries follow a standard SELECT statement structure with required and optional clauses:

SELECT <field_list>
FROM <entity_name>
[WHERE <condition>]
[GROUP BY <field_name>]
[ORDER BY <field_name> [ASC|DESC]]
[LIMIT <n>]
[LIMIT <take>,<offset>]

Field List Specification: The SELECT clause specifies one or more field names separated by commas. Field names must match the exact case-sensitive API names defined in the entity schema. Relationship fields can be traversed using dot notation to access fields from related entities. If the SELECT clause is omitted entirely, all entity fields are returned in the result set.

Entity Name Requirement: The FROM clause specifies the target entity for the query. Entity names are case-sensitive and must exactly match the entity's API name as defined in the Magentrix schema. Each query targets a single entity, though related entity fields can be accessed through relationship traversal.

Optional Filtering: The WHERE clause provides conditional filtering using comparison operators, logical operators, and supported functions. Multiple conditions can be combined using AND, OR, and NOT operators with parentheses for grouping complex expressions.

Aggregation Support: The GROUP BY clause enables aggregate queries that summarize data using functions like COUNT, SUM, AVG, MAX, and MIN. GROUP BY requires corresponding aggregate functions in SELECT and typically pairs with ORDER BY for consistent results.

Result Ordering: The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order based on a single field. Multiple-column sorting is not supported. Order specification is optional but recommended when using pagination clauses or GROUP BY aggregates.

Pagination Controls: LIMIT restricts the number of returned records and optionally skips records for pagination. LIMIT accepts either a single value (LIMIT <n>) to restrict results, or comma-separated take and offset values (LIMIT <take>,<offset>) for pagination. When using offset, ORDER BY is required.

Simple Query Examples

Basic Field Retrieval:

SELECT Id, Name FROM Account WHERE Name = "Magentrix"

All Fields Retrieval:

FROM Contact

Using String Functions:

SELECT Id, Name FROM Account WHERE Name.Contains("Magentrix") ORDER BY CreatedOn DESC

Implementing Pagination:

SELECT Id, Name FROM Account LIMIT 100
SELECT Id, Name FROM Account LIMIT 100, 10

Case Sensitivity Rules

Keyword Case Insensitivity

SQL keywords including SELECT, FROM, WHERE, GROUP BY, ORDER BY and LIMIT are case-insensitive and can be written in uppercase, lowercase, or mixed case without affecting query execution. The following queries are functionally equivalent:

select id, name from account
SELECT Id, Name FROM Account
SeLeCt Id, NaMe FrOm AcCoUnT

Best Practice: While keywords are case-insensitive, using consistent uppercase formatting for SQL keywords improves query readability and distinguishes keywords from entity and field names in code and documentation.

Entity and Field Name Case Sensitivity

Entity names and field names are strictly case-sensitive and must match their exact definitions in the Magentrix schema. Incorrect capitalization results in query errors indicating unknown entities or fields.

Invalid Query (Incorrect Field Capitalization):

SELECT Id, createdon FROM Account

Error: Field "createdon" not found

Valid Query (Correct Field Capitalization):

SELECT Id, CreatedOn FROM Account

Entity Name Verification: Use the Entity Browser in the IDE to verify correct entity and field name capitalization before constructing queries. The browser displays exact API names that must be used in MEQL statements.

Entity and Field Mapping

Direct Entity Access

Each MEQL query targets a single entity specified in the FROM clause. The entity name maps directly to the corresponding Magentrix object, providing access to all fields and relationships defined in that entity's schema.

Standard Entity Query:

SELECT Id, Name FROM Account
SELECT Id, Email FROM Contact
SELECT Id, Name FROM User

All Fields Query:

FROM Account

Returns all fields from Account entity without requiring explicit field list

Relationship Traversal with Dot Notation

MEQL supports accessing fields from related entities through dot notation. Relationship field names must respect case sensitivity and follow the exact relationship API names defined in the entity schema.

Single-Level Traversal in SELECT: The SELECT clause supports one level of relationship traversal, enabling access to fields from directly related entities.

Accessing Related Entity Fields:

SELECT Id, Name, Account.Name FROM Contact

Retrieves Contact Id and Name along with the related Account's Name

Multiple Related Fields:

SELECT Id, Name, Account.Name, Account.Industry, Owner.Email FROM Contact

Accesses fields from multiple related entities (Account and Owner)

SELECT Traversal Limitation: SELECT clause relationship traversal is limited to one level. Multi-level traversal like Account.Owner.Name or Contact.Account.Parent.Name is not supported in SELECT and will cause query errors.

Invalid Multi-Level SELECT:

SELECT Id, Name, Account.Owner.Name FROM Contact

Error: Multi-level relationship traversal not supported in SELECT

Multi-Level Traversal in WHERE: The WHERE clause supports multiple levels of relationship traversal, enabling filtering based on fields several layers deep in the data model.

Filtering on Related Fields:

SELECT Id, Name FROM Account WHERE Owner.Email = "owner@magentrix.com"

Filters Accounts based on the Owner's email address

Multi-Level WHERE Traversal:

SELECT Id, Name FROM Contact WHERE Account.Owner.Email = "manager@magentrix.com"

Filters Contacts based on their Account's Owner email (two levels deep)

Deep Relationship Filtering:

SELECT Id, Name FROM Opportunity WHERE Account.Parent.Industry = "Technology"

Filters Opportunities based on parent Account's industry (two levels deep)

Multiple Relationship Levels: WHERE clause dot notation can traverse multiple relationship levels, enabling complex filtering across entity hierarchies. Each relationship in the path must be valid and properly defined in the entity schema.

Traversal Best Practices:

SELECT Limitation Awareness: Remember that SELECT only supports one-level traversal. Design queries to retrieve directly related fields only, processing additional relationship navigation in controller or class code if needed.

WHERE Flexibility: Leverage multi-level WHERE traversal for complex filtering without SELECT limitations. Filter deeply in WHERE while retrieving only first-level related fields in SELECT.

Combined Pattern:

SELECT Id, Name, Account.Name FROM Contact 
WHERE Account.Parent.Industry = "Technology" 
AND Account.Owner.IsActive = true

Uses multi-level WHERE filtering with single-level SELECT traversal

Null Relationship Handling: When traversing relationships in WHERE clauses, records with null relationship values are excluded from results. Multi-level traversal fails if any relationship in the chain is null.

Performance Considerations: Each relationship level adds complexity to query execution. Minimize traversal depth when possible, particularly in WHERE clauses with multi-level paths.

Field Type Considerations

The Entity Browser displays field data types including strings, numbers, dates, booleans, and relationships. Understanding field types ensures correct query syntax and appropriate use of comparison operators and functions.

String Fields: Require double-quote delimiters for literal values and support string manipulation functions like Contains, StartsWith, EndsWith, ToLower, and ToUpper.

Relationship Fields: Provide object references enabling dot notation access to related entity fields. The relationship field name differs from the ID field name (e.g., "Account" vs "AccountId").

ID Fields: Store the actual foreign key values as strings. Queries can filter on either the relationship object or the ID field depending on requirements.

WHERE Clause Functionality

Comparison Operators

MEQL supports standard comparison operators for filtering records based on field values:

Equality and Inequality:

• = tests for equality
• != tests for inequality
• < tests for less than
• > tests for greater than
• <= tests for less than or equal
• >= tests for greater than or equal

Comparison Examples:

SELECT Id, Name FROM Account WHERE Name = "Magentrix"
SELECT Id, Name FROM Contact WHERE CreatedOn > "2024-01-01"
SELECT Id, Amount FROM Opportunity WHERE Amount >= 10000

Logical Operators

Multiple conditions can be combined using logical operators to create complex filtering expressions:

AND Operator: Requires all conditions to be true

SELECT Id, Name FROM Contact WHERE FirstName = "John" AND LastName = "Smith"

OR Operator: Requires at least one condition to be true

SELECT Id, Name FROM Account WHERE Type = "Partner" OR Type = "Customer"

NOT Operator: Negates a condition or expression

SELECT Id, Name, Account.Name FROM Contact 
WHERE NOT(Account.Name = "Magentrix") AND Name.Contains("ABC")

Parentheses for Grouping: Complex expressions can use parentheses to control evaluation order and group related conditions:

SELECT Id, Name FROM Account 
WHERE (Type = "Partner" AND Status = "Active") OR (Type = "Customer" AND Status = "Approved")

Supported Functions

MEQL supports comprehensive string functions and operations for powerful filtering capabilities beyond simple comparisons.

String Manipulation Functions:

Contains: Tests if a string field contains a specified substring

SELECT Id, Name FROM Contact WHERE Name.Contains("ABC")

StartsWith: Tests if a string field begins with a specified value

SELECT Id, Name FROM Account WHERE Name.StartsWith("Mag")

EndsWith: Tests if a string field ends with a specified value

SELECT Id, Email FROM User WHERE Email.EndsWith("@magentrix.com")

ToLower and ToUpper: Convert strings to lowercase or uppercase for case-insensitive comparisons

SELECT Id, Name FROM Account WHERE Name.ToLower() = "magentrix"

String.IsNullOrEmpty: Tests if a string field is null or contains an empty string

SELECT Id, Name FROM User WHERE String.IsNullOrEmpty(Name)

Mathematical Functions: Functions like Math.Round, Math.Floor, Math.Ceiling, and others can be used for numerical field operations and comparisons within WHERE clauses.

Advanced Filtering Capabilities

MEQL provides extensive advanced filtering capabilities beyond the basic functions described above. For comprehensive coverage of advanced filtering features, see Advanced Filtering and Functions.

Advanced capabilities include:

DateTime Functions: Dynamic date filtering using DateTime.Today and DateTime.UtcNow constants with date arithmetic methods (AddYears, AddMonths, AddDays, AddHours, AddMinutes, AddSeconds) for relative date calculations.

Date Part Extraction: Fn.DatePart function for extracting and filtering by specific date components including year, quarter, month, day, hour, minute, and second.

Timezone Conversion: Fn.ToLocalTime function for converting UTC-stored dates to user's local timezone for timezone-aware filtering.

Multi-Select Field Operations: Fn.Includes and Fn.Excludes functions for filtering multi-select fields based on value inclusion and exclusion patterns with complex set logic.

Comprehensive Examples: Detailed examples demonstrating function combination patterns, complex multi-function queries, and best practices for advanced filtering scenarios.

For complete syntax reference, usage examples, and best practices for all advanced filtering functions, see Advanced Filtering and Functions.

GROUP BY and Aggregate Functions

Aggregate Query Structure

The GROUP BY clause enables aggregate queries that summarize data rather than returning individual records. Aggregate queries require aggregate functions in the SELECT clause and GROUP BY specification of grouping fields.

Basic Aggregate Pattern:

SELECT <grouping_field>, <aggregate_function>
FROM <entity_name>
GROUP BY <grouping_field>
ORDER BY <grouping_field>

GROUP BY Requirements:

• Fields in SELECT must be either grouping fields or aggregate functions
• GROUP BY field should typically appear in ORDER BY for consistent results
• Aggregate functions cannot be used without GROUP BY

Aggregate Functions

COUNT(Id): Counts the number of rows in each grouping

SELECT Type, COUNT(Id) as RowCount
FROM Account
GROUP BY Type
ORDER BY Type

SUM(<field_name>): Sums the total of numeric or currency values in each group. If multi-currency is enabled, the total will be in the user's preferred currency.

SELECT Type, SUM(AnnualRevenue) as TotalRevenue
FROM Account
GROUP BY Type
ORDER BY Type

AVG(<field_name>): Calculates average of numeric or currency values in each group. If multi-currency is enabled, the values will be in the user's preferred currency.

SELECT Industry, AVG(AnnualRevenue) as AverageRevenue
FROM Account
GROUP BY Industry
ORDER BY Industry

MAX(<field_name>): Returns maximum numeric, currency, or date-time value in each group. If multi-currency is enabled, the values will be in the user's preferred currency.

SELECT Type, MAX(CreatedOn) as MostRecentDate
FROM Account
GROUP BY Type
ORDER BY Type

MIN(<field_name>): Returns minimum numeric, currency, or date-time value in each group. If multi-currency is enabled, the values will be in the user's preferred currency.

SELECT Type, MIN(CreatedOn) as OldestDate
FROM Account
GROUP BY Type
ORDER BY Type

Field Aliases in Aggregates

Field aliases using the as keyword are required when using aggregate functions in SELECT statements to retrieve aggregate values in results. Without aliases, aggregate functions execute but their calculated values are not included in the returned data set. Aliases are only supported for aggregate functions, not for regular field selections.

Valid Alias Usage:

SELECT Type, COUNT(Id) as RowCount, SUM(AnnualRevenue) as TotalRevenue
FROM Account
GROUP BY Type
ORDER BY Type

Invalid Alias Usage:

SELECT Id, Name AS AccountName FROM Account

Best Practice: Always include descriptive aliases for all aggregate functions using meaningful names like RowCount, TotalRevenue, AverageValue, or MaximumDate that clearly communicate the aggregate calculation purpose and improve result set readability.

Multi-Currency Considerations

When organizations have multi-currency enabled, aggregate functions on currency fields (SUM, AVG, MAX, MIN) automatically convert values to the querying user's preferred currency. This conversion ensures consistent currency representation in aggregate results without requiring manual conversion logic.

Currency Aggregate Behavior:

• All currency values convert to user's preferred currency before aggregation
• SUM adds converted values in target currency
• AVG calculates average using converted values
• MAX and MIN compare converted values

ORDER BY Clause

Single-Field Sorting

The ORDER BY clause sorts query results based on a single field in either ascending or descending order. Ascending order is the default when no sort direction is specified.

Ascending Order (Explicit):

SELECT Id, Name FROM Contact ORDER BY Name ASC

Descending Order:

SELECT Id, Name FROM Account ORDER BY CreatedOn DESC

Default Ascending Order:

SELECT Id, Name FROM Contact ORDER BY CreatedOn

Sorting Limitations

Single Column Only: MEQL supports sorting by only one field per query. Multiple-column sorting specifications like ORDER BY CreatedOn DESC, Name ASC are not supported and will result in query errors.

Workaround for Multi-Column Sorting: When complex sorting requirements exist, implement sorting logic within controller or class code after retrieving query results, or structure queries to retrieve pre-sorted subsets based on primary sort criteria.

Sorting with Pagination

When implementing pagination using LIMIT and offset, including an ORDER BY clause ensures consistent result ordering across paginated requests. Without explicit ordering, result sequence may vary between query executions.

Recommended Pagination Pattern:

SELECT Id, Name FROM Contact ORDER BY CreatedOn DESC LIMIT 10, 0
SELECT Id, Name FROM Contact ORDER BY CreatedOn DESC LIMIT 10, 10
SELECT Id, Name FROM Contact ORDER BY CreatedOn DESC LIMIT 10, 20

Sorting with GROUP BY

When using GROUP BY for aggregate queries, ORDER BY typically sorts by the grouping field to provide organized, predictable result ordering.

Aggregate Sorting Pattern:

SELECT Type, COUNT(Id) as RowCount
FROM Account
GROUP BY Type
ORDER BY Type

LIMIT Clauses

LIMIT for Result Restriction

The LIMIT clause restricts the maximum number of records returned by a query, enabling efficient retrieval of result subsets without processing entire entity tables.

Simple Limit:

SELECT Id, Name FROM Account LIMIT 5

Returns the first 5 Account records

Limit with Filtering:

SELECT Id, Name FROM Contact WHERE Email != null LIMIT 10

Returns up to 10 Contact records that have email addresses

LIMIT with Offset

The LIMIT clause can accept two comma-separated values where the first value specifies the number of records to return (take) and the second value specifies the number of records to skip (offset).

Comma-Separated Syntax:

SELECT Id, Name FROM Account LIMIT 5, 10

Skips the first 10 records and returns the next 5

Offset Requires ORDER BY: When using offset with the comma syntax, the query must include an ORDER BY clause to ensure consistent result ordering and predictable pagination behavior.

Pagination Implementation

LIMIT with offset enables standard pagination patterns where page size is controlled by the take value and page number determines the offset:

Page 1 (Records 1-10):

SELECT Id, Name FROM Account ORDER BY CreatedOn DESC LIMIT 10, 0

Page 2 (Records 11-20):

SELECT Id, Name FROM Account ORDER BY CreatedOn DESC LIMIT 10, 10

Pagination Formula: For page size P and page number N (starting from 1):

• LIMIT (take) = P
• Offset= (N - 1) × P

Maximum Row Limit

Each MEQL query is subject to a maximum limit of 10,000 rows per query execution. This limit applies regardless of LIMIT clause values or pagination settings.

Handling Large Datasets: When retrieving datasets exceeding 10,000 rows, implement pagination to retrieve data in multiple queries with each query returning a maximum of 10,000 rows. Use LIMIT and offset to paginate through the complete dataset systematically.

Large Dataset Pattern:

-- First chunk: Records 1-10,000
SELECT Id, Name FROM Account ORDER BY Id LIMIT 10000, 0

-- Second chunk: Records 10,001-20,000
SELECT Id, Name FROM Account ORDER BY Id LIMIT 10000, 10000

-- Third chunk: Records 20,001-30,000
SELECT Id, Name FROM Account ORDER BY Id LIMIT 10000, 20000

Null and Empty Value Handling

Null Comparisons

MEQL supports direct null comparisons using standard equality and inequality operators with the null keyword in lowercase.

Testing for Null Values:

SELECT Id, Name FROM User WHERE Name == null

Testing for Non-Null Values:

SELECT Id, Name FROM User WHERE Name != null

Null in Filter Expressions:

SELECT Id, Email FROM Contact 
WHERE Email != null AND Email.Contains("@magentrix.com")

String Empty Checks

The String.IsNullOrEmpty function provides comprehensive testing for string fields that are either null or contain empty strings, eliminating the need for compound null and empty string conditions.

Combined Null and Empty Test:

SELECT Id, Name FROM User WHERE String.IsNullOrEmpty(Name)

Finding Populated Fields:

SELECT Id, Name FROM Contact WHERE NOT(String.IsNullOrEmpty(Name))

Important Limitation: The IsNullOrEmpty function is valid only for string data type fields. Using this function on numeric, date, or boolean fields results in query errors.

Boolean Field Handling

Boolean fields require lowercase true and false keywords following C# syntax conventions rather than SQL-style integer representations or uppercase literals.

Testing for True Values:

SELECT Id, Name FROM User WHERE IsActive = true

Testing for False Values:

SELECT Id, Name FROM User WHERE IsActive = false

Boolean in Complex Expressions:

SELECT Id, Name FROM Contact WHERE IsActive = true AND Email != null

Invalid Boolean Syntax: Using TRUE, FALSE, 1, 0, or other boolean representations results in query errors. Only lowercase true and false are supported.

Date and Time Literals

ISO 8601 Format Support

Date and time literals must use ISO 8601 format enclosed in double quotes. MEQL supports multiple levels of date/time precision including date-only, date with time, and date with time including milliseconds.

Date Only:

SELECT Id, Name, CreatedOn FROM Contact WHERE CreatedOn = "2024-02-13"

Date and Time:

SELECT Id, Name, CreatedOn FROM Contact WHERE CreatedOn = "2024-02-13T18:14:34"

Date, Time, and Milliseconds:

SELECT Id, Name, CreatedOn FROM Contact WHERE CreatedOn = "2024-02-13T18:14:34.111"

Date Comparisons

Date fields support all standard comparison operators, enabling range queries and date-based filtering for temporal data analysis.

After Specific Date:

SELECT Id, Name FROM Opportunity WHERE CloseDate > "2024-01-01"

Date Range:

SELECT Id, Name FROM Account 
WHERE CreatedOn >= "2024-01-01" AND CreatedOn <= "2024-12-31"

Before Specific Date and Time:

SELECT Id, Name FROM Contact 
WHERE LastModifiedOn < "2024-02-13T18:14:34"

String Literal Syntax

Double Quote Requirement

All string values in MEQL queries must be enclosed in double quotes ("). Single quotes are not supported for string delimiters and will result in query parsing errors.

Valid String Literals:

SELECT Id, Name FROM Account WHERE Name = "Magentrix"
SELECT Id, Email FROM User WHERE Email.EndsWith("@magentrix.com")

Invalid String Literals:

SELECT Id, Name FROM Account WHERE Name = 'Magentrix'

Error: Single quotes not supported

Quote Escaping Limitation

MEQL does not support escaping double quotes within string literals. Queries requiring literal double-quote characters within string values cannot be constructed using standard escape sequences.

Unsupported Escaping:

SELECT Id, Name FROM Account WHERE Name = "Company \"ABC\" Inc"

This syntax is not supported

Workaround: Queries requiring embedded quotes should be structured to avoid the need for quoted content within string literals, or quote-containing values should be handled through parameterized query construction in controller or class code.

Unsupported Features

DISTINCT Keyword

The DISTINCT keyword for eliminating duplicate records from result sets is not supported in MEQL queries.

Not Supported:

SELECT DISTINCT Name FROM Account

IN and NOT IN Operators

List-based filtering using IN and NOT IN operators is not available in MEQL syntax.

Not Supported:

SELECT Id, Name FROM Account WHERE Type IN ("Partner", "Customer", "Vendor")

Alternative Approach: Use multiple OR conditions to achieve equivalent filtering:

SELECT Id, Name FROM Account 
WHERE Type = "Partner" OR Type = "Customer" OR Type = "Vendor"

Subqueries

Nested SELECT statements (subqueries) are not supported in any clause of MEQL queries.

Not Supported:

SELECT Id, Name FROM Account 
WHERE Id IN (SELECT AccountId FROM Contact WHERE Email != null)

Alternative Approach: Execute multiple separate queries and process results in controller or class code, using the results of one query to construct filter conditions for subsequent queries.

JOIN Clauses

Traditional SQL JOIN syntax is not supported in MEQL. Instead, use relationship traversal with dot notation to access related entity fields.

Not Supported:

SELECT Account.Name, Contact.Name FROM Contact 
JOIN Account ON Contact.AccountId = Account.Id

Supported Alternative:

SELECT Id, Name, Account.Name FROM Contact

Use dot notation to access related Account name

HAVING Clause

The HAVING clause for filtering aggregate query results is not supported. All filtering must occur in WHERE clauses before aggregation.

Not Supported:

SELECT Type, COUNT(Id) as RowCount
FROM Account
GROUP BY Type
HAVING COUNT(Id) > 10

Alternative Approach: Filter aggregate results in controller or class code after query execution, or structure queries to pre-filter records through WHERE clauses before aggregation.

Advanced SQL Features

Additional traditional SQL features not supported include:

• UNION, INTERSECT, or EXCEPT set operations
• Window functions
• Common table expressions (CTEs)
• Stored procedures or function calls beyond supported functions
• CASE statements
• Multiple ORDER BY columns

Error Handling and Validation

Common Error Types

Syntax Errors: Occur when query structure violates MEQL syntax rules including missing required clauses, incorrect keyword spelling, or unsupported features. Error messages typically indicate the specific syntax issue and location.

Entity and Field Name Errors: Result from case-sensitivity mismatches, typographical errors, or references to non-existent entities or fields. Error messages specify the unrecognized entity or field name.

Type Mismatch Errors: Occur when operations or comparisons are performed on incompatible data types, such as using string functions on numeric fields or comparing dates to strings without proper formatting.

Aggregate Function Errors: Result from using aggregate functions without GROUP BY, including non-aggregate fields in SELECT with GROUP BY, or attempting to use aliases in WHERE clauses.

Error Message Interpretation

Error messages returned by MEQL provide specific information about query failures including:

• Description of the syntax or semantic issue
• Location in the query where the error was detected
• Suggestions for correcting common issues
• Reference to unsupported features when attempting to use unavailable functionality

Debugging Strategy: When queries fail, review error messages carefully to identify the specific issue, verify entity and field names using the Entity Browser, confirm proper use of supported functions and operators, and test simplified query versions to isolate problematic clauses.

Query Validation Best Practices

Incremental Development: Build complex queries incrementally, testing each added clause to ensure proper syntax and expected results before adding additional complexity.

Entity Browser Reference: Always verify entity and field names in the Entity Browser before incorporating them into queries to ensure correct case-sensitive spelling.

Function Testing: Test supported functions in isolation with simple queries before incorporating them into complex filter expressions to verify correct syntax and expected behavior.

Result Verification: Compare query results against expected data to ensure WHERE clause logic produces correct filtering and ORDER BY clauses sort as intended.

Practical Query Examples

Basic Filtering Examples

Simple Equality Filter:

SELECT Id, Name FROM Account WHERE Name = "Magentrix"

Retrieves Accounts with exact name match

String Pattern Matching:

SELECT Id, Name FROM Account WHERE Name.StartsWith("Mag")

Finds Accounts with names beginning with "Mag"

Relationship Traversal:

SELECT Id, Name, Account.Name FROM Contact WHERE Account.Name = "Magentrix"

Retrieves Contacts associated with specific Account

Complex Filtering Examples

Multiple Conditions:

SELECT Id, Name, Account.Name FROM Contact 
WHERE NOT(Account.Name = "Magentrix") AND Name.Contains("ABC")

Finds Contacts whose names contain "ABC" but are not associated with Magentrix Account

Date Range with Relationship Filter:

SELECT Id, Name, CreatedOn FROM Contact 
WHERE CreatedOn >= "2024-01-01" AND CreatedOn <= "2024-12-31" AND Account.Type = "Partner"

Retrieves Contacts created during 2024 associated with Partner Accounts

Null and Content Checks:

SELECT Id, Name, Email FROM Contact 
WHERE Email != null AND Email.Contains("@magentrix.com")

Finds Contacts with non-null email addresses from specific domain

Aggregate Query Examples

Simple Count by Group:

SELECT Type, COUNT(Id) as RowCount
FROM Account
GROUP BY Type
ORDER BY Type

Counts Accounts by Type

Sum with Multiple Aggregates:

SELECT Type, COUNT(Id) as RowCount, SUM(AnnualRevenue) as TotalRevenue
FROM Account
GROUP BY Type
ORDER BY Type

Counts Accounts and sums revenue by Type

Average Calculation:

SELECT Industry, AVG(AnnualRevenue) as AverageRevenue
FROM Account
WHERE AnnualRevenue != null
GROUP BY Industry
ORDER BY Industry

Calculates average revenue by Industry for Accounts with revenue data

Pagination Examples

First Page of Results:

SELECT Id, Name FROM Contact ORDER BY CreatedOn DESC LIMIT 10, 0

Retrieves most recent 10 Contacts

Second Page of Results:

SELECT Id, Name FROM Contact ORDER BY CreatedOn DESC LIMIT 10, 10

Retrieves next 10 Contacts after first page

Filtered Pagination:

SELECT Id, Name FROM Contact 
WHERE IsActive = true ORDER BY Name ASC LIMIT 25, 50

Retrieves third page of 25 active Contacts sorted alphabetically

Boolean and Null Examples

Active Records:

SELECT Id, Name FROM User WHERE IsActive = true

Finds all active Users

Records with Empty Fields:

SELECT Id, Name FROM User WHERE String.IsNullOrEmpty(Name)

Identifies Users with null or empty names

Combined Boolean and Null Logic:

SELECT Id, Name, Email FROM Contact 
WHERE IsActive = true AND NOT(String.IsNullOrEmpty(Email))

Retrieves active Contacts with populated email addresses

All Fields Query Examples

Complete Entity Retrieval:

FROM Account

Returns all fields from all Account records (subject to 10,000 row limit)

All Fields with Filtering:

FROM Contact WHERE Email != null

Returns all fields from Contacts that have email addresses

All Fields with Sorting:

FROM Account ORDER BY CreatedOn DESC

Returns all fields from Accounts sorted by creation date

Query Performance Considerations

Efficient Query Design

Selective Filtering: Include WHERE clause conditions that significantly reduce result set size, particularly on indexed fields, to minimize database processing and data transfer requirements.

Appropriate Use of LIMIT: Always use LIMIT clauses when full result sets are not required, especially during development and testing, to reduce query execution time and resource consumption.

Field Selection: Specify only required fields in the SELECT clause rather than using the all-fields FROM syntax when possible. Targeted field selection reduces data transfer volume and query processing overhead for large result sets.

Indexed Field Filtering: When possible, filter on fields that are indexed in the entity schema. Indexed field filtering enables more efficient query execution compared to filtering on non-indexed fields.

Pagination Best Practices

Consistent Ordering: Always include ORDER BY clauses with pagination to ensure consistent result sequences across paginated requests. Without explicit ordering, record sequence may vary between queries.

Reasonable Page Sizes: Use LIMIT values appropriate to display requirements. Excessively large page sizes negate pagination benefits while very small page sizes increase the total number of queries required.

Index-Friendly Sorting: When possible, use ORDER BY on indexed fields to enable efficient sorting. Contact administrators about field indexing if performance issues arise with frequently used sort fields.

Aggregate Query Optimization

Appropriate GROUP BY Usage: Use GROUP BY queries only when aggregation is genuinely needed. Aggregation adds processing overhead compared to simple SELECT queries, so use judiciously.

Pre-Filtering with WHERE: Apply WHERE clause filters before GROUP BY to reduce the number of records processed by aggregation. Filtering before aggregation improves performance compared to aggregating all records then filtering results in code.

Limited Grouping Fields: Group by fields with reasonable cardinality. Grouping by fields with very high cardinality (many unique values) produces large result sets that may approach the 10,000 row limit.

Usage Context

Query Console (IDE)

MEQL queries execute in the Query Console within the Magentrix IDE, providing interactive query testing and data exploration capabilities during development. The Query Console displays results in tabular format with row counts and supports iterative query refinement.

Query Console Access: Access Query Console through the Tools menu in the IDE. Queries execute immediately upon submission, displaying results below the query editor.

Development Workflow: Use Query Console to test query syntax before implementing in code, verify data existence and correctness, explore entity relationships and data structures, and validate business logic results.

Result Limitations: Query Console displays results on-screen without export functionality. Results are limited to the 10,000 row maximum and display constraints of the browser interface.

REST API Version 3

MEQL serves as the query language for Magentrix REST API version 3, enabling external applications and integrations to query Magentrix data programmatically. API queries return results in JSON or XML format as specified by request headers.

API Integration: External applications submit MEQL queries to REST API version 3 endpoints, receiving structured result sets that can be processed programmatically for integration, reporting, or data synchronization purposes.

Programmatic Query Construction: Applications can construct MEQL queries dynamically based on user inputs, filter criteria, or integration requirements, providing flexible data access patterns for diverse integration scenarios.

REST API v2 Limitation: MEQL is not supported in REST API version 2. Applications using REST API v2 must use that version's query syntax and capabilities. MEQL features including GROUP BY and aggregate functions are only available in REST API version 3.

Feature Support Summary

Here's the revised Feature Support Summary table:

Feature Support Summary

Best Practices and Recommendations

Query Construction

Start Simple: Begin with basic SELECT and FROM clauses, then add WHERE conditions incrementally. Test after each addition to verify expected behavior before adding complexity.

Use Entity Browser: Always verify entity and field names in the Entity Browser before writing queries. Correct case-sensitive naming prevents compilation errors and failed queries.

Comment Complex Queries: When constructing complex queries with multiple conditions or aggregates, maintain comments in code explaining query purpose, expected results, and any business logic the query implements.

Consistent Formatting: Format queries consistently with line breaks between major clauses, indentation for readability, and capitalization conventions for keywords. Consistent formatting improves maintainability and reduces errors.

WHERE Clause Best Practices

Indexed Fields First: When filtering with multiple conditions, place indexed field conditions first when possible. Database engines may optimize queries more effectively when indexed fields appear early in filter logic.

Specific Before General: Order conditions from most specific (highest selectivity) to most general. Specific conditions reduce result sets early in query processing, improving overall performance.

Avoid Unnecessary Functions: Don't apply functions to fields in WHERE clauses when direct comparisons suffice. Functions on fields may prevent index usage and reduce query performance.

Parentheses for Clarity: Use parentheses generously to make condition grouping explicit even when operator precedence would produce the same result. Explicit grouping improves query readability and prevents logic errors.

Aggregate Query Best Practices

Pre-Filter with WHERE: Always apply WHERE filters before GROUP BY to reduce the number of records being aggregated. Pre-filtering significantly improves aggregate query performance.

Match SELECT and GROUP BY: Ensure every non-aggregate field in SELECT appears in GROUP BY. Mismatches between SELECT and GROUP BY cause query errors.

Include ORDER BY: Always include ORDER BY in aggregate queries to ensure consistent, predictable result ordering. Aggregate results without ORDER BY may appear in arbitrary sequence.

Meaningful Aliases: Use descriptive aliases for aggregate function results like RowCount, TotalRevenue, or AverageValue rather than generic names. Descriptive aliases improve result set readability.

Pagination Best Practices

Always Include ORDER BY: Never paginate without ORDER BY. Unpredictable ordering causes inconsistent pagination where records may appear multiple times or be skipped across pages.

Consistent Sort Fields: Use the same ORDER BY field(s) across all pages of a paginated result set. Changing sort criteria between pages produces inconsistent, confusing pagination.

Track Total Count: When implementing pagination in applications, execute a separate COUNT query to determine total result count. Total count enables proper pagination UI including page numbers and navigation.

Page Size Considerations: Balance page size between user experience and performance. Typical page sizes of 10-100 records provide good user experience without excessive query overhead.

Performance Optimization

Limit Field Selection: Select only fields actually needed rather than all fields. Reducing field count decreases data transfer and improves query performance, especially for entities with many fields.

Avoid Complex Functions: Minimize use of complex functions in WHERE clauses, particularly on large datasets. Functions prevent index usage and increase query processing time.

Batch Large Operations: When retrieving more than 10,000 rows, implement efficient batching with consistent ORDER BY using unique identifiers. Batching with ID-based ordering ensures no records are skipped or duplicated.

Test with Production Volumes: Test query performance with production-scale data volumes. Queries performing well with test data may exhibit poor performance with large production datasets.

Security and Data Access

Respect Sharing Rules: MEQL queries respect Magentrix sharing rules and record-level security. Queries return only records the executing user has permission to access based on organizational security model.

User Context Awareness: Remember that query results vary by user based on their permissions, roles, and sharing rules. Applications should not assume all users see identical query results.

Sensitive Data Protection: Avoid logging or displaying complete query results that may contain sensitive data. Implement appropriate filtering and redaction when logging query information for debugging purposes.

Development and Debugging

Query Console Testing: Always test queries in Query Console before implementing in code. Console testing enables rapid iteration and validation without code compilation cycles.

Incremental Complexity: Build complex queries incrementally, validating each component. Incremental development enables early error detection and simplifies debugging when issues arise.

Result Verification: Compare query results against expected data to verify correctness. Manual verification of sample results ensures query logic produces intended filtering and aggregation.

Error Message Analysis: Read error messages carefully when queries fail. Error messages provide specific guidance about syntax errors, unknown fields, and unsupported features.

Code Integration

Parameterization: When constructing queries dynamically in code, use proper string concatenation or parameterization to safely include variable values. Avoid SQL injection patterns even though MEQL executes in controlled environment.

Error Handling: Implement comprehensive error handling around query execution in controllers and classes. Graceful error handling provides meaningful feedback when queries fail rather than exposing technical errors to users.

Result Processing: Process query results efficiently in code using appropriate data structures and algorithms. Avoid inefficient result processing that negates query performance optimization.

Caching Considerations: Consider caching query results when appropriate for frequently executed queries with slowly changing data. Caching reduces database load and improves application response times.

Troubleshooting Common Issues

Syntax Error Resolution

Keyword Misspelling: Verify that all SQL keywords are spelled correctly. Common misspellings include "SELELCT," "FORM," "WHER," "GROPU," or "ODER."

Missing Required Clauses: Ensure FROM clause is present, as it is required in all MEQL queries. SELECT is optional when retrieving all fields, but FROM must always be specified.

Unsupported Feature Usage: Review unsupported features list when errors indicate syntax problems. Attempting to use DISTINCT, IN, HAVING, or subqueries produces syntax errors.

Parentheses Matching: Verify that all opening parentheses have corresponding closing parentheses. Unmatched parentheses cause parsing errors that may report unclear error locations.

Field and Entity Name Issues

Case Sensitivity Verification: Use Entity Browser to verify exact capitalization of entity and field names. Case mismatches are the most common cause of "unknown entity" or "unknown field" errors.

Relationship Field Names: Distinguish between relationship field names and ID field names. Use relationship names for dot notation traversal and ID field names for direct foreign key access.

Custom Entity Suffix: Remember that custom entity names include __c suffix. Queries referencing custom entities without the suffix fail with "unknown entity" errors.

Field Existence Confirmation: Verify that referenced fields actually exist in the entity schema. Fields may be removed, renamed, or unavailable in specific environments causing query failures.

Aggregate Query Issues

GROUP BY Mismatch: Ensure all non-aggregate fields in SELECT appear in GROUP BY. Including fields in SELECT without corresponding GROUP BY entries causes aggregation errors.

Missing Aggregate Functions: When using GROUP BY, SELECT must include aggregate functions. Attempting to use GROUP BY without aggregates in SELECT produces errors.

ORDER BY Without GROUP BY Field: While ORDER BY can sort by any field, aggregate queries typically sort by grouping fields. Sorting by non-grouped fields may produce unexpected ordering.

Alias in WHERE Clause: Field aliases cannot be used in WHERE clauses. Reference original field names in WHERE even when aliases are defined in SELECT.

Pagination Problems

Missing ORDER BY with Offset: Offset-based pagination requires ORDER BY. Queries using LIMIT with offset without ORDER BY fail with error messages indicating missing ORDER BY.

Inconsistent Results Across Pages: Without ORDER BY, pagination produces inconsistent results where records appear multiple times or are skipped. Always include ORDER BY for pagination.

Offset Beyond Result Count: When offset exceeds available result count, queries return empty result sets. Validate that offset values are within expected result range before executing queries.

Large Offset Performance: Very large offset values may impact query performance as databases skip many records. Consider alternative pagination strategies for very large datasets.

Result Set Issues

Empty Results: When queries return no results unexpectedly, verify WHERE clause logic is correct, check that expected data exists in the entity, confirm field value formats match (dates, strings, etc.), and ensure user has access to expected records.

Unexpected Result Count: When result counts differ from expectations, review WHERE clause conditions for logic errors, verify that GROUP BY produces expected groupings, check for data quality issues like duplicates or missing values, and confirm understanding of entity relationships.

10,000 Row Limit Reached: When queries return exactly 10,000 rows, result set may be truncated. Implement pagination to retrieve additional rows if complete dataset is needed.

Performance Degradation: If queries execute slowly, reduce selected field count, simplify WHERE clause conditions, add appropriate LIMIT values, and review ORDER BY field indexing status.

Function and Operator Issues

Function Name Errors: Verify correct function spelling and capitalization. Function names like Contains, StartsWith, and String.IsNullOrEmpty are case-sensitive.

Incorrect Function Usage: Ensure functions are used on appropriate field types. String.IsNullOrEmpty works only on string fields; using on numeric or date fields causes errors.

Operator Misuse: Use correct comparison operators for data types. Date comparisons require string literals in ISO 8601 format; boolean fields require lowercase true/false values.

Logical Operator Precedence: Remember that AND has higher precedence than OR. Use parentheses to ensure correct logical grouping when combining AND and OR operators.

Additional Resources

Related Documentation

For comprehensive information about related topics and integration contexts, reference the following documentation:

Using IDE Tools - Query Console usage instructions, Event Log debugging, and Font Icon library access within the Integrated Development Environment.

Using the Entity Browser - Complete entity schema exploration, field reference insertion, and relationship discovery supporting accurate MEQL query construction.

REST API Version 3 Documentation - Comprehensive REST API integration guidance including MEQL query submission, result format specifications, and authentication requirements.

About IDE - Overview of the Integrated Development Environment including access instructions, interface organization, and development workflow patterns.

Query Console Integration

The Query Console in the IDE provides the primary interface for interactive MEQL query development and testing. Access Query Console through the Tools menu to execute queries, view results, and refine query logic before implementing in code.

Console Capabilities: Execute queries immediately with real-time results, view row counts and field values in tabular format, search and filter displayed results, and iterate rapidly on query development without code compilation.

Console Limitations: No result export functionality, results limited to browser display capabilities, 10,000 row maximum per query, and no query saving or history features.

Code Implementation

MEQL queries execute in Controllers, Classes, and Triggers using Magentrix SDK query methods. Queries return result sets that code processes for display, business logic, or data manipulation purposes.

Controller Usage: Controllers execute MEQL queries to retrieve data for Active Page display, prepare result sets for user interface rendering, and implement page-specific data access logic.

Class Usage: Classes execute MEQL queries in reusable methods called from multiple controllers or triggers, enabling centralized data access patterns and consistent query logic across customizations.

Trigger Usage: Triggers execute MEQL queries to retrieve related data during event processing, validate business rules against existing data, and prepare information for automated operations.

REST API Integration

External applications submit MEQL queries to REST API version 3 endpoints, receiving JSON or XML formatted results for programmatic processing. API integration enables data synchronization, reporting systems, and external application access to Magentrix data.

Authentication Requirements: REST API requests require proper authentication using API tokens or OAuth credentials. Consult REST API documentation for authentication configuration and request formatting.

Request Format: MEQL queries submit as URL parameters or request body content depending on API endpoint design. Follow REST API documentation for correct query submission format.

Response Processing: Applications parse JSON or XML response formats to extract query results, handle pagination, and process returned data according to integration requirements.

The comprehensive understanding of MEQL (Magentrix Entity Query Language) syntax, capabilities, limitations, and best practices enables efficient data retrieval, analysis, and integration supporting development activities, external system integration, and data-driven portal customization across organizational implementations.

MEQL Advanced Filtering and Functions >>