Select * or Select [field1, ...?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Select * or Select [field1, ...?

Post by shiznatix »

So, I have always thought that a model should have a bunch of getters to get exactly what was needed out the DB, no more and no less. When all I needed was the column "message" I would always have something like:

Code: Select all

$thing = $model
->select()
->from($model, 'field_name')
->where('id = ?', $id)
->query()
->fetch();
now I am starting to rethink this. If I am using Zend, I can always just do $model->find($id). This will return _all_ fields from this row though instead of just the 1 field I need. I always thought that returning too many fields was just a total waste but now that I have bigger and bigger code bases, all of these getters are starting to become combersom.

So, really, how much is really gained by only returning the fields that you need? Is it worth writing all the extra code to get just those or is it ok to just return all columns from a table and just ignore the ones that you dont need?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select * or Select [field1, ...?

Post by Celauran »

All what extra code? Your ORM should already handle this and should accept an array of parameters including which fields to get.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Select * or Select [field1, ...?

Post by shiznatix »

Celauran wrote:All what extra code? Your ORM should already handle this and should accept an array of parameters including which fields to get.
Not in Zend that I have ever found. There is no $model->select($array)->where($id). There is only a $model->find($id) which returns the entire row instead of just the fields that I am looking for.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Select * or Select [field1, ...?

Post by Christopher »

shiznatix wrote:So, really, how much is really gained by only returning the fields that you need? Is it worth writing all the extra code to get just those or is it ok to just return all columns from a table and just ignore the ones that you dont need?
It depends on the length of your rows. Obviously if you have many, many fields or big TEXT or BLOB data in the record then that all needs to be fetched. But the difference between one INT and a few INTs is going to be negligible.

And then there is the Premature Optimization question: Do you have a performance problem if you return all fields in a row? If you don't then the extra code is just extra time to create and support. If you later have a problem then you can always optimize.

PS - I tend to have model methods like getSomeSpecificData() that return only the fields I need from one or more tables for that specific need.
(#10850)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Select * or Select [field1, ...?

Post by shiznatix »

I understand. I dont have a lot of fields to return really, mostly ints and a few varchars but always small ones so I guess I can save myself some time and just return everything, easy.
Christopher wrote:PS - I tend to have model methods like getSomeSpecificData() that return only the fields I need from one or more tables for that specific need.
I get this a lot too. I have bunches of those in my models and I am really scratching my head about what to do with them. On one hand, if I remove them and do an individual query each time, I will regain my models to some extent. Currently, most of my models are really just "getters" where each method just does a specific DB select. This just seams so...dirty.

But, if I remove those getter models, it is going to be painful to keep track of all the individual queries and if a change is made it will be painful.

I guess I am really trying to make my models to be more class-like but I keep ending up with mostly procedural code that is wrapped in functions and those functions just happen to be in a class. I am always trying to get away from this but I always end up back there. Any hints or tips for better structure?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Select * or Select [field1, ...?

Post by Christopher »

I often have a generic find($where, $fields='*', $sort='') type method in my Models that the other methods call. That makes them essentially two-line methods with the SQL and call to find(). It reduces code while still provideing a clear interface, encapsulateinh the SQL, and can centralize any model specific processing in find().

As for eliminating getters, not sure how you can do that because they are how Views get their data. For other operations, you might want to research "Tell, don't ask". That may help you get away from procedural style. And by procedural style, I hope you don't mean truly procedural things like:

Code: Select all

$data = $model->getSomeData();
$model->doSomething($mode);

// or
$model->mustBeDoneFirst();
$model->hasToBeDoneSecond();
(#10850)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Select * or Select [field1, ...?

Post by shiznatix »

My problem is I always end up with things like this:

Code: Select all

$companiesModel = new Model_Admin_Companies;
$clientsModel = new Model_Admin_Clients;

foreach ($this->view->invoices as &$invoice)
{
	$invoice['totalPaid'] = $this->invoicePaymentsModel
	->getTotalPaid($invoice['invoiceid']);
	
	$invoice['latestPayment'] = $this->invoicePaymentsModel
	->getLatestPayment($invoice['invoiceid']);
	
	$invoice['clientNumber'] = $clientsModel
	->getClientNumber($invoice['companyid']);
}
I am not sure exactly how to get away from those though. That is really where I am having an issue as this doesn't seam right. Any suggestions?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select * or Select [field1, ...?

Post by Celauran »

shiznatix wrote:
Celauran wrote:All what extra code? Your ORM should already handle this and should accept an array of parameters including which fields to get.
Not in Zend that I have ever found. There is no $model->select($array)->where($id). There is only a $model->find($id) which returns the entire row instead of just the fields that I am looking for.
ZF doesn't include an ORM as far as I know. I would consider including something like Propel or Doctrine.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Select * or Select [field1, ...?

Post by shiznatix »

Is an ORM really needed? Seams like a bunch of extra layers that could get even more complicated
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Select * or Select [field1, ...?

Post by Celauran »

No, it certainly isn't required, but I find them very handy.

Code: Select all

$this->Model->RelatedModel->find('fields' => array('foo', 'bar'));
Looks like a good fit for what you're trying to accomplish.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Select * or Select [field1, ...?

Post by Christopher »

Code: Select all

$this->Model->RelatedModel->find('fields' => array('foo', 'bar'));
That is technically not only an ORM example, but is an OOSQL query interface. Most ORMs have them, but so does most ActiveRecord and TableDataGateway implementations as well. Sorry to nitpick. ;)
(#10850)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Select * or Select [field1, ...?

Post by shiznatix »

Aye, thanks for the suggestions. I will try to get this all running and if I have any problems, you know where I will be posting.
Post Reply