我正在学习如何把我的头围绕着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,因为您的域模型变得更加复杂.