[kbs170]

Hi,

is there a function in Codeigniter to use "JOIN .. USING .." like $this->db->join_using()?

I use Codeigniter 3.0.4 with mariaDB and need the following statement:

Code:

JOIN (SELECT ... FROM ... WHERE) USING (...)

instead of the "normal" JOIN .. ON ... , which results from

Code:

$this->db->join('mytable as my','t1.id = t2.id')

I follow the post of newtover Feb 12 '13 at 15:54 ("What I would suggest") from
https://stackoverflow.com/questions/1477...e-group-by

Code:

SELECT *
FROM wp_posts
INNER JOIN
(
 SELECT max(post_date) post_date, post_author
 FROM wp_posts
 WHERE post_status='publish' AND post_type='post'
 GROUP BY post_author
 ORDER BY post_date DESC
 -- LIMIT GOES HERE
) p2 USING (post_author, post_date)
WHERE post_status='publish' AND post_type='post';

[neuron]

Why do you need USING clause, you can use ON clause intead:

PHP Code:

$this->db->select('max(post_date) as post_date, post_author')
        ->from("wp_posts")
        ->where('post_status', 'publish')
        ->where('post_type', 'post')
        ->group_by('post_author')
        ->order_by('post_date');
$sql = $this->db->get_compiled_select();

$this->db->select('wp.*, t.*')
->from('wp_posts as wp')
->join("($sql) as t", 't.post_author = wp.post_author and t.post_date= wp.post_date') 
->where('wp.post_status', 'publish')
->where('wp.post_type', 'post');

$result = $this-db->get(); 


As I know there is no USING clause in CI Query builder. 
But if you want it you can extend Query_builder and implement your own join method

[kbs170]

Thank you very much! That works great!

Best,
kbs170