OSSN 8.8 simplified wheres builder using helper methods

Simplified Database Querying with wheres Helpers

These two helper methods simplify query construction by handling array formatting and ensuring that all data is passed securely via prepared statements. They completely replace the need for manually creating complex nested arrays.

1. Simple Conditions / Flat Queries: OssnDatabase::wheres()

Use this function to generate a single condition in your WHERE clause. It handles the secure binding of your values.

Key Principle:

Conditions are linked by the default AND operator unless you explicitly set the optional separator to 'OR'. This is used for all flat WHERE clauses that do not require parentheses.

Function Signature

/**
 * Creates a single condition for a WHERE clause.
 */
OssnDatabase::wheres(
    string $name,           // Column name or SQL function (e.g., 'u.guid', "CONCAT(u.first_name, ' ', u.last_name)")
    string $comparator,     // Comparator (e.g., '=', 'LIKE', '!=', 'IN')
    mixed $value,           // Value to bind (e.g., $user_guid, '%search_term%')
    ?string $separator = null // Optional: 'OR' to override the default 'AND'
): array

Usage Examples

Default AND Chain

SQL Goal: WHERE u.guid = ? AND type = ?

Helper Code:

$wheres = array(
    OssnDatabase::wheres('u.guid', '=', $user_id),
    OssnDatabase::wheres('type', '=', 'profile')
);

Explanation:
Conditions are linked by the default AND operator since no separator is provided.

Using Raw SQL with Default AND

SQL Goal: WHERE u.guid != ? AND CONCAT(...) LIKE ?

Helper Code:

$wheres = array(
    OssnDatabase::wheres('u.guid', '!=', $user_id),
    OssnDatabase::wheres("CONCAT(u.first_name, ' ', u.last_name)", 'LIKE', $search_term)
);

Explanation:
The raw SQL function is used directly in the $name. Both conditions are linked by the default AND.

Simple OR Link

SQL Goal: WHERE c1 = ? OR c2 = ?

Helper Code:

$wheres = array(
    OssnDatabase::wheres('c1', '=', $val1, 'OR'),
    OssnDatabase::wheres('c2', '=', $val2)
);

Explanation:
Uses the 'OR' separator on the first condition to link it to the next.

Mixed OR and AND Logic

SQL Goal: WHERE A = ? OR B = ? AND C = ?

Helper Code:

$wheres = array(
    OssnDatabase::wheres('A', '=', 1, 'OR'),
    OssnDatabase::wheres('B', '=', 2),
    OssnDatabase::wheres('C', '=', 3)
);

Explanation:
The OR on 'A' links to 'B'. 'B' links to 'C' using the default AND. SQL's operator precedence interprets this as A = ? OR (B = ? AND C = ?).

2. Nested Logic: OssnDatabase::wheresGroup()

This is essential for introducing parentheses () and controlling complex logic (like mixing AND and OR). The connector argument dictates the logical glue inside the new set of parentheses.

Function Signature

/**
 * Creates a structured array for a nested WHERE group (i.e., parentheses).
 */
OssnDatabase::wheresGroup(
    string $connector,      // The glue for conditions *inside* the group (e.g., 'AND', 'OR')
    array $group,           // Array of conditions created by OssnDatabase::wheres()
    ?string $separator = null // Optional: Separator for linking this entire group to the next condition
): array

Complex Example: Bidirectional OR Logic

This translates the complex SQL logic: WHERE ((A AND B) OR (C AND D))

Helper Code:

$wheres = array(
    // OUTER GROUP: Uses 'OR' to join its two child groups. Result: ( [AND Group 1] OR [AND Group 2] )
    OssnDatabase::wheresGroup('OR', [ 
        
        // INNER GROUP 1: Uses 'AND' for (A AND B)
        OssnDatabase::wheresGroup('AND', [
            OssnDatabase::wheres('message_from', '=', $from),
            OssnDatabase::wheres('message_to',   '=', $to),
        ]),
        
        // INNER GROUP 2: Uses 'AND' for (C AND D)
        OssnDatabase::wheresGroup('AND', [
            OssnDatabase::wheres('message_from', '=', $to),
            OssnDatabase::wheres('message_to',   '=', $from),
        ]),
    ]),
);

Explanation:

• OUTER GROUP: Uses 'OR' to join its two child groups. This results in ( [AND Group 1] OR [AND Group 2] ).
• INNER GROUPS: Both inner groups use 'AND' to combine conditions like (A AND B) and (C AND D).

Conclusion:

Using these two helpers together makes your SQL logic transparent and completely secure. You can easily manage simple conditions, complex logic, and nested conditions with built-in protection against SQL injection.