And Or Alternatives

Apples and Oranges

I’m in the process of adding some methods to the database object to help in writing chained queries for Garden. One of the annoying problems I’ve run into is that the words “or” and “and” are reserved and won’t work as method names. So, for example, imagine I wanted to write the following chained query:

$Database
     ->Select('FieldName')
     ->From('TableName')
     ->Where('FieldName', 'value')
     ->Or()
     ->Where('FieldName', 'othervalue')
     ->And()
     ->Where('FieldName', 'Yet Another Value')
     ->Get();

I realize that this query really doesn’t make much sense, but that’s beside the point. The “or” and the “and” methods can’t exist in PHP, so what should I use instead?

I’ve played with things like AndOp and OrOp, implying that they are operators. I’ve tried going for shorter versions like Nd and R, but those are just stupid and don’t make much sense. I’ve also considered having OrWhere and AndWhere methods, but that would mean adding a whole bunch of methods like that (think: OrWhere, AndWhere, OrLike, AndLike, OrWhereIn, AndWhereIn, etc).

I’m looking for community input here. What would you prefer to type?

14 Responses to “And Or Alternatives”

  1. Pasquale

    I know they look ugly, but what about an underscore prefix?

  2. Mark

    @Pasquale – that’s actually a good idea, but we’ve reserved the underscore prefix for methods that are private or protected (it’s nice when you’re working with an object and you can quickly know that something is private or protected if it’s got an underscore prefix). Any other ideas?

  3. Jeff

    I have seen OrWhere before. It works…

    Why use AndWhere? Why not just default to AND and if someone wants to use OR they would need to explicitly define it.

    $Database
    ->Select(‘FieldName’)
    ->From(‘TableName’)
    ->Where(‘column1′, ‘value1′)
    ->Where(‘column2′, ‘value2′)
    ->OrWhere(‘column3′, ‘value3′)
    ->Get();

    Would produce something like:
    SELECT FieldName FROM TableName WHERE column1 = ‘value1′ AND column2 = ‘value2′ OR column3 = ‘value3′;

  4. Mark

    “I’ve also considered having OrWhere and AndWhere methods, but that would mean adding a whole bunch of methods like that (think: OrWhere, AndWhere, OrLike, AndLike, OrWhereIn, AndWhereIn, etc).”

    The way I’m writing it you will need to switch from or to and. Typically you only ever have ors together or ands together because mixing them never makes sense (ie.”where a = b and c = d and e = f” makes sense, but “where a = b and c = d or e = f” really means the same as “where a = b or c = d or e = f” – see what I mean?).

    Regardless, even if I only needed an “Or” method, it would still require a bunch like OrWhere, OrWhereIn, OrLike, OrHaving, etc. It makes more sense to have it be “Or” but that word is reserved. So, we’re back to square one.

  5. Karl Katzke

    Mark, I hate to rain on a parade here — but why are you reinventing the wheel? Doctrine, Zend_Db, and the 30 other database abstraction objects for PHP have already settled this question at least a dozen times that I can think of.

  6. [-Stash-]

    BoolOr / BoolAnd
    LgclOr / LgclAnd
    +Or / +And
    ?

    I don’t know if you can even use a + for names in PHP, but what about any other special character that doesn’t look like a hyphen or underscore? !@£$%^&*()€#¶•ªº¡~

  7. Benjamin__

    I believe, as has been stated, that the standard has become using where and orwhere, like, orlike, notlike, ornotlike, etc. It’s a lot of methods, but it is easy to ready, and as I said, it’s the standard inside frameworks.

  8. Scubaguy

    AndOp and OrOp works for me

  9. Adrian

    Why use a method…

    Why not something like:

    $Database
    ->Select(‘FieldName’)
    ->From(‘TableName’)
    ->Where(‘And’, ‘FieldName’, ‘value’)
    ->Where(‘And’, ‘FieldName’, ‘othervalue’)
    ->Where(‘Or’, ‘FieldName’, ‘Yet Another Value’)
    ->Get();

  10. Ian

    I personally would prefer codeigniters’ active record approach to doing things: where AND is assumed in where chains and OR has to be specified.

    AND

    $this->db->select(‘title’)->from(‘mytable’)->where(‘id’, $id);

    OR

    $this->db->where(‘name !=’, $name)->or_where(‘id >’, $id);

  11. Charles

    @Karl – The same could be said about a building a forum, why do it when there are others that have already done it? Competition, new perspectives, and different problem domains spur innovation in application design.

    I tend to also prefer ‘AND’ is assumed and you can specify the operator in some other manner if you want (i tend to write far more ‘AND’ queries)

    Fwiw, I very much dislike Zend Framework as a framework, although I do like many of the independent classes. I use my own custom framework that has some similarities to Mark’s, although I see a large divergence in the DB abstraction here. If you are interested in looking at my php 5 framework, you can view it in a recent password manager application I released:

    http://www.forthecode.org/2009/05/11/kumasafe-secure-storage-free-download/

    Mark – I’ve been silently following for sometime and say “ganbatte kudasai!”, I love to learn by observing what other people do differently. I’m eager to take a look at the guts of this system ^-^

  12. Mark

    @Charles – Thanks for the comment, and for following my progress (even if silently :)

    Kumasafe looks pretty awesome, so ganbatte kudasai, yourself!

  13. Daniel

    for the sql builder i made a while back, i’ve done it like this.

    $sql->where(null, ‘foo’, ‘=’, ‘bar’, 1, 1, 1);
    $sql->where(‘and’, ‘bar’, ‘=’, ‘baz’, 1, 1, 1);
    $sql->where(‘or’, ‘foo’, ‘=’, ‘bar’, 1, 1, 2);
    $sql->where(‘and’, ‘bar’, ‘=’, ‘baz’, 1, 1, 2);

    function where($operator, $lvalue, $logic, $rValue, $lQuote = 0, $rQuote = 1, $group);

  14. Daniel

    And that builds out this:

    WHERE (‘foo’ = ‘bar’ AND ‘bar’ = ‘baz’) OR (‘foo’ = ‘bar’ AND ‘bar’ = ‘baz’)

Leave a Reply