[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
[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-gui...eries.html (bottom of the page).
[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
[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-gui...eries.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;
}
}
[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.
[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.
[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'
);
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.[/quote]Explain please thanks.
[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.
|
