Mysql父子表减少数据库调用或在PHP中合并

If I had 2 tables, say blog_category and blog, each "blog" can belong in a particular category only so a 1-1 relationship based on a key called "blog_category_id".

如果我有2个表,比如blog_category和博客,则每个“博客”只能属于特定类别,因此基于名为“blog_category_id”的键的1-1关系。

Now in my code I would do something like:

现在在我的代码中我会做类似的事情:

//Loop through categories such as 
foreach($categories as $cat):
//then for each category create an array of all its posts
$posts = $cat->getPosts(); // This would be another DB call to get all posts for the cat
//do stuff with posts
endforeach;

Now to me this seems like it could end up quite expensive in terms of DB calls depending on the size of $categories. Would this still be the best solution to do this? Or would I be able to do something in the code and first retrieve all the categories, then retrieve all the blogs and map them to their corresponding category via the id somehow? This would in theory be only 2 calls to the DB, now size wise the result set for call 2 (the blogs) would definitely be larger, but would the actual DB call be as expensive?

现在对我而言,根据$ categories的大小,这似乎在DB调用方面可能会相当昂贵。这仍然是最好的解决方案吗?或者我能够在代码中执行某些操作并首先检索所有类别,然后检索所有博客并通过ID以某种方式将它们映射到相应的类别?理论上这只是对数据库的2次调用,现在大小明智,调用2的结果集(博客)肯定会更大,但实际的数据库调用会不会很昂贵?

I would normally go for the first option, but I'm just wondering if there would be a better way of approaching this or is it more likely that the extra processing in PHP would be more costly in terms of performance? Also specifically from an MVC perspective, if the model returns the categories, but it should also return the corresponding blogs for that category, I'm not sure how best to structure this, from my understanding, shouldn't the model return all the data required for the view?

我通常会选择第一个选项,但我只是想知道是否有更好的方法来处理这个问题,或者更有可能的是PHP中的额外处理在性能方面会更昂贵?还特别是从MVC的角度来看,如果模型返回类别,但它也应该返回该类别的相应博客,我不知道如何最好地构建这个,从我的理解,不应该模型返回所有数据视图需要吗?

Or would I be better off selecting all categories and blogs using inner joins in the first query and create the output I need of this? Perhaps by using a multi-dimensional array?

或者我最好在第一个查询中使用内部联接选择所有类别和博客并创建我需要的输出?也许通过使用多维数组?

Thanks

2 个解决方案

#1

You can use a simple SQL query to get all categories and posts like the following:

您可以使用简单的SQL查询来获取所有类别和帖子,如下所示:

SELECT *
FROM posts p
JOIN categories c ON c.id = p.blog_category_id
ORDER BY c.category_name ASC,
         p.posted_date DESC

Then when you loop over the returned records assign the current category id to a variable, which you can use to compare against the next records category. If the category is different then print the category title before printing the record. It is important to note that for this to work you need to get the posts ordered by category first and then post so that all posts in the same category are together.

然后,当您循环返回的记录时,将当前类别ID分配给变量,您可以使用该变量与下一个记录类别进行比较。如果类别不同,则在打印记录之前打印类别标题。重要的是要注意,要使其工作,您需要先按类别排序,然后发布,以便同一类别中的所有帖子在一起。

So for example:

例如:

$category_id = null;
foreach($posts as $post) {
    if($post['blog_category_id'] != $category_id) {
        $category_id = $post['blog_category_id'];
        echo '<h2>' . $post['category_name'] . '</h2>';
    }
    echo '<h3>' . $post['post_title'] . '</h3>';
    echo $post['blog_content'];
}

Note: as you have not posted up the schema of these two tables I have had to make up column names that are similar to what I would expect to see in code like this. So the code above will not work with your code without some adjustments to account for this.

注意:由于你还没有发布这两个表的模式,我不得不编写类似于我希望在这样的代码中看到的列名。因此,如果没有进行一些调整,上面的代码将无法使用您的代码。

#2

The best solution depends on what you are going to do with data.

最佳解决方案取决于您将如何处理数据。

Lazy loading

Load data when you need it. It's a good solution when you have, for instance, 20 categories and you load posts for only 2 of them. However, if you need to load posts for all of them it won't be efficient at all... It's called a n+1 queries (and it's really bad).

在需要时加载数据。例如,当您拥有20个类别并且只为其中2个类别加载帖子时,这是一个很好的解决方案。但是,如果你需要为所有这些帖子加载帖子,它根本就不会有效......它被称为n + 1个查询(这真的很糟糕)。

Eager loading

On the other hand, if you have to access to almost all of your posts, you should do an eager loading.

另一方面,如果你必须访问几乎所有的帖子,你应该做一个急切的加载。

-- Load all your data in a query
SELECT *
FROM categories c
INNER JOIN posts p ON c.id = p.category_id;

// Basic example in JSON of how to format your result 
{
   'cat1': ['post1', 'post2'],
   'cat2': ['post5', 'post4', 'post5'],
   ...
}

What to do?

该怎么办?

In your case I would say an eager loading because you load everything in a loop. But if you don't access to the most of your data, you should re-design your model to perform a lazy loading in such a way that the SQL query to load posts for a specific category is actually performed when a view try to access them.

在你的情况下,我会说一个急切的加载,因为你加载循环中的所有内容。但是,如果您不能访问大部分数据,则应重新设计模型以执行延迟加载,以便在视图尝试访问时实际执行加载特定类别的帖子的SQL查询他们。

What do you think?

你怎么看?

#1