• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Best Practice

#4
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.
Reply


Messages In This Thread
Best Practice - by emilio - 10-09-2018, 07:19 PM
RE: Best Practice - by Pertti - 10-09-2018, 11:42 PM
RE: Best Practice - by php_rocs - 10-10-2018, 07:29 AM
RE: Best Practice - by dave friend - 10-10-2018, 08:04 AM
RE: Best Practice - by emilio - 10-12-2018, 10:24 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.