如何一次获取对象图？ [英] How to fetch an object graph at once?

问题描述

我正在读一本书，作者在其中谈论如何一步获取一行+所有链接的父行。就像一次获取订单和所有物品一样。好的，听起来不错，但实际上：我从未见过在SQL中有可能要求（比如说）一个订单+ 100个项目吗？该记录集的外观如何？我会得到101行，其中包含order和item表的合并字段，其中100行的order字段具有很多NULL值，而一行的item字段具有很多NULL值？那是路要走吗？还是有一些凉爽的东西？我的意思是……我从未听说过将数组提取到字段上吗？

I'm reading a book, where the author talks about fetching an row + all linked parent rows in one step. Like fetching an order + all it's items all at once. Okay, sounds nice, but really: I've never seen an possibility in SQL to ask for - lets say - one order + 100 items? How would this record set look like? Would I get 101 rows with merged fields of both the order and the item table, where 100 rows have a lot of NULL values for the order fields, while one row has a lot of NULL values for the item fields? Is that the way to go? Or is there something much cooler? I mean... I never heard of fetching arrays onto a field?

推荐答案

一个简单的JOIN就能解决问题：

A simple JOIN would do the trick:

SELECT     o.*
,          i.*
FROM       orders o
INNER JOIN order_items i
ON         o.id = i.order_id

order_items中的每一行将返回一行。返回的行由orders表中的所有字段组成，并与其相连，order_items表中的所有字段（从字面上看，表中的记录是联接在一起的，也就是说，它们通过记录串联来组合）

The will return one row for each row in order_items. The returned rows consist of all fields from the orders table, and concatenated to that, all fields from the order_items table (quite literally, the records from the tables are joined, that is, they are combined by record concatenation)

因此，如果订单具有（id，order_date，customer_id）和order_items具有（order_id，product_id，price），则上述语句的结果将包含具有（id，order_date，customer_id，order_id的记录，product_id，价格）

So if orders has (id, order_date, customer_id) and order_items has (order_id, product_id, price) the result of the statement above will consist of records with (id, order_date, customer_id, order_id, product_id, price)

您需要了解的一件事是，只要一个主有两个不同的详细表，这种方法就会失效。让我解释一下。

One thing you need to be aware of is that this approach breaks down whenever there are two distinct 'detail' tables for one 'master'. Let me explain.

在orders / order_items示例中，orders是主文件，order_items是详细信息：order_items中的每一行都属于或完全依赖于一行按顺序。反之则不成立：orders表中的一行可以在order_items表中具有零个或多个相关行。联接条件

In the orders/order_items example, orders is the master and order_items is the detail: each row in order_items belongs to, or is dependent on exactly one row in orders. The reverse is not true: one row in the orders table can have zero or more related rows in the order_items table. The join condition

ON o.id = i.order_id 

确保仅合并并返回相关行（假设数据库允许您省略联接，则省略条件将从两个表中恢复所有可能的行组合）条件）

ensures that only related rows are combined and returned (leaving out the condition would retturn all possible combinations of rows from the two tables, assuming the database would allow you to omit the join condition)

现在，假设您有一个具有两个详细信息的主数据，例如，customers作为master，customer_orders作为detail1和customer_phone_numbers。假设您要检索特定客户以及所有订单和所有电话号码。您可能会想写：

Now, suppose you have one master with two details, for example, customers as master and customer_orders as detail1 and customer_phone_numbers. Suppose you want to retrieve a particular customer along with all is orders and all its phone numbers. You might be tempted to write:

SELECT     c.*, o.*, p.*
FROM       customers                c
INNER JOIN customer_orders          o
ON         c.id                   = o.customer_id
INNER JOIN customer_phone_numbers   p
ON         c.id                   = p.customer_id

这是有效的SQL，它将执行（假设有表和列名）
但是问题是，它会给你带来垃圾的结果。假设您有两个订单（1,2）和两个电话号码（A，B）的客户，您得到以下记录：

This is valid SQL, and it will execute (asuming the tables and column names are in place) But the problem is, is that it will give you a rubbish result. Assuming you have on customer with two orders (1,2) and two phone numbers (A, B) you get these records:

customer-data | order 1 | phone A
customer-data | order 2 | phone A
customer-data | order 1 | phone B
customer-data | order 2 | phone B

这是垃圾，因为它表明订单1与电话号码A和电话号码之间存在某种关系B，订单2和电话号码A和B。

This is rubbish, as it suggests there is some relationship between order 1 and phone numbers A and B and order 2 and phone numbers A and B.

更糟糕的是，这些结果可能会完全导致记录数爆炸，这极大地损害了数据库性能。

What's worse is that these results can completely explode in numbers of records, much to the detriment of database performance.

因此，JOIN非常适合将已知深度的项目层次结构（客户->订单-> order_items）展平到一个大表中，该表仅复制每个项的主项详细项目。但是，提取相关项目的真实图形是可怕的。这是SQL设计方式的直接结果-它只能输出规范化表，而无需重复组。这是对象关系映射器的存在方式，它允许将具有多个从属对象的依赖集合的对象定义存储在关系数据库中，并从关系数据库中检索它们，而又不会失去程序员的头脑。

So, JOIN is excellent to "flatten" a hierarchy of items of known depth (customer -> orders -> order_items) into one big table which only duplicates the master items for each detail item. But it is awful to extract a true graph of related items. This is a direct consequence of the way SQL is designed - it can only output normalized tables without repeating groups. This is way object relational mappers exist, to allow object definitions that can have multiple dependent collections of subordinate objects to be stored and retrieved from a relational database without losing your sanity as a programmer.

这篇关于如何一次获取对象图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！