CODEWITHSUNDEEP
phpcodeigniterjoinactiverecordquery-builder
Isamtron
Isamtron

Reputation: 623

Convert raw SQL with comma-JOINed tables to use CodeIgniter's query builder methods

How to write this SQL statement in Active Record?

$this->db->query('
    SELECT a.slogan, a.brief, u.id, u.name
    FROM example_tbl AS a, users AS u
    WHERE u.user_id = "' . USER_ID . '"
');

Upvotes: 0

Views: 410

Answers (2)

mickmackusa
mickmackusa

Reputation: 47982

More modern practices advise against using implicit JOINs. The following will create a JOIN (INNER JOIN) clause which associates the two table on their shared user_id column values.

The FROM and WHERE clause are built into the get_where() call which also executes the query.

result() will return an array of zero or more objects with the four columns as keys.

return $this->db
    ->select('a.slogan, a.brief, u.id, u.name')
    ->join('users u', 'u.user_id = a.user_id')
    ->get_where('example_tbl a', ['u.user_id' => USER_ID])
    ->result();

Upvotes: 0

lia ant
lia ant

Reputation: 408

$this->db->select('a.slogan, a.brief, u.id, u.name')->where('u.user_id', USER_ID)->get('example_tbl AS a, users AS u')->result();

If your selected table fields are not ambiguous you can drop shortcutting like 'AS a, u'...

Upvotes: 1

Related Questions