CODEWITHSUNDEEP
phpmysqlmodel-view-controller
imperium2335
imperium2335

Reputation: 24122

Dynamic SQL in Model or Controller

I know all logic etc should go in the controller, but I have an SQL statement where some parts of it are determined by another function's return value.

In my model:

    $cols = 'products.id, name, currencies.symbol, basePrice';
    $m = new AuthController();
    if($m->moduleExists('Sales'))
    {
      $cols .= 'lastSold';
    }

Or in my controller and have the SQL passed to the model as a parameter?

    $cols = 'products.id, name, currencies.symbol, basePrice';
    if($this->moduleExists('Sales'))
    {
      $cols .= 'lastSold';
    }

Upvotes: 1

Views: 495

Answers (1)

melc
melc

Reputation: 11671

tldr; version

Having in mind the separation of concerns and decoupling, code related to persistence optimally should not contain business logic related to sub modules of the specific system (whether that logic belongs in controllers of the Model part, of MVC, or the Controller part, or any other part the programmer has introduced to the system). Access from the persistence layer to business logic should be avoided to maintain decoupling, the other way around business logic accessing the persistence layer should be preferred.

Justification

In your model you should place code that is not relevant to what is shown to your views i.e. whether a module exists or not or whether authorised to have such a module. The code in your model should be useable by your sub systems, other systems, anything independent of your views or even business logic related to specific use cases. It should have logic related to the scope of the system. Although authorisation seems to have system scope this should be handled with extra care, since it may lead to brittle design. It is more flexible to have it at the controller level. Imagine trying to support multiple techniques of authorisation.

Depending on authorisation or view criteria to modify queries will not maintain a stable independent model.

So yes it should go in your controller.

The controller whether it is a controller related to the view or specific business logic (use case related or system related i.e. authorization controller) now could have the authorization criteria which change the query and in the future some other criteria may come up that are related to specific use case, or sub system domain. This means that the controller could change and also you will have the ability to even introduce different controllers accessing the same model with a specific way.

Implementation

There are plenty of approaches regarding your issue. Such as,

  • retrieve a full version of your data i.e. all columns, from your persistence layer and have your controller decide what to show based on logic i.e. authorization of user
  • have more than one queries in your persistence layer independent from your other logic i.e. queryProducts, queryProductsWithTotals, queryProductsWithLastSoldDate, then have your business logic controllers decide which to call
  • create a generic function in persistence layer, accepting cols (non magic values if possible) in order to parametrize what can be viewed based on logic that other controllers have and call this function accordingly
  • in general it is best to avoid placing sql queries in different places, since the maintenance becomes difficult. However there might be cases that this could happen and strongly depends on the design of the system i.e. business controllers with custom queries per use case that call the persistence layer in order to execute.

Upvotes: 1

Related Questions