4

I've though a bit about the activerecord vs. manual queries in Codeigniter. ActiveRecord is awesome when it's all about standard queries and holds development time really low.

However, when there's a need to add some complexity to the queries, the ActiveRecord gets quite complicated to work with. Sub queries or complex joins gives atleast me a lot of headache.

Since the current "$this->db->query" -call immediately executes the set query, it can't be combined with normal activeRecord calls.

So, what can I do to combine the two methods?

Example of what I want to accomplish:

$this->db->select('title, content, date');
$this->db->from('mytable');
$this->db->manual('UNION'); // My own idea of db-call that appends UNION to the query
$this->db->select('title, content, date');
$this->db->from('mytable2');
$query = $this->db->get();

Thanks!

Industrial
  • 41,400
  • 69
  • 194
  • 289
  • 1
    show us un example of a complex query and maybe we can think about how we could do it using ActiveRecord. AFAIK you can't combine $this->db->query() + $this->db->get() if this is what you meant. – Bogdan Jun 06 '10 at 18:40
  • Hi, this is actually something that I have been thinking about for some time, so I am afraid that I don't have a specific example right now. As you say, I am aware of $this->db->query() + $this->db->get() cannot be combined :) – Industrial Jun 06 '10 at 18:43
  • I'm with Bogdan, an example query would be good. – Bella Jun 07 '10 at 10:04
  • Hi guys, updated my original post with an example of what I would like to do. It could be a great way of extending the activeRecord feature with more advanced stuff without having to write the whole query from the beginning... – Industrial Jun 09 '10 at 11:26

1 Answers1

4

maybe this link will help: active record subqueries

Update---

there were another discussion about Union with Codeigniter Active Record. And I agree with the answer there.

But with some subqueries we can combine active record with manual queries. Example:

// #1 SubQueries no.1 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable');
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();

$this->db->_reset_select();

// #2 SubQueries no.2 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable2');
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();

$this->db->_reset_select();

// #3 Union with Simple Manual Queries --------------------------

$this->db->query("select * from ($subQuery1 UNION $subQuery2) as unionTable");

// #3 (alternative) Union with another Active Record ------------

$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();

nb: sorry I haven't tested this script, hope it's works and helpfull..

Community
  • 1
  • 1
bakazero
  • 130
  • 1
  • 9
  • Hi Bakazero. Thanks for your link. Very appreciated. Please check out my original post. Updated it with an example of what I would like to do. – Industrial Jun 09 '10 at 11:27
  • Hi! Like your update a lot. Not as clean as I would like it to be though. Upvote! :) – Industrial Jun 11 '10 at 19:50
  • This does not work (or no longer works). _compile_select and _reset_select are protected methods. – mpemburn Jul 29 '13 at 16:05