Better support for OR operator about notorm HOT 14 CLOSED

You are right, thanks for the ideas. What do you think about ->where("id", $array)->or("attr", $scalar)? It doesn't cover all possible cases but it seems most natural to me. I would probably also add ->and() as an alias to ->where(). The only problem is that or and and are keywords...

from notorm.

I like the idea, so it will be possible to do the code below naturally, which is very readable with the cost of having both and & or as keywords.

$db->user()
    ->where('id', 1)
    ->and(array(
        'id' => '2',
        'active' => true
    ))
    ->or(array(
        'id' => '2',
        'active' => true
    ));

By seeing the code, I believe we can turn this powerful recursive call to do several layer of conditions, by having a constant to alternate the OR/AND in between conditions.

CODE:
$db->user()
    ->where('id', 1)
    ->and(array(
        'id' => '2',
        new NotORM_Where_And('active', true)
    ))
    ->or(array(
        'id' => '2',
        new NotORM_Where_Or('active', true),
        array(
            'id' => 3,
            new NotORM_Where_Or('active', true)
        )
    ));

OUTPUT:
SELECT *
FROM user
WHERE (id = 1)
    AND (id = 2 AND active != 0)
    OR (id = 2 OR active != 0 OR (
        id = 3
        OR 'active' != 0
    ))

It will be powerful and unfortunately a bit ugly. Maybe there is a better idea to this?

from notorm.

It could be done using the __call method.

from notorm.

Basically, this will work as well.

$db->user()->where(new NotORM_Where_Or('active', true));
$db->user()->or('active', true); // Equal to above

from notorm.

I was actually refering to the keyword problem. It's not possible to name a method 'or' or 'and' but you can implement them using __call.

from notorm.

Re vrana: I like your idea of passing arguments directly to and() and or().
I'm not sure about where() always being equal to and() because of the semantic meaning of the word "where" in SQL. The good thing about where() = and() would be sort of a backward compatibility.

Re smasty/__call(): Good idea. Tested in PHP 5.3.5 and 5.1.6.

I realize now that we may still need a support for a multidimensional array. We couldn't otherwise build a queries like this:

a] ...WHERE (id=1) OR (id = 2) AND (admin = true) AND (active = true)

which I believe is the same thing as
WHERE ((id=1) OR (id = 2)) AND (admin = true) AND (active = true)

b] ...WHERE (id=1) OR ((id = 2) AND (admin = true) AND (active = true))
c] ...WHERE ((id=1) OR ((id = 2) AND (admin = true))) AND (active = true)

To produce a] I could simply call

a] $my_table->where('id', 1)->or('id', 2)->and('admin', true)->and('active', true)

There's no way however, to create b] or c].

It would be great if we could nest calls to or()/where()/and() but I'm not sure how to go about it. Kind of like:

b] $my_table->where('id', 1)->or(
      new NotORM_self('id', 2)->and('admin', true)->and('active', true)
    )
c] $my_table->or( 
      new NotORM_self('id', 1)->or( 
        new NotORM_self('id', 2)->and('admin', true)
      )
    )->and('active', true))

This isn't very easy to read though. The multidimensional array looks better to me.

from notorm.

I don't know NotORM well but I think this could work:

b] $my_table->where('id', 1)->or('id = ? AND admin = ? AND active = ?', 1, true, true)

To the where() = and() and also the __call.
I've been using it this way for a while now in my database layer and there are no problems with where()=and(). You can use where()->where()->where() as well as where()->and()->and(). The only difference is the second one looks more SQL-like and thus more natural.

from notorm.

Hey Smasty, I think I also don't understand what Jakub means with AND and OR as keyword. Still waiting for his reply on this matter and my other issue.

from notorm.

@wendynovianto I know what is he refering to. AND and OR are reserved PHP keywords and thus cannot be used as names of a real method/function. However, this can be bypassed using the PHP magic __call() method. But it's not that ideal, since such methods are not suggested by IDE when typing and they are bit slower than regular methods.

The IDE support can be bypassed using the @method annotation on the class (works at least in NetBeans IDE).
The performance issue will still remain, however, since NotORM uses many magic methods I don't see this as a big problem.

Anyhow, I think if Jakub thinks it's a good feature he is probably implementing it as we speek (or he is at work ;-)).

(P.S. to mention someone here on github, you can use the 'at' notation the same way as you would on Twitter: @wendynovianto.)

from notorm.

Hey @smasty, it's a very useful info there on both php keywords and twitter mention!

from notorm.

I've implemented a very simple OR support here: https://github.com/rixbeck/notorm
It's just evaluates AND OR in order, unfortunately doesn't support nesting in bracket.

from notorm.

I need this query with notORM for a search system.

$query = "SELECT * FROM users WHERE (name LIKE '%".$q."%') OR (identification LIKE '%".$q."%') OR (lastame LIKE '%".$q."%')";

I've tried

$result['usuarios'] = $db->users()->where('name = ? OR lastname = ?', '$q' , '$q');

But not work, helpme please

from notorm.

This is now implemented:

$db->table()->where("a", 1)->or("b", 2);
$db->table()->where("a", 1)->and("b", 2);
$db->table()->where("(a", 1)->or("b", 2)->where(")")->and("(a", 2)->or("b", 1)->where(")");

The parentheses syntax is kind of hacky but it's not necessary to use very often.

from notorm.

@cristiamdiaz, you can use this:

$like = "%$q%";
$db->users()->where('name LIKE ? OR identification LIKE ? OR lastmame LIKE ?', $like, $like, $like);

from notorm.