How can I write this query with Active Records?

[eluser]Pert[/eluser]
[quote author="behnampmdg3" date="1369826418"]Explain please thanks.[/quote]

Sure, as a quite basic example, here goes.

First thing I do is get labels for statuses or other items that might be used a lot in the system but all they really do is just give text label to an ID.

Code:

$tmp = $this->db
   ->select('id, name')
   ->get('status');
$statuses = array();
$statuses[0] = 'Unknown'; // default in case of DB corruption, etc

if ($tmp->num_rows)
{
   foreach($tmp->result() as $row)
   {
      $statuses[$row->id] = $row->name;
   }
}

In your example, you might create additional arrays for classes, etc.

Then if I have to get products, I will handle one table in my query:

Code:

$products = $this->db
   ->select('id, name, status)
   ... where ...
   ->get('products')
   ->result();

if (!empty($products))
{
   foreach($products as $row)
   {
      $row->status_text = isset($statuses[$row->status]) ? $statuses[$row->status] : $statuses[0];
      $row->class_text = isset($classes[$row->class]) ? $classes[$row->class] : $classes[0];
   }
}

While you do have to make 2 small quick additional queries in the beginning, you don't have to do any joins for products.