Advanced Query Builder Documentation

This documentation outlines the structure for generating complex WHERE clauses using the group-centric array format. This approach ensures explicit control over logical connectors (AND/OR), making complex, nested queries robust and readable.

Core Principles

• Default Connector: Conditions at the same array level are joined by the default connector (usually AND).
• Explicit Grouping: Use a Group Array ('group' key) to force parentheses (...) and control the internal connector.
• Security: All values are automatically escaped and bound as placeholders (?).

Key Distinction: connector vs. separator

These two keys control linkage at different structural levels:

1. Simple Condition (No Grouping)

For a simple condition, the array contains the column name, comparator, and value.

Example 1: Overriding with separator

The default connector is AND. The separator on condition A forces an OR link to condition B.

// SQL: WHERE ( (A = ?) OR (B = ?) AND (C = ?) )
$wheres = array(
    // Condition A: (A = 1) is OR-ed to the next item
    array(
        'name'       => 'A',
        'comparator' => '=',
        'value'      => 1,
        'separator'  => 'OR', 
    ),
    // Condition B: (B = 2) is AND-ed to the next item (default)
    array(
        'name'       => 'B',
        'comparator' => '=',
        'value'      => 2,
    ),
    // Condition C: (C = 3)
    array(
        'name'       => 'C',
        'comparator' => '=',
        'value'      => 3,
    ),
);

2. Explicit Grouped Conditions

Use this structure to create parentheses (...) and control the internal logical operator using connector.

Example 2: Complex OR Search (Using connector)

This group uses connector: 'OR' to find a keyword in any of the three fields.

// SQL: WHERE ( (CONCAT(...) LIKE ?) OR (u.username LIKE ?) OR (u.email LIKE ?) )
$wheres = array(
    // Single item which is a group
    array(
        'connector' => 'OR', // <-- Internal Glue
        'group'     => array(
            array('name' => "CONCAT(u.first_name, ' ', u.last_name)", 'comparator' => 'LIKE', 'value' => "%{$keyword}%"),
            array('name' => 'u.username', 'comparator' => 'LIKE', 'value' => "%{$keyword}%"),
            array('name' => 'u.email', 'comparator' => 'LIKE', 'value' => "%{$keyword}%"),
        ),
    ),
);

Example 3: Friend Request Check (Nested OR/AND)

This structure ensures correct friend request deletion regardless of direction:
WHERE ( (A->B AND type) OR (B->A AND type) )

$from = 10;
$to = 20;

$wheres = array(
    // OUTER GROUP: Forces OR between the two directional checks below
    array(
        'connector' => 'OR',
        'group' => array(
            
            // DIRECTION 1 GROUP: (relation_from = A AND relation_to = B AND type = request)
            array(
                'connector' => 'AND', // Internal logic is AND
                'group' => array(
                    array('name' => 'relation_from', 'comparator' => '=', 'value' => $from),
                    array('name' => 'relation_to',   'comparator' => '=', 'value' => $to),
                    array('name' => 'type',          'comparator' => '=', 'value' => 'friend:request'),
                ),
            ),
            
            // DIRECTION 2 GROUP: (relation_from = B AND relation_to = A AND type = request)
            // This entire group is OR-ed with the first group because of the parent's 'connector' => 'OR'
            array(
                'connector' => 'AND', // Internal logic is AND
                'group' => array(
                    array('name' => 'relation_from', 'comparator' => '=', 'value' => $to),
                    array('name' => 'relation_to',   'comparator' => '=', 'value' => $from),
                    array('name' => 'type',          'comparator' => '=', 'value' => 'friend:request'),
                ),
            ),
        ),
    ),
);

Another example that can be written two ways

1. Using simple separator

$wheres = array(
    // Condition 1: Use separator to force the OR link
    array(
        'name'       => 'a.message_to',
        'comparator' => '=',
        'value'      => $guid,
        'separator'  => 'OR', // <-- Overrides the default AND
    ),
    // Condition 2: Linked by the previous OR
    array(
        'name'       => 'a.message_from',
        'comparator' => '=',
        'value'      => $guid,
    ),
);

2. Using group connector

$wheres = array(
    // Outer Group: Uses 'OR' to join the two conditions
    array(
        'connector' => 'OR', // <-- Explicitly sets the internal glue to OR
        'group'     => array(
            // Condition 1: a.message_to = $guid
            array('name' => 'a.message_to', 'comparator' => '=', 'value' => $guid),
            
            // Condition 2: a.message_from = $guid
            array('name' => 'a.message_from', 'comparator' => '=', 'value' => $guid),
        ),
    ),
);

Linkage Rules Summary

We essentially defined the internal versus external linkage rules:

The Two Linkage Rules Reaffirmed

1. connector is for Internal Group Logic (Parentheses Glue)

When you see a group array, the connector defines the default glue for all the items inside that group.

array(
    'connector' => 'OR', // <-- This is the glue
    'group' => array(
        // ITEM A --[OR]-- ITEM B --[OR]-- ITEM C
    ),
)

2. separator is for External Item Logic (Sibling Glue)

The separator is placed on the item that just finished to change the default operator used to connect it to the next item.

// Assuming default is 'AND'
array(
    // ITEM 1
    array( 'name' => 'X', 'value' => 1, 'separator' => 'OR' ), // <-- ITEM 1 is linked to ITEM 2 with OR
    
    // ITEM 2
    array( 'name' => 'Y', 'value' => 2 ), // <-- ITEM 2 is linked to ITEM 3 with AND (default)
    
    // ITEM 3
    array( 'name' => 'Z', 'value' => 3 ),
)

Summary

This documentation provides a detailed structure for building dynamic SQL queries using a group-centric array format. It offers flexibility for controlling logical connectors (AND/OR) and ensures clarity when constructing complex queries.