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
Executing SQL Server Agent Job from a Stored Procedure and Returning Job Result
Postgres Interval Using Value from Table
SQL Server Equivalent of Wm_Concat Function
SQL Same Unit Between Two Tables Needs Order Numbers in 1 Cell
With (Nolock) VS Set Transaction Isolation Level Read Uncommitted
Why Is There a Huge Performance Difference Between Temp Table and Subselect
Pass in "Where" Parameters to Postgresql View
SQL Server Using Wildcard Within In
SQL Datediff - Find Datediff Between Rows
SQL Server Updating a Time Stamp Column
Select Distinct Is Slower Than Expected on My Table in Postgresql
Limit Characters Returned in Oracle SQL Query
SQL in Query Produces Strange Result
Best Way to Store Working Hours and Query It Efficiently
Grant Execute Permission for a User on All Stored Procedures in Database
SQL Query for a Carriage Return in a String and Ultimately Removing Carriage Return