CODEWITHSUNDEEP
phpmysqlactiverecordcodeigniter-3query-builder
Bhavya Sanchaniya
Bhavya Sanchaniya

Reputation: 553

Convert raw SQL containing a subquery in the SELECT clause, an implicit table JOIN, and FIND_IN_SET() to CodeIgniter active record scripting

I have this query to convert into the Active record

SELECT (
        SELECT image
        FROM bc_item_image
        WHERE item_id = i.id
        LIMIT 1
       ) as item_image,
       i.description,
       i.condition,
       c.cat_name as category_name,
       i.id as ID
FROM bc_item i,bc_category c
WHERE i.cat_id = c.id
    and i.user_id !='$user_id'
    and i.status = '1'
    and i.is_bartered = '0'
    and i.is_cancel = '0'
    and FIND_IN_SET('$subcat_id',i.interested_cat)
order by i.display_count desc

How can this be scripted using query builder methods in CodeIgniter?

Upvotes: 1

Views: 76

Answers (3)

mickmackusa
mickmackusa

Reputation: 47864

First, the subquery in your SELECT clause is not advisable for performance reasons; instead JOIN or LEFT JOIN the subquery to relate the required data.

Second, implicit / old skool comma-JOINs are commonly frowned upon because they can be overlooked by human developers. The SQL intention is more obvious when JOIN and ON/USING syntax is used.

Third, I won't change it, but FIND_IN_SET() generally indicates that you should restructure your database to avoid delimited values in a single column.

$subquery = $this->db
    ->select('item_id, MIN(image) image')
    ->group_by('item_id')
    ->get_compiled_select('bc_item_image');

return $this->db
    ->select([
        'img.image item_image',
        'i.description',
        'i.condition',
        'c.cat_name category_name',
        'i.id id'
    ])
    ->join('bc_category c', 'i.cat_id = c.id')
    ->join("($subquery) img", 'i.id = img.item_id', 'LEFT')
    ->where([
        'i.user_id !=' => $user_id,
        'i.status' => 1,
        'i.is_bartered' => 0,
        'i.is_cancel' => 0,
        "FIND_IN_SET($subcat_id, i.interested_cat)" => null
    ])
    ->order_by('i.display_count', 'DESC')
    ->get('bc_item i')
    ->result();

The value used in the first parameter of FIND_IN_SET() will not be quoted. If this is needed, then wrap that variable in escape() like $this->db->escape($subcat_id)

If $user_id = 4; and $subcat_id = 11;, then the rendered SQL* will resemble the following.

SELECT `img`.`image` `item_image`,
       `i`.`description`,
       `i`.`condition`,
       `c`.`cat_name`
       `category_name`,
       `i`.`id` `id`
FROM `bc_item` `i`
JOIN `bc_category` `c` ON `i`.`cat_id` = `c`.`id`
LEFT JOIN (
    SELECT `item_id`, MIN(image) image
    FROM `bc_item_image`
    GROUP BY `item_id`
) img ON `i`.`id` = `img`.`item_id`
WHERE `i`.`user_id` != 4
    AND `i`.`status` = '1'
    AND `i`.`is_bartered` = '0'
    AND `i`.`is_cancel` = '0'
    AND FIND_IN_SET(11, i.interested_cat)
ORDER BY `i`.`display_count` DESC

* I added my own custom spacing for readability and the identifier/string quoting may vary based on db dialect/driver.

Upvotes: 0

user9934277
user9934277

Reputation:

use 

$this->db->query('here your SQL query');

Upvotes: 1

Rp9
Rp9

Reputation: 1963

$this->db->select(" i.description, i.condition,c.cat_name as category_name,i.id,(SELECT image FROM bc_item_image WHERE item_id = i.id LIMIT 1) as item_image");
$this->db->from('bc_item as i');
$this->db->join('bc_category as c', 'i.cat_id = c.id');
      $this->db->where('i.status', 1);
      $this->db->where('i.is_bartered', 0);
      $this->db->where('i.user_id','!=', $user_id);
      $this->db->where('i.is_cancel', 0);
      $this->db->where("FIND_IN_SET('".$subcat_id."','i.interested_cat')");
      $query = $this->db->get();

Upvotes: 0

Related Questions