Query building using Kohana Query Builder

Question

If I got this:

    $result = 
    DB::select(
    'orders.date_created'
    )
    ->from('orders') 
    ->execute()->as_array();

For normal, dsiplaying all orders date_created. Now I can filter by user, doing this ?user_id=112

This will make me having this:

    if(isset($get['user_id']))
    {
        $result = 
        DB::select(
        'orders.date_created'
        )
        ->from('orders') 
                    ->where('user_id', '=', $get['user_id'])
        ->execute()->as_array();
    }else{
        $result = 
        DB::select(
        'orders.date_created'
        )
        ->from('orders')
        ->execute()->as_array();
    }

Although this is no problem for me have this, but this get to be real much ugly code when I have 4 params to filter out from(4 where statements) e.g ?user_id=112&quantity=2&...

Can I somehow make a inline if/action statement, which would grab all user_ids if there's nothing in $get['user_id'] ?

So I can end up having only this:

        $result = 
        DB::select(
        'orders.date_created'
        )
        ->from('orders') 
                    ->where('user_id', '=', (isset($get['user_id']) ? $get['user_id'] : 'GRAB ALL!'))
        ->execute()->as_array();

or is there another, even better way to do this?

Tried looking in Kohana docs if there's some ->if()->where()->exitif().. (just some imagination of how it could work)

biakaveron biakaveron · Accepted Answer · 2012-11-15T12:50:01

You can modify Query object before executing:

$query = DB::select()->from(...);
if (isset($get['user_id'])) {
    $query->where('user_id', '=', intval($get['user_id']));
}
if (isset($get['quantity'])) {
    $query->where('quantity', '=', floatval($get['quantity']));
}
$result = $query->execute()->as_array();

Query building using Kohana Query Builder

1 Answers