codeigniter change complex query into active record

Question

I have a codeigniter app.

My active record syntax works perfectly and is:

  function get_as_09($q){
    $this->db->select('m3');
    $this->db->where('ProductCode', $q);
    $query = $this->db->get('ProductList');
    if($query->num_rows > 0){
        foreach ($query->result_array() as $row){
            $row_set[] = htmlentities(stripslashes($row['m3'])); //build an array
       }
       return $row_set;
    }
  }

This is effectively

select 'm3' from 'ProductList' where ProductCode='$1'

What I need to do is convert the below query into an active record type query and return it to the controller as per above active record syntax:

select length from
(SELECT  
      [Length]
      ,CONCAT(([width]*1000),([thickness]*1000),REPLACE([ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options
  FROM [dbo].[dbo].[ProductList]) as p
  where options='25100cr' order by length

I picture something like below but this does not work.

$this->db->select(length);
$this->db->from(SELECT  [Length],CONCAT(([width]*1000),([thickness]*1000),REPLACE[ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options
      FROM [dbo].[dbo].[ProductList]);
$this->db->where(options, $q);
$this->db->order(length, desc);

Help appreciated as always. Thanks again.

Why don't you just execute your sql query ? $this->db->query($sql) — Brewal
@Brewal, not open to injection or any other security vulnerabilities? — Smudger

Muhammad Raheel Muhammad Raheel · Accepted Answer · 2013-04-18T13:33:31

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

$select =   array(
                'Length'
                'CONCAT(([width]*1000)',
                'thickness * 1000',
                'REPLACE(ProductCode, concat((width*1000),(thickness*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options'
);
$this->db->select($select);
$this->db->from('ProductList');

$Subquery = $this->db->_compile_select();

$this->db->_reset_select();

$this->db->select('length');
$this->db->from("($Subquery)");
$this->db->where('options','25100cr');
$this->db->order_by('length');

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.

Source

codeigniter change complex query into active record

1 Answers