0

I have a Model from which I want to select all rows for which 'caller' or 'callee' is a given value for presentation in a Grid.

I have tried lots of different avenues of accomplishing this and cannot get anywhere with it.

I have a working filter on the Grid which narrows down the results by date (starting and ending dates), by status ("ANSWERED","NO_ANSWER"), I can also add conditions for 'caller' and 'callee', but how do I get it to show all rows where either 'caller' or 'callee' is a match to the current $UserID? Basically showing all calls (rows) a user was involved in?

The MySQL query itself is a simple OR construction, but how do I 'feed' it into the Model or the Grid so that it plays nicely with the other filters on the page?

Niek Klein Kromhof
  • 164
  • 8

1 Answers1

1

You can use orExpr() method of DSQL to generate SQL for your needs.

For example,

$m = $this->model->debug();            // enable debug mode
$user_id = $this->api->auth->model->id;// currently logged in user ID
$q = $m->_dsql();                      // get models DSQL

$or = $q->orExpr();                    // initialize OR DSQL object
$or->where('caller', $user_id)         // where statements will be joined with OR
   ->where('callee', $user_id);

// use one of these below. see which one works better for you
$q->where($or);                        // add condition with OR statements
$q->having($or);                       // add condition with OR statements

Of course you can write all of this shorter:

$m = $this->model->debug();            // enable debug mode
$user_id = $this->api->auth->model->id;// currently logged in user ID
$q = $m->_dsql();                      // get models DSQL

$q->where($q->orExpr()
    ->where('caller', $user_id)
    ->where('callee', $user_id)
);
DarkSide
  • 3,670
  • 1
  • 26
  • 34
  • Wow, great info, I will play around with this tomorrow. – Niek Klein Kromhof Jul 31 '13 at 17:04
  • Am I right in assuming that the _dsql() means that you're modifying the model's underlying DSQL? I think that was part of what I was struggling to figure out :-) – Niek Klein Kromhof Jul 31 '13 at 17:13
  • Yes _dsql() is models underlying DSQL, but dsql() (without underline prefix) is clone of models DSQL. I mix both of these regularly, so be aware of that :) – DarkSide Aug 01 '13 at 23:16
  • 1
    Maybe something like $m->addCondition($q->orExpr(...)) should work too, but I'm not sure. Anyway I have defined for myself rule of thumb to avoid mixing model methods with DSQL methods. If I need standard functionality for my SQL select, then I use models methods, but if I need more that standard (like orExpr or whatever), then I try to set all myconditions with DSQL. Otherwise it can get quite hard to follow if we use both at the same time. Also don't forget about model->debug() and dsql->debug() methods. These help quite often to understand what's happening behind the scenes :) – DarkSide Aug 01 '13 at 23:19
  • Yeah, I could see things getting murky if you mix the two, in this particular case I think it'll actually work out for me, but some caution is needed here :) – Niek Klein Kromhof Aug 02 '13 at 10:52