MVC best practices

[eluser]Manxa[/eluser]
Hey all,

I'm doing some development, and I'm wondering about best practices. I've got a set of data stored in a MySQL database. I want to reduce the amount of overhead and code complexity. This "query" will get loaded every time the page header gets called.

I want to insert the query directly into the view just before I dump out the results. My reason for this is because I don't want to modify every since piece of code in the controllers when they call the header. Example of controller code below:

Code:

$this->load->view('header');
    $this->load->view('main/blog/blog', $data);
    $this->load->view('footer');

As you can see, every time I load the header, it would be another variable I'd have to pass. Multiply this by the number of controllers and I'll introduce a lot of code.

My concerns:

If I load the query directly into the view, will this induce a lot of overhead for the database? Is it good MVC practice?

If possible, can the array of data be stored in the session? This would reduce my dependency on database calls.

[eluser]TheFuzzy0ne[/eluser]
I don't see where you pass any variables through to the header. However, xwero showed me how to use $this->load->vars() to load variables, so I could then use my main template (which takes care of loading any subviews such as header, top navigation, side bar, content, footer etc...) to take care of everything. I'm not sure if this is the answer to your question, however, but hopefully it helps.

http://ellislab.com/forums/viewthread/106648/

Oh, and yes, I store a lot of data in the session, but my session uses my database. Also, I'd like to point out that a lot of applications around, specifically forums, can make in excess of thirty database queries per transaction, so I wouldn't get too hung up on that if I were you. I made that mistake.

[eluser]JayTee[/eluser]
[quote author="Manxa" date="1235957009"]Hey all,

I'm doing some development, and I'm wondering about best practices. I've got a set of data stored in a MySQL database. I want to reduce the amount of overhead and code complexity. This "query" will get loaded every time the page header gets called.

I want to insert the query directly into the view just before I dump out the results. My reason for this is because I don't want to modify every since piece of code in the controllers when they call the header. Example of controller code below:

Code:

$this->load->view('header');
    $this->load->view('main/blog/blog', $data);
    $this->load->view('footer');

As you can see, every time I load the header, it would be another variable I'd have to pass. Multiply this by the number of controllers and I'll introduce a lot of code.

My concerns:

If I load the query directly into the view, will this induce a lot of overhead for the database? Is it good MVC practice?

If possible, can the array of data be stored in the session? This would reduce my dependency on database calls.[/quote]
Based on the data you've given, I feel like you're already doing things the long way. Without getting into specifics about your situation, I'm going to just list a few things that I've found have been helpful for making my app as quick as possible (for the DB-intensive apps)

1. User preferences can easily be stored in a session as an array of integers rather than with full variables. Using constants to access the values make your code more readable. In addition, you can query the DB one time (at login) to get the user's preferences:

Code:

//fictitious preference array (stored in the session)
$user_prefs = array(1,1,2);
//constants:
//preference indexes
define('PREF_TIMEZONE',0);
define('PREF_THEME',1);
define('PREF_EMAIL',2);

//preference values
define('TIMEZONE_EST',1);
define('TIMEZONE_PST',2);

define('THEME_BLUE',1);
define('THEME_RED',2);

define('EMAIL_SEND',1);
define('EMAIL_NOT_SEND',2);

//code:
if ($user_prefs[PREF_TIMEZONE] == TIMEZONE_EST)
{
  //do something
}
if ($user_prefs[PREF_THEME] == THEME_RED)
{
  //show red.css
}
else if ($user_prefs[PREF_THEME] == THEME_BLUE)
{
  //show blue.css
}
else
{
  //show default.css
}

2. Try to use HTML snippets to build a final view:

Code:

//views/main.php
<html>
<head>
<title><?php echo $page_title ?></title>
</head>
<body>
<?php echo $blog.$footer ?>
</body>
</html>

//views/blog.php
<?php foreach($entry in $blog_entries): ?>
<div><h3>&lt;?php echo $entry->title ?&gt;</h3>
<p>&lt;?php echo $entry->body ?&gt;</p>
</div>
&lt;?php endforeach; ?&gt;

//views/footer.php
<div id="footer">Copyright 2009 &lt;?php $your_var ?&gt;</div>

//controller
function index()
{
  $page_data = array(
    'page_title' => 'My Page Title',
    'blog' => $this->load->view('blog',$this->modelname->get_blog_entries(),TRUE),
    'footer' => $this->load->view('footer',array('your_var' => 'I love CI'),TRUE)
  );
  $this->load->view('main',$page_data);
}

The Template library does an even better job of making the above code cleaner and more readable. http://williamsconcepts.com/ci/codeignit...s/template

3. It's important to make your database queries as slim as possible to reduce the stuff you don't need. For example:

Code:

SELECT * FROM table

is far more inefficient when you only need 2 of the columns from the table. I don't even use the * anymore.

(I'll make #4 last so the post isn't too gigantic)
4. Reduce the number of db calls by retrieving larger results with fewer queries. This one has probably saved me the most - but it's weird to explain.
Scenario:
You have X invoices with Y number of line items each
The database is something like (this is an oversimplification, mind you):
invoice: id, title, date, customer_id
line_item: id, invoice_id, qty, description

Option 1
1. Query the DB to get the invoice data
2. Loop through the invoice record set
2. When displaying the invoice data, query the db to get the line item data for each invoice
3. Loop through the line item recordset for display
4. Result: 1 query for the invoices, 1 query for each invoice's line items (depending on number of invoices)
Option 2
1. Query the DB to get all invoices and their line items at once
2. Recursive loop to show invoices and their respective line items
3. Result: 1 query, 1 loop

While recursive loops *can* be difficult to troubleshoot if you're not comfortable with them; they are really useful in reducing db queries for situations like the one I described. This same time of scenario can be used anytime you have a parent/child type of relationship like that.

Hope this helps?

[eluser]darkhouse[/eluser]
If you're loading something in the page header, another thing you can do is create a library, autoload it, and then in your header just do <?php $this->some_library->output_your_data(); ?>

Another tip, instead of loading all 3 of those views in each controller, why not load the main/blog/blog view, and have that view load the other views.

[eluser]TheFuzzy0ne[/eluser]
This is my main template.
./system/application/views/main_template.php

Code:

<?php echo $this->assets->doctype; ?>
<html lang="en" xml:lang="en">
<head>
    <title><?php echo $title; ?></title>
<?php echo $this->assets->get_all(); ?>
</head>
<body id="<?php echo $body_id; ?>">
<?php $this->load->view('frag/header'); ?>
<?php $this->load->view('frag/topnav'); ?>
<?php echo $content; ?>    
<?php $this->load->view('frag/footer'); ?>
</body>
</html>

Can't get much simpler than that...

I need to supply a few basic variables, such as body ID, and page title, as well as the content, but the views extract any other information they need direct from the URI, or userdata. I can also modify things, such as the doctype, or add assets such as scripts, meta data, and CSS from within the controller if needed, although my assets library is set to load some default CSS which is used on every page.

Here's an example of a controller method.

Code:

function login()
{        
    if ($this->input->post('submit') && $this->auth->doLogin())
    {                        
        redirect($this->input->post('login_return'));
    }

    $this->load->vars(array(
            'body_id' => 'login',
            'title' => 'Login',
            'login_return' => $this->input->post('login_return'),
            'error' => $this->auth->error_string,
            'view:content' => 'forums/member/login'
        ));

    $this->load->view('main_template');
}

Again, pretty simple, but very effective. The other thing I like about this method, is that if I forget to define a variable that one of the views requires, I get a PHP warning reminding me to do so.

[eluser]Matthieu Fauveau[/eluser]
[quote author="JayTee" date="1235966268"]
Option 2
1. Query the DB to get all invoices and their line items at once
2. Recursive loop to show invoices and their respective line items
3. Result: 1 query, 1 loop
[/quote]

Can't see how you would get all invoices and their lines at once. Would you care to explain ?
Maybe I'm just to tired to realize, but right now the only way I see is by issuing two queries.

[eluser]TheFuzzy0ne[/eluser]
LEFT JOIN the line_item table to the invoice table.

Code:

SELECT *
FROM `invoice`
LEFT JOIN `line_item`
ON `invoice`.`id` = `line_item`.`invoice_id`;

[eluser]JayTee[/eluser]
[quote author="Matthieu Fauveau" date="1235985139"][quote author="JayTee" date="1235966268"]
Option 2
1. Query the DB to get all invoices and their line items at once
2. Recursive loop to show invoices and their respective line items
3. Result: 1 query, 1 loop
[/quote]

Can't see how you would get all invoices and their lines at once. Would you care to explain ?
Maybe I'm just to tired to realize, but right now the only way I see is by issuing two queries.[/quote]
Here's a query (based on my example):

Code:

SELECT
  inv.id as invoice_id,
  inv.title,
  inv.date,
  inv.customer_id,
  inv_li.id as lineitem_id,
  inv_li.invoice_id as parent_id,
  inv_li.qty,
  inv_li.description
FROM  invoice inv
INNER JOIN line_item inv_li ON inv_li.invoice_id = inv.id
ORDER BY inv.id, inv_li.id ASC

What you'll end up with is repeated data for each of the 'main' invoice details - with different line item item data for each line item. Let's say it looks like this:

Code:

//pardon my weak attempt at formatting
| invoice_id | title     | date     | customer_id | lineitem_id | parent_id | qty | description  |
| 1          | invoice 1 | 2/2/2009 | 4           | 1           | 1         | 3   | gold nuggets |
| 1          | invoice 1 | 2/2/2009 | 4           | 2           | 1         | 1   | holy grails  |
| 1          | invoice 1 | 2/2/2009 | 4           | 3           | 1         | 9   | coconuts     |
| 2          | invoice 2 | 2/5/2009 | 7           | 4           | 2         | 6   | arrows       |
| 2          | invoice 2 | 2/5/2009 | 7           | 5           | 2         | 7   | grail beacon |
| 2          | invoice 2 | 2/5/2009 | 7           | 6           | 2         | 5   | whips        |
| 2          | invoice 2 | 2/5/2009 | 7           | 7           | 2         | 3   | chains       |

When you do your loop, you can check for invoice_id changes and/or parent_id changes to let you know when you've completed your display logic.

For fun and games, here's the dump from phpmyadmin if you want to try it:

Code:

CREATE TABLE IF NOT EXISTS `invoice` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `date` varchar(20) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

--
-- Dumping data for table `invoice`
--

INSERT INTO `invoice` (`id`, `title`, `date`, `customer_id`) VALUES
(1, 'invoice 1', '2/2/2009', 4),
(2, 'invoice 2', '2/5/2009', 7);

-- --------------------------------------------------------

--
-- Table structure for table `line_item`
--

CREATE TABLE IF NOT EXISTS `line_item` (
  `id` int(11) NOT NULL auto_increment,
  `invoice_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);

--
-- Dumping data for table `line_item`
--

INSERT INTO `line_item` (`id`, `invoice_id`, `qty`, `description`) VALUES
(1, 1, 3, 'gold nuggets'),
(2, 1, 1, 'holy grails'),
(3, 1, 9, 'coconuts'),
(4, 2, 6, 'arrows'),
(5, 2, 7, 'grail beacon'),
(6, 2, 5, 'whips'),
(7, 2, 3, 'chains');

[eluser]Matthieu Fauveau[/eluser]
Hi JayTee,

You didn't need to put all that code to explain the way you do it, but thanks anyway.

I was aware of that possibility but I think there is some concerns :

- It will probably (I did not test) consume more memory to have duplicate data in each rows ?
- It will surely consume more bandwidth (I'm thinking of those of us that have a separate server for MySQL)

Also I'm wondering if it would be faster to do :

Code:

(SELECT id AS invoice_id, title, date, customer_id, NULL AS line_id, NULL AS qty, NULL AS description FROM invoice)
UNION
(SELECT invoice_id, NULL, NULL, NULL, id AS line_id, qty, description FROM line_item)
ORDER BY invoice_id ASC, line_id ASC

Anyways we will need a lot more data to see which one is faster.

[eluser]JayTee[/eluser]
[quote author="Matthieu Fauveau" date="1236069763"]You didn't need to put all that code to explain the way you do it, but thanks anyway.[/quote]
Ahh... I just wanted to make sure I got my point across :cheese:

There's always more than one way to do things. The duplicate data in the result set is bigger; so on a super-large website, you'll definitely run into performance issues.

From the looks of it, you're headed in the right direction

good luck