title: PHP PDO Multiple Result sets in one query or multiple separate queries
body: <p>I am just finishing a simple PHP Framework for a University project and I am trying to create a simple Data Access Object per entity which encapsulates all Database interaction and I am not going to use Doctrine or another tool for this, I need to do it myself, It needs to be simple but robust, I will try keep my question as clear as possible.</p>
  
  <p>I want to create one DAO class per entity so it acts as a bridge between a table and an entity.
  I am learning a bit from Doctrine but cant find enough information.</p>
  
  <p>Since I have a Dao class per entity obviously all DB logic will be in the Dao, the entity will have pure domain logic, Each Dao is responsible for creating entities and all read/write operations to data layer</p>
  
  <p>My main question is what is the best and most efficient way to load entity associations especially if I wish to load the FULL ASSOCIATION?,</p>
  
  <p>I am trying to understand how this lazy and eager loading works under the hood for entity associations, (not partial columns)</p>
  
  <p>If I just want a column I could use a join but If I want the whole entity the only way I see this being possible is to either make another query to the database or use multiple queries in one execute statement with rowset to fetch the associated entity.</p>
  
  <p>What if I have many associations, what if the database is on a different machine? I want to keep it as flexible as possible but I do not want unnecessary complexity,
  Are there any significant differences in performance?</p>
  
  <p>Especially as if I am getting multiple entities from different tables, If I have to issue multiple queries, is there any real difference between lazy and eager loading? As there are more or less the same queries being made,</p>
  
  <p>I will use the simple example of an Article and Comments 1:many relationship as an example:</p>
  
  <pre><code>//Article Entity example, Assume each property maps to a column in a database
  class Article extends Entity
  {
      private $id;
      private $title;
      private $body;
      private $comments = [];
  
      //Getters/Setters:
      public function get_comments()
      {
          return $this-&gt;comments;
      }
  }
  
  class Comment extends Entity
  {
      private $id;
      private $title;
      private $comment;
      private $user_name;
      private $created_at;
      private $updated_at;
      private $edited = false;
      //
  }
  
  //dao class example:
  class ArticleDao extends SQLDao
  {
      private $one_to_many = [
         'comments' =&gt; [
              //metadata here
          ],
      ];
  }
  </code></pre>
  
  <p>Now the comment entity / table has have Different columns/attributes so if I want the whole thing its a different set so a join is not possible I guess?</p>
  
  <p>suppose I want to get a bunch of articles or one article and I want their associated comments which are a simple array/collection of comment entities, what is the most efficient way to go about this?</p>
  
  <p>I feel the only options I have are:</p>
  
  <p>1) Create a multi query multiple result set where I fetch everything at once,
  I could use my query builder and the Dao class can have a neat wrapper around it,
  Something like:</p>
  
  <pre><code>$article_dao = new Dao();
  $article_dao-&gt;where('id','=',$id)-&gt;with('comments')-&gt;get();
  //Creates two queries:
  SELECT * FROM Articles WHERE id = :id,
  SELECT * FROM Comments WHERE Comments.article_id = id,
  //use nextrowset:
  //Populate entities and initialise objects:
  </code></pre>
  
  <p>Option 2: Add the dao object and a load method to the comments variable and create some kind of callback which calls the method the first time the comments property is accessed,
  The code below is mainly just a rough version:</p>
  
  <pre><code>//Base Dao Class method:
  public function load_relation($relation, $id):array
  {
      //Checks if relation exists and is defined
      if(!$this-&gt;has_relation($relation))
      {
          return false;
      }
  
      /*Initialises the Comment Dao class
      and calls the method and passes parent id */
      return $this-&gt;get_relation($relation)['dao']-&gt;get($id);
  
  }
  
  //Base entity class load method:
  public function load($prop)
  { 
      Check if the property in the entity has a dao object
      if(is_a($this-&gt;$prop[0], IDao::class))
      {
           //Call that method which makes a query to the database
           $entities = call_user_func($prop);
      }
      //Now the array has been populated
      $this-&gt;$prop = $entities;
      //reuturn
      return $this-&gt;$prop;
  }
  
  //Now in the article entity, I could use this:
  public function get_comments()
  {
      return $this-&gt;load('comments');
  }
  </code></pre>
  
  <p>Or is there a better option?</p>
  
  <p>Is there a major performance advantage to use multiple queries with nextrowset instead of just a clean new query?
  The latter seems a lot simpler and my code will be cleaner</p>
  
  <p>I have already created a base query builder library I will use with the dao</p>
  
  <p>Maybe I have missed the whole point, I do not know how these ORM's work but I wanted to create a good platform</p>
  
  <p>any advice would be appreciated, im sorry if my question sounds complex, happy to elaborate/simplify as best I can if needed</p>