+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Active Record and JOIN (/showthread.php?tid=12750)
Pages:
1
2
Active Record and JOIN - El Forum - 10-30-2008
[eluser]Firstrow[/eluser]
I have question about AC left join.
I'll try to explain.
For example I have one table in db named 'acticles' and other table 'comment' which have all comments to some page.
Task: select some page and all comment for this page in one request to database. Now my code is:
Code:
$this->db->from('articles');
$this->db->join('comments', 'comments.acticle_id = articles.id','left');
$this->db->where('articles.id',$some_id);
$query = $this->db->get();
$rezult = $query->result_array();But, after this request I've received one page as many times as there are comments.
Now question, How i can receive array like that:
Code:
array(
'id' => '1',
'title' => 'Page Tile',
'comments' => array(
'coment_id' => 1,
'coment_text' => 'Text goes here'
),
array(
'coment_id' => 2,
'coment_text' => 'Text2 goes here',
)
);or how is right to use CI Active Record with Left Join?
any suggestions?
Active Record and JOIN - El Forum - 10-30-2008
[eluser]manilodisan[/eluser]
This is normal. Mysql can't return distinct articles since you're fetching the comments. It's up to you to take what mysql outputs and build the array you wish by looping trough the results.
If you have only 1 article and 100 comments for it, mysql will output 100 times that article for each comment in part.
Active Record and JOIN - El Forum - 10-30-2008
[eluser]Firstrow[/eluser]
[quote author="manilodisan" date="1225384018"]This is normal. Mysql can't return distinct articles since you're fetching the comments. It's up to you to take what mysql outputs and build the array you wish by looping trough the results.[/quote]
this is bad =(
how it loads memmory?
Active Record and JOIN - El Forum - 10-30-2008
[eluser]manilodisan[/eluser]
It's not bad. It's pretty easy to build that array based on parents (article_id).
Active Record and JOIN - El Forum - 10-30-2008
[eluser]OES[/eluser]
If you want to list 1 article with all its comment try it this way.
Code:
$this->db->select('comments.*, articles.content, articles.whatever', FALSE);
$this->db->from('comments');
$this->db->join('articles', 'articles.id = comments.acticle_id','left');
$this->db->where('comments.acticle_id',$some_id);
$query = $this->db->get();
$rezult = $query->result_array();This then would return all comments re a specefic article.
You just need to make sure you get all the article info in the select.
Active Record and JOIN - El Forum - 10-30-2008
[eluser]manilodisan[/eluser]
This would still repeat the article details for each comment in part.
Active Record and JOIN - El Forum - 10-30-2008
[eluser]Firstrow[/eluser]
[quote author="manilodisan" date="1225385287"]This would still repeat the article details for each comment in part.[/quote]
yes, its true;
Active Record and JOIN - El Forum - 10-30-2008
[eluser]OES[/eluser]
Yep sorry was not thinking straight there.
why dont you then just perform 2 queries to post to the page.
One for the Article and other for comments.
good Luck
Active Record and JOIN - El Forum - 10-30-2008
[eluser]Firstrow[/eluser]
[quote author="OES" date="1225385832"]Yep sorry was not thinking straight there.
why dont you then just perform 2 queries to post to the page.
One for the Article and other for comments.
good Luck[/quote]
its more intresting to make it in one request =)
Active Record and JOIN - El Forum - 10-30-2008
[eluser]manilodisan[/eluser]
[quote author="OES" date="1225385832"]Yep sorry was not thinking straight there.
why dont you then just perform 2 queries to post to the page.
One for the Article and other for comments.
good Luck[/quote]
And when you have 1000 articles?
1000 = how many queries?
Code:
$articles = array ();
foreach ( $query->result () as $row )
{
$articles [ $row->article_id ] [ 'title' ] = $row->article_title;
$articles [ $row->article_id ] [ 'content' ] = $row->article_content;
$articles [ $row->article_id ] [ 'comments' ] [] = array ( 'coment_id' => $row->comment_id, 'coment_text' => $row->coment_text );
}
echo '<pre>' . print_r ( $articles, TRUE ) . '</pre>';