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()
    {
        $id = $this->uri->segment(3);
        $this->db->select('*');
        $this->db->from('newsletter_article');
        $this->db->where('newsletter_article.newsletter_id', $id);
        $this->db->join('newsletter_article_photo', 'newsletter_article_photo.article_id = newsletter_article.id');
        $data['newsletter_article']= $this->db->get();
           $this->load->vars($data);
            $this->load->view('template/template');
    }

[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')
         ->from('newsletter')
         ->join('newsletter_article','newsletter_article.newsletter_id=newsletter.id')
         ->get();

you'll only have one result row for one newsletter, even if the newsletter got more than one articles. So you won't have all the articles off all the newsletters.

And if you do like :

Code:

$this->db->select('newsletter.id as newsletter_id, newsletter_article.id as article id')
->from('newsletter_article')
->join('newsletter','newsletter_article.newsletter_id=newsletter.id','left')
->get();

You won't get back the newsletters that exists but have no articles. Or if you do on articles/photos you won't get back articles that have no photo associated...

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()
{
    $this->load->model('test_model');
    $model_data = $this->test_model->newsletter_info();
    foreach($model_data as $val2):
       echo $val2['author'].$val2['images'].'<br>';
    endforeach;
}

and here is test_model.php

Code:

function newsletter_info()
{
    $query = $this->db->select('newsletter.id as newsletter_id, newsletter_article.id as

article_id, newsletter_article.author, newsletter_article_photo.images')
         ->from('newsletter')
         ->join('newsletter_article', 'newsletter_article.newsletter_id=newsletter.id')
         ->join('newsletter_article_photo',

'newsletter_article_photo.article_id=newsletter_article.id')
         ->get();        
    if ($query->num_rows() > 0)
    {
        $rows = $query->result_array();
        return $rows;
    }
    return false;
}

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')
->join('newsletter_article_photo','newsletter_article_photo.article_id=newsletter_article.id', 'left')

then it return all newsletters whatever it have or not any article or images.

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')
->join('newsletter_article_photo','newsletter_article_photo.article_id=newsletter_article.id', 'left')

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');
$this->db->where('x.newsletter_id', $id);
$query = $this->db->get('newsletter_article x');

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');
$this->db->join('newsletter_article_photo z', 'y.id = z.article_id', 'left');
$this->db->where('x.id', $id);
$query = $this->db->get('newsletter x');

[eluser]Sumon[/eluser]
[quote author="Armchair Samurai" date="1223458933"]

Code:

$this->db->join('newsletter_article_photo y', 'x.id = y.article_id', 'left');
$this->db->where('x.newsletter_id', $id);
$query = $this->db->get('newsletter_article x');

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()
    {
        $this->db->join('choy_user_details', 'choy_user.choy_user_id = choy_user_details.choy_user_det_id');
        $this->db->join('choy_user_type', 'choy_user.choy_user_type_id = choy_user_type.choy_user_type_id', 'left');
        $this->db->where('choy_user_id', $userId);
        $query = $this->db->get('choy_user');    
        return $query->result();
    }

and i have this error:

[color=red]A PHP Error was encountered

Severity: Notice

Message: Undefined variable: userId

Filename: models/user_model.php

Line Number: 45

[quote]solved