How to write this query in Codeigniter Active Record

How to write this query in Codeigniter Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to write this query in Codeigniter Active Record (/showthread.php?tid=66027)

How to write this query in Codeigniter Active Record - mubbi.qureshi - 08-25-2016

how to write this query in active record format?

SELECT COUNT(TotalBook) AS BooksRow FROM ( SELECT COUNT( BookID ) TotalBook FROM books GROUP BY Title ) src

i need help Sad

RE: How to write this query in Codeigniter Active Record - kilishan - 08-25-2016

Honestly, for more complex SQL like this one, I don't worry about the Query Builder unless I really need to for some reason. Something like this works just fine:

Code:
$this->db->query('SELECT COUNT(TotalBook) AS BooksRow FROM ( SELECT COUNT( BookID ) TotalBook FROM books GROUP BY Title ) src');

This one doesn't need it, but if you have user-supplied input you should definitely use Query Bindings.

However, I believe you can do the following (completely untested):

Code:
$this->db->select('COUNT(TotalBook) AS BooksRow')

    ->get('( SELECT COUNT( BookID ) TotalBook FROM books GROUP BY Title ) src')

;

RE: How to write this query in Codeigniter Active Record - mubbi.qureshi - 08-25-2016

(08-25-2016, 10:41 AM)Thanks for your reply :) and yes i am adding user bindings in it that\s why for security reasons i wanted the active record. i will use "where" and "like" clauses in the sub query can you please also add 1 example of where and like clause in this active record sub query part .kilishan Wrote: Honestly, for more complex SQL like this one, I don't worry about the Query Builder unless I really need to for some reason. Something like this works just fine:

Code:
$this->db->query('SELECT COUNT(TotalBook) AS BooksRow FROM ( SELECT COUNT( BookID ) TotalBook FROM books GROUP BY Title ) src');

This one doesn't need it, but if you have user-supplied input you should definitely use Query Bindings.

However, I believe you can do the following (completely untested):

Code:
$this->db->select('COUNT(TotalBook) AS BooksRow') ->get('( SELECT COUNT( BookID ) TotalBook FROM books GROUP BY Title ) src') ;