php - PDO 使用外键获取类

Question

我有类(class):

class Order {
    private $order_id;
    private $product;
    private $quantity;
}

class Product {
    private $product_id;
    private $name;
    private $price;
}

我还有那些 MySql 表:

orders:
+----------+------------------+----------+
| order_id |     product      | quantity |
+----------+------------------+----------+
| INTEGER  | INT(FOREIGN KEY) | INTEGER  |
+----------+------------------+----------+

products:
+------------+---------+---------+
| product_id |   name  |  price  |
+------------+---------+---------+
|   INTEGER  | VARCHAR | INTEGER |
+------------+---------+---------+

orders.product 是与 product.product_id 关联的外键。

我想使用 PDO 从订单表中获取 ID 为 123 的订单，并将结果自动存储在 Order 类中。我知道有 PDO::fetchObject 和 PDO::FETCH_CLASS，问题是它们不能递归工作。

我知道我有一个简单的解决方案，即在一个查询中获取 order_id 和 quantity 列，然后在第二个查询中获取产品字段。有没有一种方法可以在一个查询中实现这一点(使用 JOIN)？

Answer 1

您可以在 Order 类中施展魔法，例如:

$dbh = new PDO(...);

class Order {
    private $order_id;
    private $product_id;
    // IMPORTANT!
    // If class have `product` property - setter won't be called.
    // private $product;
    private $quantity;

    // PDO call this method only in case
    // class doesn't have property with same name like column in db.
    public function __set($name, $value) {
        // If property is product - call setProduct.
        if ($name === 'product') {
            $this->setProduct($value);
        }
    }

    public function setProduct($productId)
    {
        /** \PDO IMPORTANT global var here just for example, use better design. */
        global $dbh;
        $s = $dbh->prepare('SELECT * FROM products WHERE product_id = :pId');
        $s->bindParam(':pId', $productId, PDO::PARAM_INT);
        $s->execute();
        $this->product = $s->fetchAll(PDO::FETCH_CLASS, Product::class);
    }
}

$s = $dbh->prepare('SELECT * FROM orders WHERE order_id = :oId');
$s->bindValue(':oId', 123, PDO::PARAM_INT);
$s->execute();
$result = $s->fetchAll(PDO::FETCH_CLASS, Order::class);