Thoughts on Mysql_Views and Tables in same model

Hi All,

Bit of a newbie question here, but I did not find any references in the forum on this.

By way of context, I've been maintaining our internal PHP portals for the last 6 years. It was written as a MV (no C) using PHP, and Ajax in a Wordpress site. We used no php framework and coded all the PHP data interfaces by hand. The time has come to start from scratch and I feel like I have been doing a lot of catchup learning about the new technologies. From all my research, I am settling on CI as robust framework to use (although aurelia looks good, but too new I suspect).

Now to the question! In my existing php data interfaces I often use a mysql view for reading but write to the table. Typically, when needed, there is a view that is paired with a table. The best example of this is an invoice master and detail table. The total of the invoice is the sum of (detail amounts * detail quantity), and we all know it is bad practice to store that number. When I get all the invoices for a client, I want to see the total value of the invoice returned with the date, invoice number etc. I use a MYSQL View to sum that together and join it with the invoice master so the view returns date, invoice no, ..., total. This makes my php code simple because I do not have to do any looping and calculating just pass the results straight on up.

To reiterate, this means that in my current model I am reading from a mysql_view but writing to a table (my model knows which fields are read-only and dont exist in the table so it does not expose modification functions for them).

So as a best practice question, would/could you do something similar in CI?

Thanks

Matt.

There shouldn't be anything preventing you from taking this approach in CI. If you implement a completely MVC approach, your model would be able to use the view for some/all find/get requests and update/insert data in the table(s), and your controller and view(s) would never have a reason to know the difference.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

One rule I apply is the reusability of the code. I would say when learning your way through MVC, when in doubt, put the code in the Model. This is because any Controller can call a Model, but the reverse is not true. Model code is more reusable, and minimizing redundant SQL across one or more files creates a project that is easier to maintain as it evolves. I would say the View is the worst place for SQL or any logic that manipulates the data.

Fat vs. skinny models is it's own debate, but I think this advice is the most helpful to someone being introduced to CI.

@colonelclick he was talking about SQL views, not MVC views. In SQL, a view is basically a SQL query which can be treated like a table. SQL views are often used for more complicated SQL statements, or to isolate underlying structural changes in the database from the application code (when used in combination with stored procedures to handle delete/update/insert procedures, you could make drastic changes to the database without changing your application code).

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

You can do this of course also with CI. Its depends on your personal style of coding and doing database operations and of the situation. i like views and use them where ever it makes sence. As performance reason a view should alwasy have a where clause otherwise its better do make a normal select.

I use views also if the client user / programmer should only work with a result and the database structur should be hold private. So that would be for security reasons.