• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MariaDB / mysql: JOIN (SELECT ... FROM ... WHERE) USING (...)

#1
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';
Reply

#2
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
Reply

#3
Thank you very much! That works great!

Best,
kbs170
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.