1

I have two database tables Players and Units. Respectively I also have two classes in PHP that look pretty identically in the base.

class Player {

    private $id;
    private $name;
    // a bunch of other properties

    public function __construct($id){
        // fetch info from database with $id and populate properties
    }

}

 

class Unit {

    private $id;
    private $name;
    // a bunch of other properties

    public function __construct($id){
        // fetch info from database with $id and populate properties
    }

}

But one player can have multiple units, so I implemented the method loadUnits() in the Player class

public function loadUnits(){
    $units = array();
    foreach($db->prepare('SELECT id FROM units WHERE owner = ?')->execute([$this->id])->fetchAll() as $unit){
        $units[] = new Unit($unit['id']);
    }
    return $units;
}

The problem is that constructing Unit X number of times will make X number of calls to the database and this is really something I don't like. I would like to ask what are the good practices and how is this done in reality? Thanks!

Improve this question
6
  • In reality, you use an ORM (personal favorite: Eloquent) that lets you set up the models and relations in a few lines of code, which takes care of optimizing multipel database calls. However, selects are extremely cheap compared to writes. Commented Jul 18, 2014 at 10:23
  • Why don't you optimize the query? i.e. SELECT id FROM units WHERE owner IN ('x', 'y', 'z') Commented Jul 18, 2014 at 10:23
  • This code will make only 1 call per Player, no matter how many the units are. Did you mean X queries for X players? Commented Jul 18, 2014 at 10:24
  • @jon I mean that in the class Unit's constructor there's also a call to the database to retrieve the unit's other stats Commented Jul 18, 2014 at 10:25
  • @LatheesanKanes the query is good, as each unit has an owner and multiple units can have the same owner so the query returns only the units that have the given owner Commented Jul 18, 2014 at 10:26

3 Answers 3

Reset to default
1

Instead of just selecting the id in loadUnits and then issuing another query for the properties per id, I recommend getting all unit properties with a single query and passing those properties to the constructor of Unit

//don't just get the id's, get the actual unit properties
public function loadUnits(){
    $units = array();
    foreach($db->prepare('SELECT <all properties> FROM units WHERE owner = ?')->execute([$this->id])->fetchAll() as $properties){
        $units[] = new Unit($properties['id'],$properties);
    }
    return $units;
}


class Unit {

    private $id;
    private $name;
    // a bunch of other properties

    //make the unit properties an optional parameter and use it
    //instead of querying the db if available
    public function __construct($id,$properties=null){

        if(is_null($properties)) {
            // fetch info from database with $id and populate properties
        }
        else {
            // populate via $properties
        }
    }

}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I have a similar problem in one of my projects. Calling Room->computeDoors() will run a query to find what Doors are attached to the Rooms, then for each of them it has to run a query to find out what the Door's properties are... and then it has to query each room to find out what the door is connected to!

Problem solved: Memcache. Store as much as you can in cache, that way the data's already there to be used no matter how many times you need it, even across pageloads/AJAX calls, or even across users! Just make sure to invalidate or update the cache when you update the object's state.

Improve this answer

7 Comments

And, if you have 5000 units in DB, and a player has 100, you either store 5000 units and cache their info (worse), or store 100 and end up with the problem of the OP. You will still need to fetch the info for each unit.
@RoyalBg Why is storing 5000 units in cache "bad"? The way I see it, that's an extremely good thing. It may be slow for the very first ever load of data, or if the server has just rebooted, but after that it's lightning-fast.
Let's take things to a lower level for a moment. The reason why memcache is fast is because it stores info into the memory, leaving you "only" with the overhead of establishing a connection to it. MySQL on the other hand does the same thing, InnoDB to be exact - it stores working data set into memory, controlled by the variable innodb_buffer_pool_size. If you use persistent connections, you reduce the overhead of establishing a connection and you get fast access to data in the memory, leaving you with a simple system where you just repeat 10, 5000 or whatever number of queries.
Because you might rarely need them. If they has changable statistics (you need to save in DB), then cache is bad at all, because you will need to clear it. If the data does not need to be changed for long term e.g. only for the moment where two players are in battle with these units, then they go again to the original state, you need to info statically in classes and just load and changed it in memory, database might not be needed at all.
Now, by caching into memcache, what you're doing is effectively doubling the memory "waste" since you'll save data into ram which has already been saved there by MySQL. SELECTs are incredibly cheap. If combined with prepared statements, using MySQL to fetch data from RAM becomes 1) simple 2) fast 3) optimal 4) easy to maintain and write the code. Hence, in my personal opinion, the usage of memcache to perform what you described is a waste and probably not an optimization but quite the contrary.
|
1

You can solve this bei either using an ORM like Doctrine or Eloquent.

In Doctrine you define the relations between your entities, it will automatically generate the SQL and tables, it will generate proxies containing findBy() methods, give notes about wrong relations or missing values. Doctrine has implemented different fetching methods and caching for example persistence of entities and lazy loading. You define your model and Doctrine takes care of everything else the most stable and fastest way.

If you want to implement your own, you should cache the results locally in the instance.

private $units;

protected function loadUnits(){
    $units = array();
    foreach($db->prepare('SELECT id FROM units WHERE owner = ?')->execute([$this->id])->fetchAll() as $unit){
        $units[] = new Unit($unit['id']);
    }
    $this->setUnits($units);
    return $this;
}

public function setUnits($units) {
    assert(is_array($units));
    $this->units = $units;
    return $this;
}

public function getUnits() {
    // this if needs improvement to fit your needs
    if (!is_array($this->units)) {
        $this->loadUnits();
    }

    return $this->units;
}
Improve this answer

9 Comments

"You can solve this by either using an ORM like Doctrine or Eloquent." -- How? To someone who has never heard of these, how are they helpful?
But I don't see how this will help the issue. If the player has for example 10 units, 10 queries are still going to be made by constructing Unit 10 times :?
@NiettheDarkAbsol: To be fair, your suggestion of using memcache has exactly the same problem.
@Jon To some extent, yes, but at the same time no. I explained how Memcache would help solve the problem, rather than just naming... things.
@php_nub_qq there is only 1 query per construct... you fetch 10 units per player in one query.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.