Using OUTER APPLY with Codeigniter active record

Question

Is it possible to use SQL Server's OUTER APPLY with Codeignter's Active Record?

Not sure about APPLY but you can do OUTER join.. like: $this->db->join('table', 'table.id = table2.id', 'outer'); — Ilanus, Jan 18 '16 at 12:55
Yeah, I've used join plenty of times, but in this case I need to use APPLY. Might need to get creative with this... — Tom, Jan 18 '16 at 12:57

mjosephabie · Answer 1 · 2021-02-03T10:05:39.683

Not possible. For using Cross Apply or Outer Apply, you need to add a specific function in the file system\database\DB_query_builder.php

that being said, you can add this function (modified from join one) to said file:

public function apply($table, $type = '', $escape = NULL){
    if ($type !== '')
    {
        $type = strtoupper(trim($type));

        if ( ! in_array($type, array('CROSS', 'OUTER'), TRUE))
        {
            $type = 'CROSS';
        }
    }
    else{
        $type = 'CROSS';
    }

    // Extract any aliases that might exist. We use this information
    // in the protect_identifiers to know whether to add a table prefix
    $this->_track_aliases($table);

    is_bool($escape) OR $escape = $this->_protect_identifiers;

    // Do we want to escape the table name?
    if ($escape === TRUE)
    {
        $table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
    }

    // Assemble the APPLY statement
    $this->qb_join[] = $join = $type.' APPLY '.$table;

    if ($this->qb_caching === TRUE)
    {
        $this->qb_cache_join[] = $join;
        $this->qb_cache_exists[] = 'join';
    }

    return $this;
}

And then, you can using it like

$this->db->apply('(select * from table_a where table_a.id=table_z.fk_id) as tmp_table');

or something more complete like

$this->db->apply('(select * from table_a where table_a.id=table_z.fk_id) as tmp_table','outer',false);

score 0 · Answer 2 · edited May 23 '17 at 12:14

You can do it by

$this->db->join('comments', 'comments.id = blogs.id', 'outer');

Or

$this->db->query("SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name=table2.column_name;");

Codeigniter Query Builder and OUTER APPLY example

Possible joins with Codeigniter
(associate with this $this->db->join('comments', 'comments.id = blogs.id', 'outer');)

left
right
outer
inner
left outer
right outer

Using OUTER APPLY with Codeigniter active record

2 Answers2