Support for multiple grouping of 'OR' and 'AND' where clauses

[shaq147]

I need to be able to build queries such as: SELECT * FROM tablename WHERE a = 1 AND (b=2 OR c=3)

This should be the case when I write: $db->select()->from('tablename')->where('a', '=', 1)->where('b', '=', 2)->orWhere('c', '=', 3)

But this does not provide any bracket grouping. Whenever a where clause is followed by an orWhere, how can we indicate grouping of or's?

Cheers.

[kwhat]

Hi @shaq147,

If you take a look at the dev-v2 PR #80 that I have open, this address that problem with more flexible syntax. You can grab a copy of the v2 branch from my fork.

Best, Alex

[shaq147]

Thanks @kwhat

I took a quick look at your changes. Before I dig deeper into this and merge it with my current version, I was wondering if you can give a small example of the syntax of how the grouping of where clauses works to build the query?

[kwhat]

@shaq147, Take a look at the docs and comments on the same PR. Basically it goes a little something like this:

$select = $pdo->select(array(
        "col_one",
        "col_two" => "alias_two"
    ))
    ->from("your_table")
    ->where(
        new \Slim\PDO\Clause\Grouping("AND", array(
             new Clause\Conditional("col_one", ">=", 1000),
             new \Slim\PDO\Clause\Grouping("OR", array(
                 new Clause\Conditional("alias_two", "LIKE", "Foo%"),
                 new Clause\Conditional("alias_two", "LIKE", "Bar%"),
             ));
        ));
    );

Will produce the following SQL: SELECT col_one, col_two AS alias_two FROM your_table WHERE (col_one >= 1000 AND (alias_two LIKE "Foo%" OR alias_two LIKE "Bar%"));

There is no limit to the number of nested conditionals.

[shaq147]

@kwhat Dear lord. Creating classes like that gets too complex for when I've got people reading the code, and not good practice. I was hoping the finalized query (a more complex version of your case) could look something like:

$pdo->select(array(
        "col_one",
        "col_two",
        "col_three",
        "col_four"
    ))
    ->from("your_table")
    ->where("col_1", ">=", 1000),
    ->where(
            array("col_two", "col_three", "col_four")
            array("LIKE", "=", "IN")
            array("Bar%", "Foo", array(123, 456, 789))
        )
    ->orWhere("col_two", "!=", "FooBar");

To create a query:

SELECT col_one, col_two, col_three, col_four 
FROM your_table 
WHERE (col_1 >= 1000) 
AND (
    col_two LIKE "Bar%" OR 
    col_three = "Foo" OR
    col_four IN (123, 456, 789)
) 
OR (col_two != "FooBar")

Which basically means if there where(field, exp, val) function receives an array of count > 1 for it's field parameter, they get injected by ORs. field, exp and val will have to have the same length array for index mapping. I might just implement this myself if I get around to it.

However, I'm not sure about the possibility of multi level groupings like you have. Any thoughts?

[kwhat]

@shaq147 Whats the difference between object and array creation in modern PHP? If your doing OOP, classes are far easier to follow.

Its going to get tricky to implement what you are talking about in the underlying query builder. Its further complicated the more conditions you have. For example:

SELECT col_one, col_two, col_three, col_four 
FROM your_table 
WHERE (
    col_1 >= 1000
    AND (
        col_two LIKE "Bar%" 
        AND (
            col_three = "Foo" OR
            col_four IN (123, 456, 789)
        )
    ) 
)
OR (col_two != "FooBar")

You maybe able to figure out a way to patch it in. Maybe by taking an array into andWhere() or orWhere() and using the qualifier to join the array statements. You probably want to verify that each sub array contains the valid 3 args, so there is some extra work that needs to be done.

[kwhat]

So to answer my somewhat rhetorical question; In PHP 5.6 arrays vs objects is a memroy for time trade-off with objects using less memory and arrays being faster. Based on what I have seen in PHP 7+, the difference is negligible. If you are using anything less than 5.6, it's time to upgrade. https://gist.github.com/Thinkscape/1136563#gistcomment-1672635

[shaq147]

Well, I would say there's a line between simplicity and the time efficiency trade off. After thinking about this, I think the best way to handle these nested AND/ORs in the whereClause would be to take further control of the query build.

Ex query to build:

SELECT * FROM table WHERE
(col1 = 'value1') AND
( (col2 = 'value2') OR (col3 = 'value3' AND col4 = 'value4') AND (col5 = 'value5') )

Becomes

select()->from('table')
->where('col1 = ?', 'value1')
->where( 
        (col2 = ?) OR (col3 = ? AND col4 = ?) AND (col5 = ?), 
        array('value2', 'value3', 'value4', 'value5')
     )

Which means the where function takes in 2 parameters instead of 3. Can you think of a query a function like this can't build?

[shaq147]

If anybody is interested, here are the functions that need to be changed to support the multiple grouping using the select build I wrote above:

PDO/Statement/StatementContainer.php - 1 function replacement

public function where($expression, $values = null, $chainType = 'AND')
{
    $values = (array) $values;
    foreach($values as $value) {
        $this->values[] = $value;
    }

    $this->whereClause->where($expression, $chainType);

    return $this;
}

PDO/Clause/WhereClause.php - 2 function replacements

public function where($expression, $chainType = 'AND')
{
    $this->container[] = ' '.$chainType.' ('.$expression.') ';
}

public function orWhere($expression)
{
    $this->where($expression, 'OR');
}

Here's some use cases:

$db->select()->from('table')->where('column = ?', '999')
$db->select()->from('table')->where('column = ? OR column2 IN (?, ?, ?), array(999, 1, 2, 3))
$db->select()->from('table')->where('column = (SELECT id FROM table2 WHERE id = ? OR id = ?)', array(999, 888))

Note: These are equivalent:

$db->select()->from('table')->where('column LIKE ?',  %something%)
$db->select()->from('table')->whereLike('column', '%something%')

These are also equivalent:

$db->select()->from('table')->where('column IN (?, ?, ?)', array(1, 2, 3))
$db->select()->from('table')->whereIn('column', array(1, 2, 3))

The where() simply provides deeper control of your where clauses.

[kwhat]

This is now fully functional in 2.x so I am closing this issue.