请在学校的项目中帮助我。我如何在Laravel控制器中查询这种情况。我有三张桌子:shipping_table和shipping_products and tbl_products,现在我的桌子结构是:
运输表:
Ship_ID (INT AUTO INC)
AMOUNT (DOUBLE,2)
NAME (VARCHAR)
SHIP_DATE (DATE)
RECEIVER (VARCHAR)
shipping_products:
ID (INT AUTO INC)
Ship_id (foreign key from shipping table)
Product_id
products_table:
Product_id (Auto Inc)
name(varchar)
Qty(int)
Description (varchar)
现在我想要的是这样的查询结果:我想把所有的东西都放在运输桌上,在子阵列中,我想获得带有所需运输ID的Shippting_products中列出的产品。
类似的结果:示例我有2个shipping_table值
Array(2) {
[0] Array(4) {
['Ship_id'] "1"
['Amount'] "10000"
['Ship_date'] "1995-12-11"
['Ship_products'] Array(3)
['id'] "1" Array(2)
['product_id'] "5"
['name'] "Product 1"
['id'] "2" Array(2)
['product_id'] "6"
['name'] "Product 2"
['id'] "3" Array(2)
['product_id'] "10"
['name'] "Product 15"
}
[1] Array(4) {
['Ship_id'] "2"
['Amount'] "15000"
['Ship_date'] "1995-12-15"
['Ship_products'] Array(2)
['id'] "1" Array(2)
['product_id'] "5"
['name'] "Product 1"
['id'] "2" Array(2)
['product_id'] "6"
['name'] "Product 2"
}
}
SQL零件很容易(使用JOINS
)
SELECT *
FROM Shipping S
LEFT JOIN Shipping_Products SP
ON SP.Ship_Id=S.Ship_Id
LEFT JOIN Products P
ON P.Product_id=SP.Product_id
php
零件更为复杂,因为您必须以循环获取结果并检测Ship_id
和Product_id
的更改并放入生成的数组中。
因为这是作业..我将其作为练习...
这是一个快速算法示例 - 未经测试但合理的声音。
$cur_ship = '';
$cur_prod = '';
$results = array();
foreach ($resultset as $key => $row) {
if ($cur_ship != $row['Ship_id']) {
$cur_ship = $row['Ship_id'];
$cur_prod = '';
$results[$cur_ship] = array();
// Fill ship info from $row
}
if ($cur_prod != $row['Product_id']) {
$cur_prod = $row['Product_id'];
$results[$cur_ship][$cur_prod] = array();
// Fill Product info from $row
}
// FILL OUR $results[$cur_ship][$cur_prod] from $row
}