php – 将执行以下操作的Select语句

我正在学习如何把我的头围绕着sql和PHP.我有4个表结构如下

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

正如你所看到的那样,表格通过id一直链接到点,这意味着一个项目将包含一些幻灯片,其中包含许多包含多个点的形状.

我有一个SQL查询

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`

我想要的是将这个查询的结果看起来像这样

[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

我想要的是将这个荒谬的数组转换成更类似的东西

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

我不能为我的生活找出如何将它转换成这样一个数组.

如果无法完成查询,我设计的是一个更好的查询.也许一个抓住点,然后把它放在一个数组中的点…我想我只是有一个想法…

新信息,

所以我添加了一个答案这个问题,我不知道这是否是标准的方式.为了帮助其他答案,如果我的不是一个很好的解决方案,我也会添加我的思想过程在这里.

查看我的答案,了解更多信息.

还有一个ORM与我的算法比较如何？

使用像 Doctrine这样的ORM,你可以简单地对它进行建模

/**
 * @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;
}

等等…

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

当然,有相当多的设置和处理开销涉及到,但是您会欣赏ORM,因为您的域模型变得更加复杂.

php – 将执行以下操作的Select语句

猜你在找的PHP相关文章