How to Rewrite This SQL into Codeigniter's Active Records

How can I rewrite this SQL into CodeIgniter's Active Records?

Codeigniter currently does not have support for subqueries in its Active Records class.

You'll simply have to use this:

$this->db->query($your_query, FALSE);

Remember to pass that second argument, so that Codeigniter doesn't try to escape your query.

Rewrite MySQL query using Codeigniter Active record

After many researches, I ended up writing my own active record script.

$this->db->select(array('DATEDIFF(reply.timestamp, question.timestamp)'))
->from('question')
->join('reply', 'question.id = reply.question_id')
->where('question.company_id', $company_id);

Thanks

active record in codeigniter in the case of assignment by value

Using active record you can rewrite your update query as below,Also you can use single update instead of running 2 update queries

public function foo_bar_function($id, $date, $amount) {
$this->db->set('date', $date);
$this->db->set('amount', $amount);
$this->db->set('diff', 'total -'.$amount, FALSE);
$this->db->where('id', $id);
$this->db->update('table_foo');
}

See set() method of active record

Using Mysql WHERE IN clause in codeigniter

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this

Go to system/database/DB_active_rec.php
Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)
public function _reset_select()

Now subquery writing in available
And now here is your query with active record

$this->db->select('trans_id');
$this->db->from('myTable');
$this->db->where('code','B');
$subQuery = $this->db->_compile_select();

$this->db->_reset_select();
// And now your main query
$this->db->select("*");
$this->db->where_in("$subQuery");
$this->db->where('code !=', 'B');
$this->db->get('myTable');

And the thing is done. Cheers!!!

Note : While using sub queries you must use

$this->db->from('myTable')

instead of

$this->db->get('myTable')

which runs the query.

Watch this too

How can I rewrite this SQL into CodeIgniter's Active Records?

Note : In Codeigntier 3 these functions are already public so you do not need to hack them.

Codeigniter equivalent of this mysql query

Try the following code

$this->db->select('*');
$this->db->where('(a_id = "xx" or b_id = "xx")';
$this->db->where('(a_id = "zz" or b_id = "zz")';
$this->db->where('active', '1');


Related Topics



Leave a reply



Submit