如何从mysql创建具有关联标记对象的帖子列表 [英] How to create a list of posts with associated tag objects from mysql
问题描述
概念性问题
如何处理从关系型MySQL数据库中创建PHP嵌套对象列表？
背景
我目前正在PHP / MySQL中创建自己的博客从头开始。到目前为止，我已经实现了MVC类型的框架，路由和错误处理（一切正常）。缺少的主要功能之一是标签。我遇到的问题是无法通过高效从数据库创建Post对象列表的过程进行思考，其中每个Post对象都有一个关联的Tag对象列表。
为了创建博客索引页面，我必须有一个博客Post对象列表，每个对象都有自己的Tag对象列表。虽然为单个Post对象检索标签很容易理解，但我不想在列出所有帖子时为每个帖子运行单独的查询（索引页面）。
注意
我非常清楚不建议重新发明轮子。但是，我这样做仅用于信息/学习目的！另外，这意味着我非常希望在可能的情况下不使用任何框架来实现这一目标，并且我打算继续使用MySQL。无论如何，感谢您考虑提出建议！
课程描述
这是一个非常基本的课程描述显示帖子和标签之间的相关性（标签是嵌套的）。
php
Conceptual Problem
How do you deal with creating a list of nested objects in PHP from a relational MySQL database?
Background
I am currently in the process of creating my own blog from scratch in PHP/MySQL. So far I've implemented an MVC-type framework, routing, and error handling (everything works fine). One of the main features missing yet is tags. The problem I am running into is not being able to think through the process of efficiently creating a list of Post objects from the database, where each Post object has a list of associated Tag objects.
In order to create the blog index page I must have a list of blog Post objects, each with their own list of Tag objects. While retreiving the tags for a single Post object is simple to understand, I'd rather not run a separate query for each Post when listing them all (index page).
Note
I am extremely aware that reinventing the wheel is not recommended. However, I am doing this for informational/learning purposes only! Additionally, this means that I would highly prefer not using any frameworks if possible to accomplish this, and am planning on staying with MySQL. Thank you for thinking of suggesting otherwise anyway!
Class Description
Here is a very basic class description to show the correlation between Posts and Tags (tags are nested).
php
Post {
public $id;
public $title;
//Array of Tag objects
public $tags = array();
}
Tag {
public $id;
public $name;
public $description;
}
//Representation of target object hierarchy
posts: [{
id: 1,
title: "Post 1",
tags: [{
id: 23,
name: "Tag 23",
description: "Tag 23 Description"
}, {
id: 6,
name: "Tag 6",
description: "Tag 6 Description"
}]
}, {
id: 2,
title: "Post 3",
tags: []
},
...
]
+----+-----------+
| post |
+----+-----------+
| id | title |
+----+-----------+
+----+---------------------+
| tag |
+----+-------+-------------+
| id | name | description |
+----+-------+-------------+
-- Association (membership) table for posts to tags
+----+-----------------+
| post_tag |
+---------+------------+
| post_id | tag_id |
+---------+------------+
想法
让'n`等于必要的查询数量
让't`等于帖子的标签数量
1查询密集型（`n = 1 + t`）
- 查询所有帖子的列表，然后为每条记录发送与该帖子相关的所有标签的另一个查询
2.服务器密集型（`n = 2`）
- 查询所有帖子的列表和所有标签/关联的列表
- 在帖子上循环并嵌套另一个循环标签记录以添加所有相关标签
3.服务器密集型（`1`）
- 查询加入标签的所有帖子列表（按帖子ID排序） ）
- 循环记录并为当前帖子创建标签，添加帖子并在帖子ID更改时开始新帖子
我的尝试：
Ideas
Let `n` equal the number of queries necessary
Let `t` equal the number of tags a post has
1. Query Intensive (`n = 1 + t`)
- Query a list of all posts and then for each record send another query for all tags associated with that post
2. Server Intensive (`n = 2`)
- Query a list of all posts and a list of all tags/associations
- Loop over the posts and nest another loop against the tag records to add all associated tags
3. Server Intensive (`1`)
- Query a list of all posts joined to tags (ordered by post id)
- Loop over the records and create tags for the current post, adding the post and starting a new one when the post id changes
What I have tried:
// Idea 1 (horrendous in my eyes) - NOTE: Fake code (untested) to give an general idea
// One initial query, than a lot later on depending on the number of posts * tags per post
$postRecordSet = $conn->query("SELECT * FROM post ORDER BY id");
$posts = array();
//Create list of posts
while ($row = $postRecordSet->fetch_assoc())
$id = $row["id"];
//Select all tags for each post
$tagResultSet = $conn->query("SELECT * FROM posts_tags AS pt INNER JOIN tags AS t ON pt.tag_id = t.id WHERE pt.post_id = $id");
$tags = array();
//Create list of Tags
while ($row = $tagResultSet->fetch_assoc()) {
$tags[] = new Tag($row["id"], $row["name"]);
}
//Create post object from recordset (with list of tags) and add to list of posts
$post = new Post($row["id"], $row["title"], $tags);
$posts[] = $post;
}
显然，这种方式效率极低（由于可能的数量）查询）但这是最容易理解的。但是，我不确定要追求哪些其他选择，或者它们可以合并到什么程度。请让我知道接近这个的好方法。谢谢！
免责声明
我已经花了很多时间查看多个类似问题，但没有人回答我的问题非常好（至少根据我的理解）。他们中的大多数只处理从MySQL返回嵌套数据，这在某种程度上有所帮助，但最终没有太大帮助，因为我也非常倾向于找出如何创建帖子列表（带有嵌套的标签列表）。任何帮助或想法将不胜感激！
Obviously, this way is an extremely inefficient way (due to possible amount of queries) but it is the easiest to understand. However, I'm not sure which other options to pursue, or to what degree they could be combined. Please let me know good ways of approaching this. Thanks!
Disclaimer
I have already spent time looking at multiple "similar" questions and none have answered my question very well (at least to my understanding). Most of them deal with merely returning nested data from MySQL, which is helpful to some degree but ultimately not too helpful, as I am also heavily leaning on figuring out how to create the list of posts (with nested lists of tags) afterwards. Any help or ideas would be greatly appreciated!
推荐答案
id ;
public
id; public
title ;
// 标记对象数组
public
title; //Array of Tag objects public
标签 = array（）;
}
标签{
public
tags = array(); } Tag { public
这篇关于如何从mysql创建具有关联标记对象的帖子列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
