Mvc and only selecting fields needed

Question

I cant seem to find an acceptable answer to this.

There are two big things I keep seeing: 1) Don't execute queries in the controller. That is the responsibility of business or data. 2) Only select the columns that you need in a query.

My problem is that these two things kind of butt heads since what is displayed in the UI is really what determines what columns need to be queried. This in turn leads to the obvious solution of running the query in the controller, which you aren't supposed to do. Any documentation I have found googling, etc. seems to conveniently ignore this topic and pretend it isn't an issue.

Doing it in the business layer

Now if I take it the other way and query everything in the business layer then I implicitly am making all data access closely reflect the ui layer. This is more a problem with naming of query functions and classes than anything I think.

Take for example an application that has several views for displaying different info about a customer. The natural thing to do would be to name these data transfer classes the same as the view that needs them. But, the business or service layer has no knowledge of the ui layer and therefore any one of these data transfer classes could really be reused for ANY view without breaking any architecture rules. So then, what do I name all of these variations of, say "Customer", where one selects first name and last name, another might select last name and email, or first name and city, and so on. You can only name so many classes "CustomerSummary".

Entity Framework and IQueryable is great. But, what about everything else?

I understand that in entity framework I can have a data layer pass back an IQuerable whose execution is deferred and then just tell that IQueryable what fields I want. That is great. It seems to solve the problem. For .NET. The problem is, I also do PHP development. And pretty much all of the ORMs for php are designed in a way that totally defeat the purpose of using an ORM at all. And even those dont have the same ability as EF / IQueryable. So I am back to the same problem without a solution again in PHP.

Wrapping it up

So, my overall question is how do I get only the fields I need without totally stomping on all the rules of an ntier architecture? And without creating a data layer that inevitably has to be designed to reflect the layout of the UI layer?

Answer 1

And pretty much all of the ORMs for php are designed in a way that totally defeat the purpose of using an ORM at all.

The Doctrine PHP ORM offers lazy loading down to the property / field level. You can have everything done through proxies that will only query the database as needed. In my experience letting the ORM load the whole object once is preferable 90%+ of the time. Otherwise if you're not careful you will end up with multiple queries to the database for the same records. The extra DB chatter isn't worthwhile unless your data model is messy and your rows are very long.

Keep in mind a good ORM will also offer a built-in caching layer. Populating a whole object once and caching it is easier and more extensible then having your code keep track of which fields you need to query in various places.

So my answer is don't go nuts trying to only query the fields you need when using an ORM. If you are writing your queries by hand just in the places you need them, then only query the fields you need. But since you are talking good architectural patterns I assume you're not doing this.

Of course there are exceptions, like querying large data sets for reporting or migrations. These will require unique optimizations.

Answer 2

Questions

1) Don't execute queries in the controller. That is the responsibility of business or data.

How you design your application is up to you. That being said, it's always best to consider best patterns and practices. The way I design my controllers is that I pass in the data layer(IRepository) through constructor and inject that at run time.

public MyController(IRepository repo)

To query my code I simply call

repository.Where(x=> x.Prop == "whatever")

Using IQueryable creates the leaky abstraction problem. Although, it may not be a big deal but you have to be careful and mindful of how you are using your objects especially if they contain relational data. Once you query your data layer you would construct your view model in your controller action with the appropriate data required for your view.

public ActionResult MyAction(){

   var data = _repository.Single(x => x.Id == 1);

   var vm = new MyActionViewModel {
       Name = data.Name,
       Age = data.Age
   };

   return View();
}

If I had any queries that where complex I would create a business layer to include that logic. This would include enforcing business rules etc. In my business layer I would pass in the repository and use that.

2) Only select the columns that you need in a query.

With ORMs you usually pass back the whole object. After that you can construct your view model to include only the data you need.

My suggestion to your php problem is maybe to set up a web api for your data. It would return json data that you can then parse in whatever language you need.

Hope this helps.

Answer 3

The way I do it is as follows:

Have a domain object (entity, business object .. things with the same name) for Entities\Customer, that has all fields and associated logic for all of the data, that a complete instance would have. But for persistence create two separate data mappers:

• Mappers\Customer for handling all of the data
• Mappers\CustomerSummary for only important parts

If you only need to get customers name and phone number, you use the "summary mapper", but, when you need to examine user's profile, you have the "all data mapper". And the same separation can be really useful, when updating data too. Especially, if your "full customer" get populated from multiple tables.

// code from a method of some service layer class
$customer = new \Model\Entities\Customer;
$customer->setId($someID);

$mapper = new \Model\Mappers\CustomerSummary($this->db);
if ($needEverything) {
    $mapper = new \Model\Mappers\Customer($this->db);
}

$mapper->fetch($customer);

As for, what goes where, you probably might want to read this old post.