Geek asks and answers

Creating nested relationships with ORM and minimizing queries

Edit 3

After reading the load on the boat, I really do not think that with the ORM or the system in general, it is possible to build relationships of organized objects, as I want, in the fewer queries that I use. If someone can serve as an example that this is possible, I would kiss you.

In Edit 2 Nested for loops, I think the best solution works

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 like the best example, because to complete simple projects I would probably execute 200-500 queries. This is bad.

Edit 2

, - , , , "", . , where_related , , , , ORM . , , foreach, . .

,

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->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()

, , , foreach, , , - foreach

, , foreach 63 , . . .

__________ 1

, , , . , , , - ORM.

slides project. , , . , . .

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();
    }

}

, , .

... ORM?

ORM , , .

DataMapper ORM CodeIgniter. , , , , .

+-----------+    +------------+    +---------+    +----------+
|  projects |    | slides     |    | shapes  |    |  points  |
+-----------+    +------------+    +---------+    +----------+
|    id     |    |  id        |    | id      |    | id       |
+-----------+    | project_id |    |slide_id |    | shape_id |
                 +------------+    +---------+    | x        |
                                                  | y        |
                                                  +----------+

-

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

β†’ β†’ β†’

? ORM, , ORM-? , 1, ?

, , . , , , .

+3
6

, , , CodeIgniter DataMapper ActiveRecord.

, DataMapper - ActiveRecord. , , DM ( ) . , $mapper->store( $user );.

" ." & copy; GoF

..

, : ( , "" )

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;

   }


}

-

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

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

, 1, X 0 .. , .


ORM, , , .

0

, Datamapper, GenericObject Codeigniter, ORM :

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") );
}

, - :

$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;
}

, (, , ).

0

, , , . PHP, .

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
}

, , .

MVC , .

0

-: ORI CI, , , - , ( # 2).

Propel ( , , , Doctrine - ), , 1.6, . docs on Relationships. , ?:

0

, , , .

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

Circuit Lock:

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
    );

Inquiry:

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
...

When processing this output, you can build a tree of objects, which, as you want, is all in 1 query. But this post processing may take some time. You will need to go through each point.

0
source

Take a look at Granada .

From the readme file:

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 within your relationships!

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

He will execute 3 queries:

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

More articles:


All Articles