Active Record model design for complex queries

Active Record model design for complex queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active Record model design for complex queries (/showthread.php?tid=47613)

Active Record model design for complex queries - El Forum - 12-15-2011

[eluser]Unknown[/eluser]
We all know Active Record is awesome for single table db operations.

What i'm trying to figure out is how to design the model when queries start becoming more complex.

For example, I have a model called Appointment_model. My fetch_all() method originally looked like this:

Code:
function fetch_all()

{

    this->db->select('*')->from('appointments');

    return $this->db->get();

}

Next thing I know, I wanted to join the patients table, therefore I had:

Code:
function fetch_all()

{

    $this->db->select('a.*, p.firstname, p.surname')

    ->from('appointments a')

    ->join('patients p', 'a.patient_id = p.id');

    return $this->db->get();

}

And finally, I wanted some conditional where parameters, therefore:

Code:
function fetch_all($where=array())

{

    $this->db->select('a.*, p.firstname, p.surname')

    ->from('appointments a')

    ->join('patients p', 'a.patient_id = p.id');

    if (!empty($where)) $this->db->where($where); 

    return $this->db->get();

}

As you can see this is rapidly getting complicated, and my application is going to require more joins and order by options down the track.

I wonder whether I am going in the right direction.

It seems OK to just pass a $where parameter, but then what if I want to do some additional joins, order by etc.

Must I have:

Code:
function fetch_all($joins, $where, $order_by) {

?
This feels kind of messy as well.

What is the best practice to manipulate queries without overly complicating things?