A Select Statement that would do the following

Question

I am just learning how to wrap my head around sql and php. I have 4 tables structured as follows

+-----------+    +------------+    +---------+    +----------+
|  Project  |    | Slide      |    | Shape   |    |  Points  |
+-----------+    +------------+    +---------+    +----------+
|    id     |    |  id        |    | id      |    | id       |
+-----------+    | project_id |    | cont_id |    | shape_id |
                 +------------+    +---------+    | x        |
                                                  | y        |
                                                  +----------+

As you can see the tables are linked by id all the way down to points meaning a project will contain a number of slides that contain a number of shapes that contain a number of points.

I have a SQL query

SELECT slide.`id`, shape.`id`, points.`x_point`, points.`y_point` 
FROM `project`, `slide`, `shape`, `points` 
WHERE 1 = slide.`project_id` 
   AND slide.`id` = shape.`slide_id` 
   AND shape.`id` = points.`shape_id`

What I want is to take the results of this query that look like this

[0] => stdClass Object
     (
         [id] => 27
         [x] => 177
         [y] => 177
     )

 [1] => stdClass Object
     (
         [id] => 27
         [x] => 178
         [y] => 423
     )

 [2] => stdClass Object
     (
         [id] => 27
         [x] => 178
         [y] => 419
     )

 [3] => stdClass Object
     (
         [id] => 27
         [x] => 178
         [y] => 413
     )

 [4] => stdClass Object
     (
         [id] => 27
         [x] => 181
         [y] => 399
     )

 [5] => stdClass Object
     (
         [id] => 27
         [x] => 195
         [y] => 387
     )

 [6] => stdClass Object
     (
         [id] => 27
         [x] => 210
         [y] => 381
     )

 [7] => stdClass Object
     (
         [id] => 27
         [x] => 231
         [y] => 372
     )

 [8] => stdClass Object
     (
         [id] => 27
         [x] => 255
         [y] => 368
     )

 [9] => stdClass Object
     (
         [id] => 27
         [x] => 283
         [y] => 368
     )
... AND CONTINUED FOR A LONG TIME

What I want is to convert this beastly array of crap into something that more resembles this

[9] => stdClass Object
         (
             [id] => ID OF LIKE SHAPES
             [x] => Array(ALL THE X POINTS)
             [y] => ARRAY(ALL THE Y Points)
         )

I cannot for the life of me figure out how to convert this to such an array.

If it cannot be done with the query I designed is there a better query. Maybe one that grabs the points then takes that puts it into an array that of the points... I think I just got an Idea...

---

New Info,

So I added an answer to this question, I don't know if that's the standard way. To help out other answers if mine is not a good solution I will add my thought process here as well.

Check out my answer bellow for more info.

Also how does an ORM compare to my algorithm bellow?

Answer 1

Using an ORM like Doctrine, you would simply model it like

/**
 * @Entity
 */
class Project
{
    /**
     * @Id @GeneratedValue
     * @Column(type="integer")
     */
    private $id;

    /**
     * @OneToMany(targetEntity="Slide", mappedBy="project")
     */
    private $slides;

    public function __construct()
    {
        $this->slides = new \Doctrine\Common\Collections\ArrayCollection;
    }
}

/**
 * @Entity
 */
class Slide
{
    /**
     * @Id @GeneratedValue
     * @Column(type="integer")
     */
    private $id;

    /**
     * @ManyToOne(targetEntity="Project", inversedBy="slides")
     * @JoinColumn(name="project_id", referencedColumnName="id")
     */
    private $project;

    /**
     * @OneToMany(targetEntity="Shape", mappedBy="slide")
     */
    private $shapes;
}

And so on...

See http://www.doctrine-project.org/docs/orm/2.0/en/reference/association-mapping.html#one-to-many-bidirectional

Of course, there's a fair amount of setup and processing overhead involved but you'll appreciate an ORM as your domain model becomes more complex.

Answer 2

So I have been working on this a while and I came up with my own answer. I would love input because I think it is probably the BAD way to do this.

Here is my thought process. One Query is great but what if we build the results array incrementally. What I mean is we can build the results array by traversing through the tables with designed SELECT statements.

Here is the code I comment it because I am having a hard time describing my algorithm in just words.

/*  $cur_project is set above from an input value. Assume any int
    The algoritim Traverses a series of results and puts them into the proper places in a usable array.
    The algorithim has an query count of NumberOfSlides + 2(NumberOfSlides)+1 which seems really high
    For real word application if querying the DB is as bad as everyone says.
*/
// A blank array to build up
$projectArray = Array();

// I just want to see how many queries this thing generates
$queryCount = 0;

// Query 1 - This query will get all slides in a project.
$slide_id = $this->db->query('SELECT slide.`id` 
                                FROM `slide`
                                WHERE slide.`project_id` = '.$cur_project);
$queryCount++;

//Now traverse the results to Query 1
foreach ($slide_id->result() as $slide_id){
    // In the project array add an element with the key that is
    // the slide_id for all slides in that project.  Then for each
    // key also create a new empty array at each added element
    $projectArray[$slide_id->id] = Array();

    // Query 2 - grab all the shapes that match the current slide in the current project!
    // This is where things get inefficient.
    $shape_id = $this->db->query('SELECT shape.`id`
                                    FROM `shape`
                                    WHERE shape.`slide_id` = '.$slide_id->id
                                );
    $queryCount++;

    // Traverse the results to Query 2
    foreach ($shape_id->result() as $shape_id) {
        // For every slide now create a key that matches the shape and fill that array with 
        // info I need such as an array of the points.
        $projectArray[$slide_id->id][$shape_id->id] = Array(
                                            'x_points' => Array(), 
                                            'y_points' => Array()
                                        );
        // Query 3 - Ask the DB for x/y points for the current shape. You can see how for slides with lots of shapes
        $points = $this->db->query('SELECT points.`x_point`, points.`y_point` 
                                    FROM `points` 
                                    WHERE points.`shape_id` = '.$shape_id->id
                                );
        $queryCount++;
        // Traverse the Query 3 results
        foreach ($points->result() as $point) {
            // Populate the final arrays with the points
            $projectArray[$slide_id->id][$shape_id->id]['x_points'][] = $point->x_point;
            $projectArray[$slide_id->id][$shape_id->id]['y_points'][] = $point->y_point;

        }
    }
}

The above returns an array that looks like so

Array
(
    [1] => Array
        (
            [27] => Array
                (
                    [x_points] => Array
                        (
                            [0] => 177
                            [1] => 178
                            [2] => 178
                            [3] => 178
                            [4] => 181
...

Which can be interpreted as

Array
(
    [SLIDE_ID] => Array
        (
            [SHAPE_ID] => Array
                (
                    [x_points] => Array
                        (
                            [0] => 177
                            [1] => 178
                            [2] => 178
                            [3] => 178
                            [4] => 181
...

My problem with this solution is what I say in my top comment. I think you could duplicate these results with an array search for the original results as listed in the answer. That seems worse though.

Please for the life of me tell me how to improve this any comments on it will help me.

Thanks a lot.