Multiple functions for several joins in a query - how to do it?

Multiple functions for several joins in a query - how to do it? - Printable Version

+- 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: Multiple functions for several joins in a query - how to do it? (/showthread.php?tid=29765)

Multiple functions for several joins in a query - how to do it? - El Forum - 04-20-2010

[eluser]titanite[/eluser]
Hello,

I'm a novice at this, so please bear with me.

I'm doing this query for a movie review (stored in a table called movie_review), and in the review, there is normally a director whose name I store in another table called persons.

Now, there could be a second director, whose name I would like to show.

In non-MVC formats, I would do first do a query to retrieve the the movie review and if Director 2 exists, I would do another query.

Code:
print "$row[movietitle]";

print "by director $row[director1]";

if ($row[director2]) {

$query = "select first_name, last_name from persons where id = '$row[director2]'"; etc etc etc  

print "and also by $first_name $last_name.";

}

In CI, this is my controller, which I name movie.php

Code:
&lt;?php

class Movie extends Controller {

    function Movie()

    {

        parent::Controller();

        $this->load->helper('url');

        $this->load->helper('form');

    }

    function index()

    {

        $data['title'] = "All Movies";

        $data['heading'] = "All Movies";

        $this->db->order_by("date", "desc"); 

        $data['query'] = $this->db->get('movie_review');

        $this->load->view('movie_listing_all', $data);

    }

    function movie_review()

    {

        $this->load->model('Movie_model');

        $id = $this->uri->Segment(3);

        $data['info'] = $this->Movie_model->get_review($id);

        $this->load->view('movie_view', $data);

    }

}

?&gt;

My model is called movie_model.php

Code:
&lt;?php

class Movie_model extends Model {

    function Movie_model()

    {

        parent::Model();

    }

    function get_review($id)

    {

        $this->db->select('*');

        $this->db->from('movie_review');

        $this->db->where('movie_review.id', $id);

        $this->db->join('authors', 'authors.id = movie_review.author');

        $this->db->join('persons', 'persons.id = movie_review.director_1');

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

        $info = $q->row();

        return $info;

    }

}

?&gt;

As you can see, I have done a join. However, this only works for director_1, I will not be able to do a second join for director_2.

Another problem is in the view file. How can I rename a variable so as to distinguish between the names of Director_1 and Director_2?

Code:
<h2>Title: &lt;?php echo "$info->title"; ?&gt;</h2>

<h2>Marketed title: &lt;?php echo "$info->title_marketed"; ?&gt;</h2>

<h3>Directed by: &lt;?php echo "$info->first_name $info->last_name"; ?&gt;&lt;?php if($info->director_2) { echo ", $info->first_name $info->last_name";} ?&gt;</h3>

<p>Review:</p>

<p>&lt;?php echo "$info->text;"?&gt;</p>

<p>Written by author &lt;?php echo "$info->name"; ?&gt;</p>

<p>on date &lt;?php echo "$info->date" ?&gt;</p>

Can anyone advise a stupido like me? Sad

Multiple functions for several joins in a query - how to do it? - El Forum - 04-20-2010

[eluser]Mischievous[/eluser]
Not sure about what your exactly trying to do but I always setup my DB requests like this:

Code:
function fetch_review($where, $join, $type = 'array')

    {

        $type = strtolower($type);

        if(is_array($where))

        {

             foreach($where as $column => $value)

             {

                 $this->db->where_in($column, $value);

             }

        }

        if(is_array($join))

        {

             foreach($join as $table => $fields)

             {

                 $this->db->join($table, $fields);

             }

        }

        $this->db->join('authors', 'authors.id = movie_review.author');

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

        if($this->db->count_all_results() > 0)

        {

            switch($type)

            {

                case "object":

                    $request = $result->result();

                break;

                case "bool":

                    return TRUE;

                break;

                default:

                    $request = $result->result_array();

            }

            return $request;

        } else {

            return FALSE;

        }

    }

Usage:

Code:
$where = array('id' => $id);

    $join = array('persons' => 'persons.id = movie_review.director_1');

    $this->fetch_review($where, $join);

Which allows you to pass where statements and join statements as you need dynamically?
As for the naming, setup a format_result() function and run through the result and add your prefix's as needed d1_director, d2_director etc. etc.

Multiple functions for several joins in a query - how to do it? - El Forum - 04-20-2010

[eluser]Federico Baña[/eluser]
You can make a second join to the persons table by director_2 on the reviews table, in the case you dont have a second director, all the values that should match would return NULL. Also you'd need to set field aliases to avoid ambiguity.

Code:
&lt;?php

class Movie_model extends Model {

    function Movie_model()

    {

        parent::Model();

    }

    function get_review($id)

    {

        $this->db->select('*'); // change this line and select all fields one by one, for it as a good practice and also you need to set field aliases there

        // here with the aliases (dont know the field names you use)

        $this->db->select('movie_review.field_x');

        $this->db->select('fst_directors.name AS director_1_name');

        $this->db->select('sec_directors.name AS director_2_name'); 

        $this->db->from('movie_review');

        $this->db->where('movie_review.id', $id);

        $this->db->join('authors', 'authors.id = movie_review.author');

        // match first director, you MUST USE an alias here, otherwise you'll get an error

        $this->db->join('persons AS fst_directors', 'fst_directors.id = movie_review.director_1');

        // march second director also with alias

        $this->db->join('persons AS sec_directors', 'sec_directors.id = movie_review.director_2');

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

        $info = $q->row();

        return $info;

    }

}

?&gt;

This way, a row on movie_review has a NULL director_2, the columns on the result set (director_2_name in the example) will be NULL as well.
There on the view you use a conditional to know if there's or there's not a second director.

Code:
if($info[$i]->director_2_name !== NULL){ echo 'This review has a second director' }

For example.

Hope this helps you...

Multiple functions for several joins in a query - how to do it? - El Forum - 04-21-2010

[eluser]titanite[/eluser]
Dear Mischevious: You are WAY too advanced for me... I'll study your method carefully and find out why you do it that way.

Federico: Thank you so much for your help! It was exactly what I needed to learn! Smile

However, I have run into one error. In the cases where $info->director_2 is NULL, I have the "Message: Trying to get property of non-object" error message.

When director_2 is not null, I have his name printed.

Why is there such an error? Sad

Code:
<h3>Directed by: &lt;?php echo "$info->director_1_firstname $info->director_1_lastname"; ?&gt;&lt;?php if($info->director_2_firstname !== NULL){ echo ", $info->director_2_firstname $info->director_2_lastname"; }  ?&gt;</h3>

Multiple functions for several joins in a query - how to do it? - El Forum - 04-22-2010

[eluser]titanite[/eluser]
Hi guys,

I don't get it. Why is it that when the database entry is NULL, I get this error message:

A PHP Error was encountered
Severity: Notice
Message: Trying to get property of non-object
Filename: libraries/Loader.php(673) : eval()'d code
Line Number: 9

I've checked the Loader.php file, it seems that the problem is with short tags?

What's wrong? Sad