FuelPHP orm multiple AND,OR query

Question

How can I execute this query using orm in fuelPHP?

SELECT * FROM user when a=1 and b=2 and (c=1 or d=2 or e=3) 

I don't know how to implement this: and (c=1 or d=2 or e=3)

I am stuck at:

 $where = array()
 $where[] = array('a'=>1,'b'=>2);

 //Adding c,d,e column in $where[]
  I dont know how to combine AND and OR

     $query = Model_User::find('all', array(
                          'where' => $where
                  ));)

Is there any way to accomplish this?

Answer 1

You can use where_or like this:

$q = Model_User::find('all', array(
    'where' => array(
    array('a', 1),
        'and' => array(
            array('b', 2),
        ),
    ),
));

or indeed like this (which i find much easier to read when selecting:

$q = Model_User::query();
$q->where('a', '=', 1);
$q->and_where('b', '=', 2);
$q->or_where_open();
  $q->where('c', '=', 1);
  $q->or_where('d', '=', 2);
  $q->or_where('e', '=', 3);
$q->or_where_close();

Answer 2

You can also make use of it in this manner or take a look at this link for more light
Query builder Where - Classes FuelPHP documentation

//this should be done within the model that you wish to deal with
$query = DB::select("*");
$query->from->("user");
$query->where("a","=","1");
$query->and_where("b","=","2");
$query->where(function($query){
$query->where("c","=","1");
$query->or_where("d","=","2");
$query->or_where("e","=","3");
});
$query->execute();

Answer 3

You can chain, so this is even more readable:

$q = Model_User::query()
     ->where('a', '=', 1)
     ->and_where('b', '=', 2)
     ->or_where_open()
        ->where('c', '=', 1)
        ->or_where('d', '=', 2)
        ->or_where('e', '=', 3)
   ->or_where_close();

If you want to use DB queries for performance, but still need ORM model objects as result, you can use as_object(), see http://fuelphp.com/docs/packages/orm/crud.html#/custom_sql