Model for stored function

[Oren.Me]

Hi,

I have a design issue I need to resolve before starting building my app.

I have a complex data structure which is saved in several small simple tables.

In order to query my DB for a complete comprehensive result I use stored functions.

This is true in majority of cases, and I seldom need only one table data for a certain view.

To make it short I’m having issues understanding what I need exactly in terms of MVC.

I’ll take an example of one view I have. this one view shows results of tests that are ran and their data is stored in a DB server.

The tests track numerous attributes and I use stored procedure to query the data in a way that can tell me what happened in the test.

This view will be controlled by one controller, e.g. resultController which actually won’t do much more then render the view as its data is coming out in a controlled manner from the stored function.

And what about the model? the model only needs to query out the stored function data, it has no data input.

Do I implement a model for this? How do I do that?

What is the recommended methodology in this case ?

By the way, this situation is true through out most of my application, so I’m wondering if this is a good practice or a bad design? I already have a working site that serves me and uses this stored functions and it is working well.

Any help will be welcomed.

[Javache (Pieter Debaets)]

And what about the model? the model only needs to query out the stored function data, it has no data input. Do I implement a model for this? How do I do that?

Implementing a model for this is the best approach as this will allow you to use all of yii’s query methods for generating your views.

You could generate your model using Gii, but you really only need the model() and tableName()-method.

[Oren.Me]

Implementing a model for this is the best approach as this will allow you to use all of yii’s query methods for generating your views.

You could generate your model using Gii, but you really only need the model() and tableName()-method.

How do I create a model for a stored function? I know how it is done for a table, but not for a function.

Is it okay to say that in my MVC the controller actually serves the view functionality more than it handles the model, if at all.

There is no correlation between the controller and the model other then just get data from it.

The view needs to get data from a couple of stored functions, and translate it to functionality in the view, so in my case let’s say that my view is called “aView” and my controller is called “aController”, but do I need “aModel”?

There is no such model in my DB, this "a" entity is constructed from a couple of stored functions in my DB. So basically "a" is a web app entity and not a DB entity/table.

[joblo (Joe)]

I think you don’t need a model. For me a model is useful for generating a user interface for CRUD operations.

Working with models produces a lot of overhead with bad performance.

For your needs I would use the basic database methods (DAO).

You can use the CSqlDataProvider if you need a dataProvider when using CListView to display the db-results in a view.

[Oren.Me]

So actually you recommend to skip the model and access the stored function directly through the controller?

Doesn’t this break the entire MVC principal?

I can create a model without it’s CRUD.

What will happen if I need to use this stored function elsewhere? I’ll have to repeat the SQL in every controller.

Don’t you think I should create the stored function in a model so it can be accessed by any controller?

And how do I generate a model for a stored function? is it possible?

[joblo (Joe)]

An AR model is designed to work with single and related tables or database views, not for usage with stored procedures.

This is because it uses the db schema behind, read and caching the metadata… This is not possible for stored procedures (with custom input/output params …).

Do you know another framework/programming language that can handle this on the fly??

I would create one or more custom components (maybe inherited from CSqlDataProvider) the handles the stored procedure stuff - depending on the context: DbBookingProvider, DbStatisticsProvider …

I would more think in ‘context-models’ than in ‘ar-models’ and single tables/views.

Or I would add a method to an existing AR-model:

Model Basket (with a single basket table behind)

When booking the basket you have a sp ‘bookBasket(…)’ where you have to do a lot of db validations/checks.

In this case I would add a method ‘bookBasket’ to the model ‘Basket’ that fires the sp.

Some links:

• Forum discussion

• DAO vs. AR performance

[Say_Ten (Yii)]

I would create models that encapsulate the data you need in the views. I would probably use a data source object to create an API for retrieving them, so if it’s ever switched out the API holds true. The models would not be active records however, just behave in a similar fashion by having the appropriate public attributes.