CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
Port 8080
Port 8080

Reputation: 888

Select all columns from a CodeIgniter active record query with two joins

I want to convert a raw SQL to CodeIgniter's active record. How can I convert this?

SELECT c.*,
       p.*,
       cp.*
FROM css c,
     php p,
     cplusplus cp
WHERE cp.id = p.cp_id
  AND p.id = c.p_id

Upvotes: 0

Views: 116

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

  • Those old skool comma-joined tables should be written as INNER JOINs (the default JOIN).
  • select(*) (select all columns) is the default behavior in CodeIgniter; so that method can be safely omitted.
  • from('php') can be removed by writing the first table name in the get() method call. Either way, get() still needs to be called to execute the query. The order of calling clause building methods does not negatively affect the built query.
  • result() will return an array of zero or more objects.

Model method structure:

public function getPhpCssCPlusCplus(): array
{
    return $this->db
        ->join('css', 'php.id = css.pid')
        ->join('cplusplus', 'php.cp_id = cplusplus.cp_id')
        ->get('php')
        ->result();
}

Upvotes: 0

mrsrinivas
mrsrinivas

Reputation: 35404

It is very is to write CI Active records. You can start learning here.

First configure the DB details then load

$this->load->database();

This is active record way of your query. Not exactly the same but response will same(optimized way)

   $this->db->select("*")
              ->from("php")
              ->join("css", "css.pid = php.id")
              ->join("cplusplus", "cplusplus.cp_id = php.cp_id")
              ->get()->result_array(); //Returns multiple records

You can use ____->get()->row_array(); for single record.

** Response will be in array format.

Upvotes: 2

Related Questions