0

Figure1: Here's an example Model class

class Games {
    public $id = 0;
    public $date = null;
    public $player1_id = 0;
    public $player2_id = 0;
    public $score = null;

    public function save(){
        if(empty($this->id)){
            // do INSERT here
        }
        else{
            // do UPDATE here  
        }
    }
}

Figure2: Here's an example use of the class

// Save a new date to an existing game
$game = new Games;
$game->id = $input['id'];
$game->date = $input['date'];
$game->save();

If an UPDATE query is performed based on figure2, then the values for player1_id, player2_id, and score will be incorrectly overwritten to zeros/nulls. Therefore, I need a way to determine from inside the class that certain properties were not set from the call in figure2, so I can dynamically alter the UPDATE query to only update specific fields in the database. Or am I approaching this wrong?

Note: I know I could use a single array property to hold all the fields (and use isset), but this feels like it's breaking the point of the Model and would also be very PHP specific (in that the solution doesn't transfer nicely to other languages such as JAVA where arrays are strictly typed?). I also realize I could do what I think ORMs do and make an initial SELECT query before I perform an update (but that seems very inefficient!)

tereško
  • 58,060
  • 25
  • 98
  • 150
prograhammer
  • 20,132
  • 13
  • 91
  • 118
  • Dumb question - why are you creating a new Games object in figure 2? – Major Productions Jan 26 '15 at 23:20
  • LOL! Sorry, let me update that...(forgot the `$game->save();` call) – prograhammer Jan 26 '15 at 23:23
  • 1
    You're right, ORM could do that for you. I suggest you to look into these, they'll save you a lot of time writing and updating boilerplate code or debugging idiot bugs. – pyb Jan 26 '15 at 23:29
  • ORMs don't fit my project well. Also see this great post on whether ORMs are good for complex projects: http://stackoverflow.com/questions/7765070/redbean-orm-performance – prograhammer Jan 26 '15 at 23:35
  • I do however use classes for connections/prepares/etc. All the DAL stuff. – prograhammer Jan 26 '15 at 23:36
  • You're needlessly worried about optimization, IMO. It's okay to run a SELECT if you need one. DBAs complain about queries because far too often bad coders stick queries in loops, or otherwise write bad DAL statements that do the same. I will say, as I did below, that I don't like your current architecture. You really should separate the persistence part from the game. Your existing class is doing too much, and you're starting to get into the "how can I make it work with my current setup" contortions that are really a bad code smell. Change your setup. – Major Productions Jan 27 '15 at 00:06
  • Yeah I'm considering making the SELECT call solution, but the architecture shown here is simplified and shows a direct "Games" model for a Games table. This is what an ORM does, so how can it be bad architecture? Yes, I do have classes (services...managers?) that take care of many things related to Games and they make calls to other classes, but eventually a class will have to call this Model once they are ready to save. – prograhammer Jan 27 '15 at 00:10
  • I guess I was just looking to see if someone had some sort of `isDirtyField` technique they used that I'm missing here? Something that recognized a property was set or not, so I have the option to not perform an initial SELECT to fill my object. – prograhammer Jan 27 '15 at 00:12
  • I added a suggestion to implement your own dirty checking under an answer below. Also I agree with @KevinM1 on the optimization part. Can you try to add an ORM just for a small part of the project, and see how bad the performances get? – pyb Jan 27 '15 at 00:27
  • @DavidGraham I've already stated why I question your architecture twice - the persistence code (your save method) really doesn't belong there. To put it in more OO design lingo, you seem to be violating the Single Responsibility Principle. Your game object shouldn't be concerned with managing game state and persistence. Refactor that part out. You'll save yourself a lot of headaches down the road. If you were really doing it like an ORM, you'd have an entity manager. *That's* how they handle object persistence. They don't rely on model entities to do it for them. – Major Productions Jan 27 '15 at 00:48

3 Answers3

0

Select your object before updating it. That way, your UPDATE statement will contain all the fields.

Also, this let you check that the id exists.

pyb
  • 4,813
  • 2
  • 27
  • 45
  • So each time I instantiate the object with an ID in the constructor I should peform a SELECT? I'm just uneasy about adding an extra query (queries should not be added liberally!) – prograhammer Jan 26 '15 at 23:27
  • Can people delete games? If so, it might be wise to check if you still have a game before running all your update logic. – pyb Jan 26 '15 at 23:30
  • This makes unique key constraints that I've setup in MySQL to be a wasted effort? If I'm going to perform the constraint check via a SELECT query each time before I INSERT/UPDATE. – prograhammer Jan 26 '15 at 23:32
  • Keep in mind I might be doing INSERTs and UPDATEs together in the form of `ON DUPLICATE KEY UPDATE` – prograhammer Jan 26 '15 at 23:33
  • I don't understand. Unique key constraints are just that: they ensure that each game has a unique value for that column. It has nothing to do with deletions. – pyb Jan 26 '15 at 23:35
  • If it's a game that already has info exists in the db, then of course you should SELECT the relevant info and populate the instance with it. Actually, you really should decouple the code that persists/retrieves a game's info from the game itself. If your game needs to save itself, it should access a game manager to help do that, preferably with a real ORM underneath. Like: `public function save() { $this->manager->save($this); }` – Major Productions Jan 26 '15 at 23:36
  • I'm only centralizing writes, not reads. See another post from me here: http://stackoverflow.com/questions/28052185/if-im-already-using-pdo-then-what-is-the-advantage-to-further-abstracting-selec#comment44487492_28052185 – prograhammer Jan 26 '15 at 23:39
  • In the above, `$manager` would be an instance of a GameManager object that you passed to your Game object's constructor upon initialization. Since objects are passed by reference, you're really only passing that reference in, not the whole object. – Major Productions Jan 26 '15 at 23:39
  • I'm using Laravel, and I'm using as much of the framework DB class as I can to do things...however, I don't make activeRecord(Eloquent ORM) SELECT calls. It's pointless for me, see comment and link above. – prograhammer Jan 26 '15 at 23:40
0

Otherwise, in plain SQL you can do:

UPDATE games SET date = "2015-01-26", anotherField = $game->anotherField() WHERE id = $game->id;

Note: this is pseudo code. Use prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

Reflection doc: http://php.net/manual/en/book.reflection.php

pyb
  • 4,813
  • 2
  • 27
  • 45
  • This means I'll have to write a separate SQL statement for each variation (updating 1 field, updating 2 fields, different combonations, etc), not good. – prograhammer Jan 26 '15 at 23:29
  • I thought you did not want to update your save() method every time you update your view, and the values were retrieved from a form, so you would always get all values. – pyb Jan 26 '15 at 23:33
  • 1
    If you don't want to use an ORM, use black magic: 1) set all fields to a constant named UNSET or PRISTINE 2) let something update your object 3) loop through all the object properties using new ReflectionClass($game)->getProperties(), if the value is not PRISTINE then include it in your UPDATE query. – pyb Jan 26 '15 at 23:46
  • Bob, how can I test if it's set to a constant? Remember, the field could be zero, null, anything. `isset`(on an array element) was the only thing I thought I could use? – prograhammer Jan 27 '15 at 00:37
  • ```isset``` doesn't work? I did not think of it. I was thinking of setting all values to that PRISTINE constant in the default constructor ( ```new Game;``` ) and comparing them later. – pyb Jan 27 '15 at 00:40
  • yeah, but if I say `if ($this->score != PRISTINE)` doesn't this just check if the are values equal? I mean, it's not checking that score is set to a pointer or something is it? If both values happen to be null, then I wouldn't know if score was set to PRISTINE or not? I'm not following this constant solution you are recommending, maybe I'm not understanding constants correctly. – prograhammer Jan 27 '15 at 00:44
  • Set PRISTINE to a dummy object. Be careful when using ```==``` and ```===```, see http://board.phpbuilder.com/showthread.php?10312028-Compare-by-reference – pyb Jan 27 '15 at 00:55
  • right, I know about the ===, but I see now what you are trying to do. Using a dummy object and the reflection class, gotcha!! Put that into an answer and I'll accept that (you can still mention your recommendation on having the ORM perform an initial SELECT). Thanks Bob! – prograhammer Jan 27 '15 at 01:11
0
  1. declare a PRISTINE constant. Try setting it to new instance of an object you've made, say new MyPristine.
  2. In your Game constructor, set all fields to that constant. This is the trick to mark them as " untouched ". You can use Reflection ( like ReflectionClass($game)->getProperties() ) to loop through all of them
  3. Before saving, loop again through the fields and test their value with PRISTINE. If it's different, it needs to be changed.

This solution suppose no one/nothing else will change the Game instances between the steps 2 and 3. That's why most ORMs will do a SELECT beforehand.

pyb
  • 4,813
  • 2
  • 27
  • 45