WHERE ... OR ...

[Kal-Aster]

Hi I really like this library, thank you. It seems like it hasn't been touched since 2 years and I don't know if it has still a maintainance, however I didn't understand if there is the support for WHERE ... OR ... statements. If not, will they be implemented?

[jasny]

You can use where("abc = ? OR def = ?", [1, 9]).

It isn't easily possible to add a WHERE statement to OR the existing statement. As a workaround you can do

$query->where($query->getPart('where') . " OR abc = ?", $value, Query::REPLACE);

---

I have plans to update this library and will consider this feature. Possible syntax could be

$query->orWhere("abc", $value);

[jasny]

I'm unsure what the following code should produce

$query = new Query("SELECT * FROM foo WHERE a = 1");
$query->where("b = 2");
$query->orWhere("c = 3");
$query->where("d = 4");
$query->orWhere("e = 5");

This would produce something like

SELECT * from foo WHERE (((a = 1 AND b = 2) OR (c = 3)) AND d = 4) OR (e = 5)

But it feels unlikely that that's the intended outcome.

For now, the solution proposed as workaround seems like the best option.

[Kal-Aster]

I think that any new ->where should replace anything done before, so only ->andWhere and ->orWhere would append a condition. Does it make sense?

[Kal-Aster]

Or probably the ->where method could return an object having an ->or and ->and and ->end methods

$query->where('d=4')->or('c=3');
$query->where('a=2')->or('b=4');

This will produce: WHERE (d=4 OR c=3) AND (a=2 OR b=4)

[jasny]

Having where() return a new object still doesn't allow us to add an or condition to an existing query. It also breaks the fluent interface pattern that Query has.

As example, the above condition can already be accomplished with

$query
    ->where('d = ? or c = ?', [4, 3])
    ->where('a = ? or b = ?', [2, 4]);

The difficulty lies in applying both or and and conditions to an existing where statement.

[Kal-Aster]

What about this?

$query
    ->where('d = ? or c = ?', [4, 3])
    ->where('a = ? or b = ?', [2, 4]);
// WHERE a=2 or b=4

$query
    ->where('d = ? or c = ?', [4, 3])
    ->andWhere('a = ? or b = ?', [2, 4]);
// WHERE (d=4 or c=3) AND (a=2 or b=4)

$query
    ->where('d = ? or c = ?', [4, 3])
    ->orWhere('a = ? or b = ?', [2, 4]);
// WHERE (d=4 or c=3) OR (a=2 or b=4)

$query
    ->orWhere('a = ? or b = ?', [2, 4]);
// WHERE (a=2 or b=4)

$query
    ->andWhere('a = ? or b = ?', [2, 4]);
// WHERE (a=2 or b=4)

So the where method overrides anything and the andWhere and orWhere methods appends to the existing condition, if any, with the corresponding operator

[jasny]

@Kal-Aster That doesn't solve anything. This issue isn't in where overwriting the current statement or not. The issue is in combining andWhere() and orWhere() statements.

$query = new Query("SELECT * FROM foo WHERE a = 1");
$query->andWhere("b = 2");
$query->orWhere("c = 3");
$query->andWhere("d = 4");
$query->orWhere("e = 5");

What should be the outcome of this statement? That's not obvious.

[Kal-Aster]

Well it actually solves it, giving the user the thoughtfulness of ordering well the conditions.

$query = new Query("SELECT * FROM foo WHERE a = 1");
$query->andWhere("b = 2");
$query->orWhere("c = 3");
$query->andWhere("d = 4");
$query->orWhere("e = 5");

this would be:

SELECT * FROM foo WHERE a=1 AND (b=2) OR (c=3) AND (d=4) OR (e=5)

This query will follw the rule of precedence of operators and if the user wants to OR something strictly with another, it would be hir care to put that inside the *Where call.

Let's say we want to compare b and c togheter and then the rest, we'll do:

$query = new Query("SELECT * FROM foo WHERE a = 1");
$query->andWhere("b = 2 OR c=3");
$query->andWhere("d = 4");
$query->orWhere("e = 5");

and this would be:

SELECT * FROM foo WHERE a=1 AND (b=2 OR c=3) AND (d=4) OR (e=5)

Well, this is my point, I don't know if it makes sense 😅

[MikeMarschall]

+1 for the the solution presented by @Kal-Aster – as the PHP DSL is just a (thin) wrapper of the underlying SQL DSL, the cognitive overhead of automatic grouping seems unfavorable over a concise interface, so:

$query = new Query("SELECT * FROM foo WHERE a = 1");
echo $query->asString() . PHP_EOL;

$query->where("b = 2");
$query->orWhere("c = 3");
echo $query->asString() . PHP_EOL;

$query->where("d = 4");
$query->orWhere("e = 5");
echo $query->asString() . PHP_EOL;

$query->andWhere("f = 6");
$query->orWhere("g = 7");
echo $query->asString() . PHP_EOL;

$query->where("h = 1");
$query->orWhere("h = 2");
$query->orWhere("h = 3");
echo $query->asString() . PHP_EOL;

$query->where("i = 1");
$query->orWhere("(i = 2 AND i = 3)");
$query->andWhere("(i = 4 OR i = 5)");
echo $query->asString() . PHP_EOL;

should output something like this:

SELECT * FROM foo WHERE a = 1
SELECT * FROM foo WHERE b = 2 OR c = 3
SELECT * FROM foo WHERE d = 4 OR e = 5
SELECT * FROM foo WHERE d = 4 OR e = 5 AND f = 6 OR g = 7
SELECT * FROM foo WHERE h = 1 OR h = 2 OR h = 3
SELECT * FROM foo WHERE i = 1 OR (i = 2 AND i = 3) AND (i = 4 OR i = 5)

when implemented in this way, you can still at some point introduce an expression builder abstraction, so things like the following would be possible:

$expr = new ExpressionBuilder(/* sql dialect seems like a reasonable parameter */);

$query = new Query("SELECT * FROM foo WHERE a = 1");
$query->orWhere($expr->and_(
    $expr->eq("b", "2"),
    $expr->eq("c", "3"),
    $expr->eq("d", "4")
));
$query->andWhere($expr->or_(
    "e = 5",
    $expr->gt("e", 10)
));
echo $query->asString() . PHP_EOL;

which could output something like the following:

SELECT * FROM foo WHERE a = 1 OR (b = 2 AND c = 3 AND d = 4) AND (e = 5 OR e > 10)