Page 1 of 1

Select * or Select [field1, ...?

Posted: Tue Oct 09, 2012 6:49 am
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?

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

Posted: Tue Oct 09, 2012 7:52 am
by Celauran
All what extra code? Your ORM should already handle this and should accept an array of parameters including which fields to get.

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

Posted: Tue Oct 09, 2012 8:02 am
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.

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

Posted: Tue Oct 09, 2012 1:29 pm
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.

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

Posted: Tue Oct 09, 2012 1:40 pm
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?

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

Posted: Tue Oct 09, 2012 2:13 pm
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();

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

Posted: Wed Oct 10, 2012 3:51 am
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?

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

Posted: Wed Oct 10, 2012 5:10 am
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.

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

Posted: Wed Oct 10, 2012 6:34 am
by shiznatix
Is an ORM really needed? Seams like a bunch of extra layers that could get even more complicated

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

Posted: Wed Oct 10, 2012 7:02 am
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.

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

Posted: Wed Oct 10, 2012 1:33 pm
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. ;)

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

Posted: Thu Oct 11, 2012 1:53 am
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.