Active Record 3 table join |
[eluser]mdriscol[/eluser]
Hey guys, I've searched the forum and I still don't understand how I'm supposed to go about joining data from 3 tables. Can someone help me out? I'm creating an online newsletter with articles and photos. I have 3 tables to accomplish this. Tables: newsletter -id -issue -volume -date newsletter_article -id -newsletter_id -title -author -article_text newsletter_article_photo -id -article_id -bunch of upload data for image linking So, the newsletter contains articles and the articles contain text as well as photos. How can I go about joining the three? I know how I can join the article and photos but then joining them with the newsletter gets confusing. Can someone help me out here. Thanks! Here is the controller I have so far. But it doesn't work right. Code: function newsletter_view()
[eluser]Dready[/eluser]
Hello, The problem is that you join something that exists (newsletter) with something that can be empty (newsletter_article). If you do it like : Code: $this->db->select('newsletter.id as newsletter_id, newsletter_article.id as article_id') And if you do like : Code: $this->db->select('newsletter.id as newsletter_id, newsletter_article.id as article id') Perhaps you should look at an CodeIgniter ORM, check the forums, there are a few, some call it datamapper, others ignited records...
[eluser]mdriscol[/eluser]
Thanks for the fast reply Dready. Much appreciated. I'll check out a ORM and see if I can manage to do it. I was going to have my controller add a spaceholder entry to each table. For example, when I add an article it would insert a placeholder row in the photos table so that the article would show up. This may be the wrong way to go about it though.
[eluser]Sumon[/eluser]
I don't think you need to add any placeholder row in photos table. Hang around few seconds please, within few minutes i am going to update this post to best suited for you. Edit: [Sorry to say i can't sort out the alternative ![]()
[eluser]Sumon[/eluser]
May be it's a solution for you I have created three tables as mentioned earlier. And a bunch of codes to test what's happening with 'left' join and simple join. // here is my controller Code: function record_sets() Code: function newsletter_info() If we use no join then it return only those records which have newsletter_article and newsletter_article_photo entry. And if we use left join as Code: ->join('newsletter_article','newsletter_article.newsletter_id=newsletter.id', 'left') If we want to get all newsletters which have newsletter_article but might not images then use: Code: ->join('newsletter_article','newsletter_article.newsletter_id=newsletter.id') helpful?
[eluser]Armchair Samurai[/eluser]
So, you just need to get all the articles for a particular newsletter? Is that correct? If so, it's a straight forward two table join. I've also made the assumption that each entry must have an article, but photos are optional. Code: $this->db->join('newsletter_article_photo y', 'x.id = y.article_id', 'left'); I would avoid joining the newsletter table as well because it would return redundant information - get that data in a separate query. That being said, if you really want to join all three: Code: $this->db->join('newsletter_article y', 'x.id = y.newsletter_id');
[eluser]Sumon[/eluser]
[quote author="Armchair Samurai" date="1223458933"] Code: $this->db->join('newsletter_article_photo y', 'x.id = y.article_id', 'left'); I would avoid joining the newsletter table as well because it would return redundant information - get that data in a separate query. [/quote] Yep i thought previously to make a good solution data redundancy will be an issue. There is a post related with this. http://ellislab.com/forums/viewthread/92890/P15/#470732
[eluser]chazy (aldever calvo)[/eluser]
hi.. about joining tables.. this is my function Code: function GetAllDetails() and i have this error: Quote:[color=red]A PHP Error was encountered |
Welcome Guest, Not a member yet? Register Sign In |