Creating nested relationships with an ORM and minimizing queries

Edit 3

After reading a boat load I really don't think with any ORM or system in general it is possible to build the relationships of organized objects like I want in fewer queries that I am using. If any can provide an example of it being possible I would kiss you.

In Edit 2 The nested for loops I think is the best solution running

Total_queries = 1 + 2(Slides_in_project) + Shapes_in_project
                |                |                        \
  Query to get project           |                         \
                                 |                          \
        Query to get slides and double because of points     \
                                                              \
                                              Get all the shapes in the project

I would love a better example because to populate simple projects I would probably be running 200-500 queries. This is bad.

Edit 2

Well I have been playing with this for a while now and I have some results but I do not think they are the "RIGHT" way and that matters to me a lot. What I do is I use the where_related method to get the right objects but I think my query count is still pretty high and I know an ORM can do better. When I use the where related to create the proper hierarchy I have to use nested foreach loops and I don't like that. That means useless querying.

Here is the solution I came up with

function get_project_points($link_id)
{
    echo "<pre>";
    foreach($this->where('link_id', $link_id)->get()->slide->where_related('project', 'id', $this)->get() as $slide){
        echo $slide->id."<br>";
        foreach($slide->shape->where_related('slide', 'id', $slide->id)->get() as $shape){
            echo "\t".$shape->id."<br>";
            foreach ($shape->point->where_related('shape', 'id', $shape->id)->get() as $point) {
                echo "\t\t".$point->id."<br>";
            }
        }
    }
}

This outputs a nice tiered structure and as you can see it would be easy to replace the echos with object/array population.

What I would rather have though is one chained command that did the same thing if possible that way scoping isn't an issue either.

Some chain more resembling

$this->where('link_id', $link_id)->get()
    ->slide->where_related('project', 'id', $this)->get()
    ->shape->where_related('slide', 'id', $slide->id)->get()
    ->point->where_related('shape', 'id', $shape->id)->get()

That of course does not achieve anywhere near the same results as the nested foreach loops but what I would like to know is it possible to chain relationships and populate objects without nested foreach

So I just did some profiling and the nested foreach loops generate 63 queries on a small project, and take almost half a second to generate results. This is really too slow. There must be a better query.

__________Edit 1

All the below information is great but I have been playing with it and I cannot seem to get any relationship to work let alone a 3 tiered one. I have tried just about everything I can think of and read the doc's but for somereason my brain doesn't like the ORM.

I would like to just echo the id's of all slides in a project. I will give a list of what I have tried with no avail. My model structure is the same as below I am just adding methodes.

class Project extends DataMapper {
    var $has_many = array("slide");

    function get_project_slides($link_id)
    {
        $this->where('link_id', $link_id)
            ->where_related('slides', 'project_id' 
                $this->where('link_id', $link_id)->get()->id
            )
        ->get();
    }

}

And I have tried what I would think is the logical opposite in the Slide Method.

What am I doing wrong... How do you structure ORM relationships?

---

Original Question

I am using an ORM for the first time and I am having huge problems visualizing how to structure the code to pull data from the relationships.

I am using DataMapper as my ORM with CodeIgniter. I have the installation working just fine and I read all the docs, I just cannot get my head around how to get information in controllers

+-----------+    +------------+    +---------+    +----------+
|  projects |    | slides     |    | shapes  |    |  points  |
+-----------+    +------------+    +---------+    +----------+
|    id     |    |  id        |    | id      |    | id       |
+-----------+    | project_id |    |slide_id |    | shape_id |
                 +------------+    +---------+    | x        |
                                                  | y        |
                                                  +-----开发者_运维技巧-----+

Models -

project.php

class Project extends DataMapper {
    var $has_many = array("slide");
}

//  End of project.php
//  Location: ./application/models/project.php 

slide.php

<?php

class Slide extends DataMapper {
    var $has_many = array("shape");
    var $has_one = array("project");
}

//  End of slide.php
//  Location: ./application/models/slide.php 

shape.php

<?php

class Shape extends DataMapper {
    var $has_many = array("point");
    var $has_one = array("slide");
}

//  End of shape.php
//  Location: ./application/models/shape.php 

point.php

<?php

class Point extends DataMapper {
    var $has_one = array("shape");
}

//  End of point.php
//  Location: ./application/models/point.php 

The above should create a decending one->many relationship between projects->slides->shapes->points

How do you start to deal with information? When I was not using an ORM I handled all data processing in the Model is this incorrect for the ORM Models? Say you wanted to get all the points in all the shapes in project 1 how would you go about structuring such a call?

I don't really need specific code if you want that would be helpful. What I really need is some ideas on how to visualize how to tier the objects so you can deal with each at any tier.

---

First of all, I'm sorry to break this to you , but CodeIgniter's DataMapper is actually a variation of ActiveRecord pattern.

If you care, you compare the real DataMapper pattern with its counterpart - ActiveRecord. In short - difference is in fact that in DM pattern you Domain Object is unaware of type ( and even the existence ) of storage. It is used in manner like $mapper->store( $user );.

"Favor object composition over class inheritance." © GoF

That said..

---

If i read the examples right then it should work like this: ( i am assuming that relationships between 'entities' are already established )

class Project extends DataMapper
{
   // --- the rest of your stuff 

   public function get_all_points()
   {
      $points = array();

      $slides = $this->slide->get()->all;


      foreach ( $slides as $slide )
      {
         $shapes = $slide->shape->get()->all;

         foreach ( $shapes as $shape )
         {
            $points = array_merge( $point = $shape->point->get();
         }
      }

      return $points;

   }


}

Then you can use something like

$project = new Project;
$all_points = $project->where( 'id' , 1 )->get_all_points();

foreach ( $all_points as $point )
{
   $point->x = 0;
   $point->save();
}

This should gather all th points that are related to project with ID 1, and set the X value to 0 and store each in the database .. not that any sane person would do it.

---

I am not using any sort of ORM, that's why i really hope i got this wrong, because this looks to me like an abomination.

---

I'm not sure how Datamapper does it but I have a custom GenericObject model for Codeigniter that does ORM like this:

class Article extends GenericObject
{
    public $relationships = array ( "has_many" => array ("comments"), "has_one" => array ("users") );
}
class Comments extends GenericObject
{
    public $relationships = array ( "belongs_to" => array ("articles", "users"), "has_one" => array ("users") );
}
class Users extends GenericObject
{
    public $relationships = array ( "has_many" => array ("comments", "articles") );
}

If I want to get everything from a User then I can just do something like:

$User = new User( $some_user_id );
$User->boot_relations("all");

foreach ($User->Comments as $Comment)
{
    echo $Comment->title."<br />";
    echo $Comment->body."<br />";
    echo "written by ".$User->username;
}

So it can be fairly elegant (or at least I like to think so).

---

With most relational data, I typically lazy-load objects at they are needed. I'm not a PHP developer, but here's what I would do in pseudo-code.

class Projects {
    var slides = null;
    function getSlides() {
        if(slides == null) {
            slides = getSlidesForProject(this.id);
        }
        return slides;
    }
}

class Slides {
    var shapes = null;
    function getShapes() {
        if(shapes == null) {
            shapes = getShapesForSlide(this.id);
        }
        return slides;
    }
}

class Shapes {
    //... same as Projects.getSlides and Slides.getShapes
}

Unfortunately, this causes multiple calls to the database if you needed to get all Points for a Project.

With any MVC solution, I recommend going with a light Controller and a heavy Model to make code reuse and testing easier.

---

First off : I don't know anything about CI's own ORM implementation, but when I see what you are doing, either it's missing some functionality or you're using it in the wrong way (from your edit #2).

In Propel however (just mentioning this because that's what I use most if the time, Doctrine is another good alternative) these things are easily done, especially in the new 1.6 branch, using fluent interfaces. Just check out the docs on Relationships. Doesn't that look like something you'd want to use? :p

---

From what I understand, you want to retrieve one project with all the associated points, given the constraints between each table.

Check this sqlFiddle: http://sqlfiddle.com/#!2/9ed46/2

Schema cration:

CREATE TABLE project
    (
     id int auto_increment primary key, 
     name varchar(20)
    );

CREATE TABLE slide
    (
     id int auto_increment primary key, 
     name varchar(20),
     project_id int
    );

CREATE TABLE shape
    (
     id int auto_increment primary key, 
     name varchar(20),
     slide_id int
    );


CREATE TABLE point
    (
     id int auto_increment primary key, 
     name varchar(20),
     shape_id int
    );

Request:

select project.id as project_id, project.name as project_name,
       slide.id as   slide_id,   slide.name as   slide_name,
       shape.id as   shape_id,   shape.name as   shape_name,
       point.id as   point_id,   point.name as   point_name

from project

left join slide on slide.project_id = project.id
left join shape on shape.slide_id   = slide.id
left join point on point.shape_id   = shape.id

where project.id = 1

It returns something like this:

PROJECT_ID PROJECT_NAME SLIDE_ID SLIDE_NAME SHAPE_ID SHAPE_NAME POINT_ID POINT_NAME
1          fst_project  1       fst_slide   1        fst_shape  1        first_pt
1          fst_project  1       fst_slide   1        fst_shape  2        2nd_pt
...

By processing this output, you could construct an object tree that is like you want, everything in 1 query. But this post-processing may take some time. You would have to loop through each point.

---

Have a look at Granada.

From the readme:

class User extends Model {
         public static $_table = 'user';

         public function post(){
               return $this->has_many('Post');
         }
         public function avatar(){
               return $this->has_one('Avatar');
         }
   }

You can include relationships inside your relationships !

$user_list = Model::factory('User')->with(array('post'=>array('with'=>'comments')),'avatar')->find_many();

It will make 3 querys:

SELECT * FROM user 
SELECT * FROM avatar WHERE user.id IN (......)
SELECT * FROM post WHERE user.id IN (.....)