PHP/MySql - Optimal way to querying an object with nested objects

Question

I struggle with this question every time I make a new OOP app. Assuming this will grow to be a mid to large scale site that will keep growing, what is the proper way to implement selecting objects nested in another object (in this case what's the best way to implement querying for matchup info).

Let's say I have this:

Class SportsTeam {
   protected $teamId;
   protected $teamName;
   protected $teamLocation;
}

Class Matchup {
   protected $matchupId;
   protected $date;
   protected $time;
   protected $homeTeamId;
   protected $awayTeamId;
   protected $homeTeam; //SportsTeam object
   protected $awayTeam; //SportsTeam object
}

Class SportsTeamDao {
    public function getSportsTeam($teamId) { //Get SportTeam info }
}

Class MatchupDao {
    public function getMatchup($matchupId) { //Get Matchup info }
}

OPTION 1: Select the matchup object itself and within the matchup object have a call in the get method of homeTeam object and a call in the awayTeam object to get the entire SportsTeam object as necessary so that if I add properties to the SportsTeam object later on (e.g. teamLogo) they are accessible to where I use the matchup object.

Class Matchup {
   protected $matchupId;
   protected $date;
   protected $time;
   protected $homeTeamId;
   protected $awayTeamId;
   protected $homeTeam; //SportsTeam object
   protected $awayTeam; //SportsTeam object

   //Getters and setters for matchupId, date, time, homeTeamId, awayTeamId
   public function getHomeTeam() {
      $this->homeTeam = SportsTeamDao::getSportsTeam($this->homeTeamId);
      return $homeTeam;
   }

   public function getAwayTeam() {
      $this->awayTeam = SportsTeamDao::getSportsTeam($this->awayTeamId);
      return $awayTeam;
   }
}

Class SportsTeamDao {
    public function getSportsTeam($teamId) { //Get SportTeam info }
}

Class MatchupDao {
    public function getMatchup($matchupId) {
        $query = "SELECT matchupId, date, time, hometeamid, awayteamid WHERE matchupId = $matchupId LIMIT 1"; //I'd parameterize it of course
    }
}

OPTION 2: Select all the details possibly needed for matchup in a join so I only go to the database once. Drawback being if I add properties to SportsTeam later I'd have to remember to add it to my select statements for SportsTeam and Matchup and any other place that needs to use these new properties.

Class SportsTeam {
   protected $teamId;
   protected $teamName;
   protected $teamLocation;
}

Class Matchup {
   protected $matchupId;
   protected $date;
   protected $time;
   protected $homeTeamId;
   protected $awayTeamId;
   protected $homeTeam; //SportsTeam object
   protected $awayTeam; //SportsTeam object
}

Class SportsTeamDao {
    public function getSportsTeam($teamId) { //Get SportTeam info }
}

Class MatchupDao {
    public function getMatchup($matchupId) {
        $query = "SELECT M.matchupid, M.date, M.time, M.homeTeamId, M.awayTeamId, HT.teamName AS homeN, HT.teamLocation AS homeL, AT.teamName AS awayN, AT.teamLocation AS awayL FROM matchups M LEFT JOIN sportsteam HT ON M.hometeamid = HT.teamid LEFT JOIN sportsteam AT ON M.awayteamid = AT.teamid WHERE matchupid = $matchupid LIMIT 1";
    //Set matchup properties and hometeam object properties and awayteam object properties
    }
}

My dilemma is option 1 is more DB intensive (think if I want to display a list of football players on a team, it's 1 call for team + 1 call to get all Id's of players on the team + 53 more calls one for each player object) but easier to manage, option 2 is less DB connections (1 call for team, 1 call for all players) but more tedious to maintain. So what's the proper way to implement this or is there a third better way?

If you use prepared statement and reuse the SELECT statement, I think option 1 is acceptable. — Passerby
– Passerby, Commented Apr 23, 2015 at 3:03
Thanks for the reply. Here's a follow up to that, if I use a prepared statement within my getSportsTeam() function, but the function is called multiple times in a row, does that still take advantage of the prepared statement? Doesn't it prepare it again every time the function is called? — Felix
– Felix, Commented Apr 23, 2015 at 3:14
You'll need to cache the prepared statement variable, and reuse that variable. I usually do that by building a custom DB class that handles the caching. — Passerby
– Passerby, Commented Apr 23, 2015 at 3:17
Interesting idea, can you give me an example? Like is this a parent class with a variable for each prepared statement or something more complex? — Felix
– Felix, Commented Apr 23, 2015 at 3:21

Passerby · Accepted Answer · 2015-04-23 03:43:47Z

1

This is too long for a comment, so here comes an answer.

Option 1 is "structurally" better. Your main concern is heavy DB connection, which can be optimized at some degree if you use prepared statement and reusing the SELECT statement.

This is what I generally do to ensure the statement reusing (just a demonstration; there might be more logic in real code):

class DB
{
    protected $statements=[];
    public function prepare($query)
    {
        if(empty($this->statements[$query]))
        {
            $this->statements[$query]=$mysqli->prepare($query);
        }
        return $this->statements[$query];
    }
}

So whenever you prepares a query, the DB instance checks if the query has been prepared before, and if it does, return that statement instead of re-prepare again.

Next step would be to make your classes depend on your DB class. You can do this by using Singleton (a static DB "variable" under the global namespace):

class Singleton
{
    protected static $db;
    public static function getDB()
    {
        if(empty(self::$db))
        {
            self::$db=new DB(/*...*/);
        }
        return self::$db;
    }
}

or something like this:

class Car
{
    public function __construct(DB $db);
}

It's up to you to choose a proper way that fits your need.

answered Apr 23, 2015 at 3:43

Passerby

10.1k2 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

denied Over a year ago

Does it correct to make one class depend on another by using singleton? You can can make explicit dependency by passing DB object in the constructor of first class.

Passerby Over a year ago

@denied I never said to use dependency with singleton. Did you misread the "or" phase?

denied Over a year ago

Woops, I've wanted to start a holy war so much, that I haven't even read your answer to the end :) Sorry )

Collectives™ on Stack Overflow

PHP/MySql - Optimal way to querying an object with nested objects

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related