MVC confusion redux

[eluser]Maglok[/eluser]
It's another one of those 'where do I do this' things.

Some info:

2 tables: News, Person

Person has the Firstname, Lastname columns while every row in the News table has a author_id that corresponts with an id in the person table.

2 models: model_news, model_person

Model_news has a get_all() that gets the entire table of news. Model_person has a get_name() that will return a string with the fullname of a person with a parameter of ID.

1 view: newsindex

Displays the data in a nice way.

So now I have to run a get_name() for each news post on the page. But I should not call that from the view, views don't talk directly to models do they? I am a bit confused.

[eluser]rogierb[/eluser]
I would create a new method get_news_with_author() ;-)
A lot faster en a lot less queries.

If you don't want to do that then loop in your controller en use get_name() there. Store the values in a new array and pass that aray (with news and name) to the view.

[eluser]Maglok[/eluser]
I considered that though then I'd have to make a method for like every specific call (of course there are exceptions) to the database I have to make. Seems a bit cluttery.

[eluser]jedd[/eluser]
This anti-clutter position would have more credibility if you didn't have a get_all() function. (How often do you use that?)

How about a get() function in your News model that looks like this:

Code:

function get  ( $author = false )  {
   if ($author)
      $query_string = SELECT .. yada yada JOIN yada yada WHERE author = $author
   else
      $query_string = SELECT * FROM news
  
   // Do the usual stuff here with the query string ..
   }

You could get more sophisticated by having additional params for get - perhaps limiting, starting at, options for changing the join etc. Personally I'd stick with nice short, sharp functions that do exactly what they say they do - I find it's more clutter in my files but much less clutter in my brain.

[eluser]Maglok[/eluser]
I use get_all() to get all the news in the system. I was gonna add a limit parameter once it worked. I'll think on the get() instead of get_all(). I've been looking in a buncha example applications and they tend to go the get_all() route.

I am atm implementing it all in one query in the model, but am now tripping up over the SQL. A news post has 2 IDs besides it's primary key. One for the author, one for a potential editor. I need to write a query that selects the names of both the author and the editor. That's doable if it was just one name, but it is thus two. Never had a situation like this before.

[eluser]rogierb[/eluser]
I agree with Jedd. As for you latest post, go for a join on both the tables. It is not bad practice to have multiple methods like get_all_news(), get_news_author(), get_news_author_editor().

But like Jedd said, it can be done in one method.

[eluser]jedd[/eluser]
[quote author="Maglok" date="1259339776"]
I use get_all() to get all the news in the system. I was gonna add a limit parameter once it worked.
[/quote]

At that instant you'll be breaking UN regulations if you still call it 'get_all'

I am atm implementing it all in one query in the model, but am now tripping up over the SQL. A news post has 2 IDs besides it's primary key. One for the author, one for a potential editor. I need to write a query that selects the names of both the author and the editor. That's doable if it was just one name, but it is thus two. Never had a situation like this before.

Sounds fun. Describe table structure, desired output, and what you've got for a query so far.

[eluser]Maglok[/eluser]
UN regulations? What would that be? I do want it to be as compact as possible, so if I can get several methods I got atm back into one get(), that would be neat. Since I obviously have a bunch more model classes that do the same thing.

Well I have these two table's (dutch though)

Code:

TABEL PERSONEN (is bigger, but this is relevant)
- id            INT, auto_increment, primary key
- voornaam        VARCHAR
- achternaam        VARCHAR
- tussenvoegsel        VARCHAR

TABEL NIEUWS
- id            INT, auto_increment, primary key
- titel            VARCHAR
- inhoud        TEXT
- categorie        VARCHAR
- community        BOOL
- auteur_id        INT
- aanpasser_id        INT
- date_created        DATETIME
- date_modified        TIMESTAMP

Now in a news post I need to grab both the name of the author (auteur_id) and the name of a (potential) editor (aanpasser_id), which are both people in the 'personen' table.

I am leaning towards something like:

Code:

SELECT nieuws.*, personen.achternaam, personen.voornaam, personen.tussenvoegsel
FROM nieuws
INNER JOIN personen
ON nieuws.auteur_id = personen.id
INNER JOIN personen
ON nieuws.aanpasser_id = personen.id

There are two things fundamentally wrong with this though. That is that aanpasser_id can be empty and also that the query cannot differentiate between the author name and the editor name. Voornaam+tussenvoegsel+achternaam == name btw.

[eluser]rogierb[/eluser]
You can use something like:

Code:

SELECT n.*, a.achternaam as a_achternaam, a.voornaam as a_voornaam, a.tussenvoegsel as  a_voorvoegsel,
e.achternaam as e_achternaam, e.voornaam as e_voornaam, e.tussenvoegsel as e_voorvoegsel
FROM nieuws  n
LEFT JOIN personen a
ON n.auteur_id = a.id
LEFT JOIN personen e
ON n.aanpasser_id = e.id

a = author
e = editor

When no author or editor is found, the fields would be empty

[eluser]Maglok[/eluser]
Ooh, that's a neat trick.

Code:

SELECT `n`.*, `a`.`voornaam` as a_voornaam, `a`.`tussenvoegsel` as a_tussenvoegsel,
`a`.`achternaam` as a_achternaam, `e`.`voornaam` as e_voornaam,
`e`.`tussenvoegsel` as e_tussenvoegsel, `e`.`achternaam` as e_achternaam
FROM (`nieuws` n)
LEFT JOIN `personen` a ON `n`.`auteur_id` = `a`.`id`
LEFT JOIN `personen` e ON `n`.`aanpasser_id` = `e`.`id`
ORDER BY `n`.`date_created` desc

Code:

function get_all() {
         $this->db->select('n.*, a.voornaam as a_voornaam, a.tussenvoegsel as
a_tussenvoegsel, a.achternaam as a_achternaam, e.voornaam as e_voornaam,
e.tussenvoegsel as e_tussenvoegsel, e.achternaam as e_achternaam');
        $this->db->from('nieuws n');
        $this->db->join('personen a','n.auteur_id = a.id', 'left');
        $this->db->join('personen e','n.aanpasser_id = e.id', 'left');
        $this->db->order_by('n.date_created', 'desc');

        return $this->db->get();
    }

EDIT: Implemented it, and it works now, see above. Thanks for the help. I am now going to look into the get_all, get business.