Welcome Guest, Not a member yet? Register   Sign In
Model relationships
#1

[eluser]bayowar[/eluser]
I'm looking for advice/best practices for a simple problem. I searched the forums and documentation and didn't find much.

I have one model (Comments_model) that fetches all comments to a blog entry.
Part of each comment is data from a second model (User_model), that should get merged with each of my comments using a foreign key.

Or, more generally, I'd like to fetch an array of results from Model1 and merge each row with related data from Model2.

Without models, I'd JOIN the datasets in SQL and have one loop in my view.

With proper models, the best I (and most others, acording to my search results) could come up with is this:
Code:
class Comments_model extends Model {
{
    ...

    function getComments($entry_id)
    {
        $query = boring_db_stuff();

        if($query->num_rows())
        {
            $comments = $query->result_array();

            foreach($comments AS $key => $comment)
            {
                $comment_author = $CI->User_model->getUserData($comment['user_id']);
                $comment['user'] = get_object_vars($comment_author);

                $comments[$key] = $comment;
            }

            return $comments;
        }
        else
        {
            return 0;
        }
    }
}
... which loops twice: Once in the model and once in the view.

I can't do something like while($comment = $CI->Comments_model->getComment()), either. That would put controller logic in my view.

Admittedly, this is not the worst of problems, but it applies to a lot of cases and I'd rather safe a loop in each of those.


Please tell me what I'm missing here, I can't figure it out for the life of me.
#2

[eluser]darkhouse[/eluser]
Just because you have a users model and a comments model doesn't mean you can't return user data from the comments model.

I would do this:
Code:
class Comments_model extends Model {
   function getComments($entry_id){
      $this->db->select('c.comment, u.username'); //or whatever you're selecting
      $this->db->join('users u', 'c.user_id = u.id');
      $this->db->where('c.entry_id', $entry_id);
      $query = $this->db->get('comments c');
      return $query->result();
   }
}

Now you can do things like you normally would, and like you should.
#3

[eluser]Colin Williams[/eluser]
darkhouse is right. Your comments model is responsible for returning all the comment data and any data associated with it. Just because this comes from different/multiple tables doesn't make it wrong. I would go ahead and JOIN the data as needed, but keep an eye out for areas where processes can be abstracted.
#4

[eluser]bayowar[/eluser]
Right, thanks for the advice.

Alas, in my example the user data isn't coming from a table in my CI database.
User_model fetches data from the database of a forum - and because the structure
of that table is bound to change with forum versions and because I want to able
to switch forums down the road, I 'd like to use a model for abstraction.

Does that make sense?
#5

[eluser]obiron2[/eluser]
I have the same sort of logic issue.

Lets say I have a league table. I may to pull up the cumulative results for a location (lots of players) or for a player (lots of locations). Each data set would access the results table with location id and player id as filters in the where clause, so they are the same query.

Should they go into the players model, the locations model, both (and be written twice), a separate results model which is loaded as required and would require an initial query to get a list of players or locations and then a loop thorugh the results set for each cumulative results query.

I tested this on a more complex model (F1 league calculator) and what could be done in one BIG query but broke MVC rules ended up running over 1600 queries and almost killed the database.

Obiron
#6

[eluser]bayowar[/eluser]
I think darkhouse and Colin have a point with join.

If you need all locations in your players array, and vice versa, you could
join both tables with Active Record in each model

This works, if your tables aren't subject to frequent changes or require
abstraction for other reasons.
#7

[eluser]gon[/eluser]
You could write a users model method that returns user data for multiple user ids, and use that from the comments model. Then, do a second loop over comments and set the user data for each.
#8

[eluser]bayowar[/eluser]
[quote author="gon" date="1231956719"]You could write a users model method that returns user data for multiple user ids, and use that from the comments model. Then, do a second loop over comments and set the user data for each.[/quote]
At least that saves a few queries, so thanks.
#9

[eluser]obiron2[/eluser]
That was kind of my point. Do you write a join query multiple times and return a flat dataset or run each query separately looping through the previous results set.

E.g.

I am writing a system that has the following as 1 to many relationships with unique id as a foreign key in the child table:-

Leagues, Seasons, Divisions, Games, Results.

If I need to validate whether a league administrator has the rights to modify a specific result I need to run back up the hierachy to see whether they have a record in the League_admins table. Is it more effient from a process design to get the game from the result, load the games model and get the division from the game etc. or simply write one query in the results model that joins the fields together unitl I get to the leagues. doing it the first way requires multiple DB calls but has maximum flexibility. Doing it the second way requires only one DB call but I would need to write (and rewrite) the output result for each level of the query and breaks the Object concept of a game should not care how it is related to a Division; it should just ask the divison model for the answer.

Obiron
#10

[eluser]bayowar[/eluser]
Quote:a game should not care how it is related to a Division; it should just ask the divison model for the answer.
Exactly.

Quote:That was kind of my point. Do you write a join query multiple times and return a flat dataset or run each query separately looping through the previous results set.
I think at this point somebody would have pointed out a definite answer, if there was one.




Theme © iAndrew 2016 - Forum software by © MyBB