How can I write this query with Active Records?

How can I write this query with Active Records? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How can I write this query with Active Records? (/showthread.php?tid=58209)

How can I write this query with Active Records? - El Forum - 05-23-2013

[eluser]behnampmdg3[/eluser]
Hi;

I use this code to send dynamic data to model and run queries:

Code:
public function results_products($data, $record_start=0,$number_of_records=50)

  {

   $this->db->select('products_table.id, code, class, category, status, price, production_date, products_status.title AS STATUS');

   $this->db->from('products_table USE INDEX (PRIMARY)');

   $this->db->join('products_status', 'products_status.id = products_table.status');

   $this->db->order_by('products_table.id');

   $this->db->where('products_table.id >', $record_start);

   $this->db->where($data);

   $this->db->limit($number_of_records);

                 }

Now I need to change my code to a nother query which I have a hard time writing with active records! I still want to send dynamic $data so the part in WHERE is different each time. How can I do that with active records? If I cant, how can I send dynamic $data to this query so the WHERE part is dynamic? Thanks
For example:

Code:
SELECT q2.id,

       q2.code,

       q2.class,

       q2.category,

       q4.title

FROM   (SELECT id,

               `code`,

               `class`,

               `category`,

               `status`,

               `price`,

               `production_date`

        FROM   products_table AS q1

        WHERE  q1.id > 0

               AND q1.class = 3

        ORDER  BY q1.id

        LIMIT  50) AS q2

       INNER JOIN products_table AS q3

               ON q3.id = q2.id

       JOIN `products_status` AS q4

         ON q4.id = `q3`.`status`

Thanks

How can I write this query with Active Records? - El Forum - 05-24-2013

[eluser]TheFuzzy0ne[/eluser]
Doing that with the Active Record class will probably be impossible, since it's geared more towards simpler queries.

When you say you want to change the WHERE condition, do you simply mean the value, or the entire WHERE condition. In either case, query bindings will probably help: http://ellislab.com/codeigniter/user-guide/database/queries.html (bottom of the page).

How can I write this query with Active Records? - El Forum - 05-24-2013

[eluser]Pert[/eluser]
Wouldn't simple query like this be enough?

Code:
SELECT * FROM products_table, products_status

WHERE

products_table.class = 3 AND

products_table.status = products_status.id

ORDER BY products_table.id

LIMIT 50

How can I write this query with Active Records? - El Forum - 05-24-2013

[eluser]behnampmdg3[/eluser]
[quote author="TheFuzzy0ne" date="1369386788"]When you say you want to change the WHERE condition, do you simply mean the value, or the entire WHERE condition. In either case, query bindings will probably help: http://ellislab.com/codeigniter/user-guide/database/queries.html (bottom of the page).[/quote]The whole WHERE condition is dynamic. This means I will have to put ligic in the model. ot have multiple queries. Dont really like either. This is where I build the $data.

Code:
public function validate_search()

 {

  $CI =& get_instance();

  $CI->price_type=$CI->input->post('price_type');

  $CI->load->library('form_validation');

  $this->class = $CI->input->post('class');

  if($this->class!='0')

   {

    $data['class'] = $CI->input->post('class') ? $CI->input->post('class') : '';

    $CI->form_validation->set_rules('class', '<strong class="yellow">Product Class</strong>', 'exact_length[1]|numeric|xss_clean|valid_product_class');

   }

  $this->category = $CI->input->post('category');

  if($this->category!='0')

   {

    $data['category'] = $CI->input->post('category') ? $CI->input->post('category') : '';

    $CI->form_validation->set_rules('category', '<strong class="yellow">Product Category</strong>', 'exact_length[1]|required|alpha|xss_clean');

   }

  #Price 

  $price = $CI->input->post('price_type');

  if($price=='exact_price')

   {

    $data['price'] = $CI->input->post('price') ? $CI->input->post('price') : '';

    $data['price_type'] = 'exact_price';

    $data['price_to'] = NULL;

    $data['price_from'] = NULL;

    $CI->form_validation->set_rules('price', '<strong class="yellow">Product Price</strong>', 'required|numeric|xss_clean|numeric|greater_than[0]');

   }

  if($price=='between_price')

   {

    $data['price_from'] = $CI->input->post('price_from') ? $CI->input->post('price_from') : '';

    $data['price_to'] = $CI->input->post('price_to') ? $CI->input->post('price_to') : '';

    $data['price_type'] = 'between_price';    

    $data['price'] = NULL;

    $CI->form_validation->set_rules('price_from', 'Product <strong class="yellow">Price from</strong>', 'required|numeric|xss_clean|numeric|greater_than[0]');

    $CI->form_validation->set_rules('price_to', 'Product <strong class="yellow">Price to</strong>', 'required|numeric|xss_clean|numeric|greater_than[0]');

   } 

  if($price!='exact_price' && $price!='between_price')

   {

    $data['price'] = NULL; 

    $data['price_from'] = NULL;

    $data['price_to'] = NULL;    

   }

  # END price

  if(strlen($CI->input->post('production_date'))>1)

   {

    $CI->form_validation->set_rules('production_date', 'Product <strong class="yellow">date</strong>', 'valid_production_date|xss_clean');

    $data['production_date'] = $CI->input->post('production_date') ? $CI->input->post('production_date') : '';

   }

  else

   {

    $data['production_date']=NULL;

   }

  $this->status = $CI->input->post('status');

  if($this->status!='0')

   {

    $data['status'] = $CI->input->post('status') ? $CI->input->post('status') : '';

    $CI->form_validation->set_rules('status', '<strong class="yellow">Product Status</strong>', 'alpha|xss_clean');

   } 

  $CI->form_validation->set_rules('status', 'Product <strong class="yellow">status</strong>', 'exact_length[1]|required|numeric|xss_clean');

  if($CI->form_validation->run())

   {

    $data['success']=TRUE;

    return $data; 

   } 

  else

   {

    $data['success']=FALSE;

    return $data; 

   }

  }

How can I write this query with Active Records? - El Forum - 05-24-2013

[eluser]behnampmdg3[/eluser]
[quote author="pert" date="1369398104"]Wouldn't simple query like this be enough?

Code:
SELECT * FROM products_table, products_status

WHERE

products_table.class = 3 AND

products_table.status = products_status.id

ORDER BY products_table.id

LIMIT 50

[/quote]Long story short:
For smaller tables yes. Once you start dealing with larger data, then no.

How can I write this query with Active Records? - El Forum - 05-29-2013

[eluser]Pert[/eluser]
[quote author="behnampmdg3" date="1369431473"]Long story short:
For smaller tables yes. Once you start dealing with larger data, then no.[/quote]

I've started to pre-fetch labels that get used a lot all over the place.

Like get all the statuses and assign them into PHP array using ID as array index:

Code:
$array = array(

'1' => 'Status #1',

'7' => 'Status #7',

'4' => 'Status #4'

);

Then whenever I need to use the text label I can get it with simple <b>$status[$product->status]</b> and you don't need to join tables in your queries. I know joining up few big tables can be hit the memory limit quite easily.

How can I write this query with Active Records? - El Forum - 05-29-2013

[eluser]behnampmdg3[/eluser]
[quote author="pert" date="1369815168"][quote author="behnampmdg3" date="1369431473"]Long story short:
For smaller tables yes. Once you start dealing with larger data, then no.[/quote]

I've started to pre-fetch labels that get used a lot all over the place.

Like get all the statuses and assign them into PHP array using ID as array index:

Code:
$array = array(

'1' => 'Status #1',

'7' => 'Status #7',

'4' => 'Status #4'

);

How can I write this query with Active Records? - El Forum - 05-29-2013

[eluser]Pert[/eluser]
[quote author="behnampmdg3" date="1369826418"]Explain please thanks.[/quote]

Sure, as a quite basic example, here goes.

First thing I do is get labels for statuses or other items that might be used a lot in the system but all they really do is just give text label to an ID.

Code:
$tmp = $this->db

   ->select('id, name')

   ->get('status');

$statuses = array();

$statuses[0] = 'Unknown'; // default in case of DB corruption, etc

if ($tmp->num_rows)

{

   foreach($tmp->result() as $row)

   {

      $statuses[$row->id] = $row->name;

   }

}

In your example, you might create additional arrays for classes, etc.

Then if I have to get products, I will handle one table in my query:

Code:
$products = $this->db

   ->select('id, name, status)

   ... where ...

   ->get('products')

   ->result();

if (!empty($products))

{

   foreach($products as $row)

   {

      $row->status_text = isset($statuses[$row->status]) ? $statuses[$row->status] : $statuses[0];

      $row->class_text = isset($classes[$row->class]) ? $classes[$row->class] : $classes[0];

   }

}

While you do have to make 2 small quick additional queries in the beginning, you don't have to do any joins for products.