My opinion? Query Builder (QB) is a great tool, but like any tool, it isn't appropriate for every task.
Most often I rely on good old
$this->db->query(). And when the query requires inputs use
Query Bindings so the values are escaped. The most important thing to know about QB is that it takes all the QB methods you called to create a string and then, quite literally, calls
query() using the constructed string. Very often it makes little sense to jump through all the QB hoops just to build a simple query string, e.g.
PHP Code:
$query = $this->db->query("SELECT * from events");
The real value of QB - where it really shines - is when you want to produce different query strings based on some condition.
Here's a very partial list of potential changes/conditions.
- Additional or different "where" clause(s)
- Change sort order
- A different set of fields
- Insert vs Update
Imagine a situation where you want to implement a user-defined search. The search input allows the selection of lots of options e.g. equal, not-equal like, not-like, ends with, begins with, etc. Here's a QB implementation from a "real" project. The final query also included sort order and limit/offset.
PHP Code:
/**
* Adds a where clause to the query based on the params
* @param string $search_option - The switch statement shows the valid options
* @param mixed $search_value - What to search for. Must be array if option is 'in' or 'ni'
* @param string $search_field - Name of the field to be searched
*/
protected function set_search($search_option, $search_value, $search_field)
{
switch ($search_option)
{
case 'cn': //'contains'
$this->db->like($search_field, $search_value);
break;
case 'nc': //'does not contain'
$this->db->not_like($search_field, $search_value);
break;
case 'eq': //'equal'
$this->db->where($search_field, $search_value);
break;
case 'ne':
//'not equal'
$this->db->where($search_field." !=", $search_value);
break;
case 'lt': //'less'
$this->db->where($search_field." <", $search_value);
break;
case 'le': //'less or equal'
$this->db->where($search_field." <=", $search_value);
break;
case 'gt': //'greater'
$this->db->where($search_field." >", $search_value);
break;
case 'ge': //'greater or equal'
$this->db->where($search_field." >=", $search_value);
break;
case 'bw': //'begins with'
$this->db->like($search_field, $search_value, 'after');
break;
case 'bn': //' does not begin with'
$this->db->not_like($search_field, $search_value, 'after');
break;
case 'in': //'is in' IMPORTANT: $search_string must be an array
$this->db->where_in($search_field, $search_value);
break;
case' ni': //'is not in' IMPORTANT: $search_string must be an array
$this->db->where_not_in($search_field, $search_value);
break;
case 'ew': //'ends with'
$this->db->like($search_field, $search_value, 'before');
break;
case 'en': //'does not end with'
$this->db->not_like($search_field, $search_value, 'before');
break;
}
}
The above method might be called multiple times depending on the number of fields the user selected to search. QB makes this kind of problem pretty easy to solve.
