mysql - leftJoin 与同一个表返回比预期更多的记录（查询生成器）
问题描述
我创建了一个查询来获取数据以返回视图。
查询应该返回 8 条记录，而是返回 10 条记录。一个订单被错误地复制了 3 次。
我可以发现以下两个 leftJoin 与同一个表导致了这个错误：
左加入：
->leftJoin('users_r3_details AS users_1', 'orders_detail_new.userID', '=', 'users_1.userID')
->leftJoin('users_r3_details AS users_2', 'orders_detail_new.customerID', '=', 'users_2.userID')
列 ：
'users_1.firstname AS FirstnameUser',
'users_1.lastname AS LastnameUser',
'users_2.firstname AS FirstnameCustomer',
'users_2.lastname AS LastnameCustomer',
我无法修复它，所以我会在这里感谢您的专业知识。
谢谢
询问 ：
# SQL : GET THE ORDER DATA
$orders = DB::table('orders_detail_new')
->leftJoin('status_order','orders_detail_new.statusorderID', '=', 'status_order.id')
->leftJoin('users_r3_details AS users_1', 'orders_detail_new.userID', '=', 'users_1.userID')
->leftJoin('users_r3_details AS users_2', 'orders_detail_new.customerID', '=', 'users_2.userID')
->leftJoin('dessert_servings','orders_detail_new.dessertservingID', '=', 'dessert_servings.id')
->leftJoin('product_types','orders_detail_new.producttype_ID', '=', 'product_types.producttypeID')
->leftJoin('product_names','orders_detail_new.productname_ID', '=', 'product_names.productname_ID')
->leftJoin('party_loaf_portions','orders_detail_new.ID_portion', '=', 'party_loaf_portions.portion_ID')
->leftJoin('party_loaf_weights','orders_detail_new.partyloafweightID', '=', 'party_loaf_weights.id')
->leftJoin('party_loaf_sandwiches_1','orders_detail_new.partyloafsandwich1ID', '=', 'party_loaf_sandwiches_1.id')
->leftJoin('party_loaf_sandwiches_2','orders_detail_new.partyloafsandwich2ID', '=', 'party_loaf_sandwiches_2.id')
->leftJoin('party_loaf_sandwiches_3','orders_detail_new.partyloafsandwich3ID', '=', 'party_loaf_sandwiches_3.id')
->leftJoin('party_loaf_sandwiches_4','orders_detail_new.partyloafsandwich4ID', '=', 'party_loaf_sandwiches_4.id')
->leftJoin('party_loaf_ribbons','orders_detail_new.partyloafribbonID', '=', 'party_loaf_ribbons.id')
->leftJoin('savoury_canapes','orders_detail_new.canapeID', '=', 'savoury_canapes.id')
->leftJoin('savoury_mini_puffs','orders_detail_new.minipuffID', '=', 'savoury_mini_puffs.id')
->leftJoin('savoury_mini_vols_au_vent','orders_detail_new.minivolsauventID', '=', 'savoury_mini_vols_au_vent.id')
->leftJoin('savoury_little_appetisers','orders_detail_new.littleappetiserID', '=', 'savoury_little_appetisers.id')
->leftJoin('sweet_sweets','orders_detail_new.sweetID', '=', 'sweet_sweets.id')
->select(
'orders_detail_new.userID AS userID',
'orders_detail_new.customerID AS customerID',
'orders_detail_new.id AS rowID',
'orders_detail_new.orderID AS orderID',
'users_1.firstname AS FirstnameUser',
'users_1.lastname AS LastnameUser',
'users_2.firstname AS FirstnameCustomer',
'users_2.lastname AS LastnameCustomer',
'orders_detail_new.laboratory AS Laboratory',
'orders_detail_new.ownerID AS ownerID',
'orders_detail_new.statusorderID AS StatusID',
'status_order.status_order AS Status',
'orders_detail_new.deliverydate AS DeliveryDate',
'product_types.product_type AS Type',
'product_names.product_name AS Name',
'dessert_servings.serving_persons AS Servings',
'orders_detail_new.desserttextmessage AS Message',
'orders_detail_new.dessertdecorchocolateID AS DecoChocoFruits',
'orders_detail_new.dessertdecorflowerID AS DecoSmallFlowers',
'orders_detail_new.piedecoration AS Decoration',
'party_loaf_portions.portions AS Portions',
'party_loaf_weights.weight AS Weight',
'party_loaf_sandwiches_1.assortment AS Assortment1',
'party_loaf_sandwiches_2.assortment AS Assortment2',
'party_loaf_sandwiches_3.assortment AS Assortment3',
'party_loaf_sandwiches_4.assortment AS Assortment4',
'party_loaf_ribbons.color AS RibbonColor',
'slicescutID AS Cut',
'bread_quantity AS BreadQuantity',
'savoury_canapes.assortment AS CanapésAssortment',
'savoury_mini_puffs.assortment AS MiniPuffsAssortment',
'savoury_mini_vols_au_vent.assortment AS VolsAuVentAssortment',
'savoury_little_appetisers.assortment AS AppetisersAssortment',
'sweet_sweets.assortment AS SweetAssortment',
'orders_detail_new.sweet_quantity AS SweetQuantity',
'orders_detail_new.savoury_quantity AS SavouryQuantity',
'orders_detail_new.productprice AS Price',
'orders_detail_new.productaddfee AS Surcharge',
'orders_detail_new.piedecorationprice AS DecoSurcharge',
'orders_detail_new.subtotal AS Total'
)
### ->orderby('orders_detail_new.id', '=', 'asc')
->where('orders_detail_new.producttype_ID', '=', 4) // FOR test PURPOSE
->where('orders_detail_new.productname', '=', 26) // FOR test PURPOSE
### ->take(5) // FOR test PURPOSE
->get();
dd($orders);
dd（订单）
Collection {#2265 ▼
#items: array:10 [▼
0 => {#2257 ▼
+"userID": 22
+"customerID": 31
+"rowID": 1019
+"orderID": 1007
+"FirstnameUser": "Un"
+"LastnameUser": "Admin"
+"FirstnameCustomer": "Daniel"
+"LastnameCustomer": "Favre"
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-09-02"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
1 => {#2258 ▼
+"userID": 22
+"customerID": 31
+"rowID": 1019
+"orderID": 1007
+"FirstnameUser": "Un"
+"LastnameUser": "Admin"
+"FirstnameCustomer": "Daniel"
+"LastnameCustomer": "Favre"
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-09-02"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
2 => {#2264 ▼
+"userID": 22
+"customerID": 31
+"rowID": 1019
+"orderID": 1007
+"FirstnameUser": "Un"
+"LastnameUser": "Admin"
+"FirstnameCustomer": "Daniel"
+"LastnameCustomer": "Favre"
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-09-02"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
3 => {#2259 ▼
+"userID": 9
+"customerID": null
+"rowID": 1029
+"orderID": 1008
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
4 => {#2262 ▼
+"userID": 9
+"customerID": null
+"rowID": 1039
+"orderID": 1009
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
5 => {#2260 ▼
+"userID": 9
+"customerID": null
+"rowID": 1049
+"orderID": 1010
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
6 => {#2261 ▼
+"userID": 9
+"customerID": null
+"rowID": 1059
+"orderID": 1011
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
7 => {#2266 ▼
+"userID": 9
+"customerID": null
+"rowID": 1069
+"orderID": 1012
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
8 => {#2267 ▼
+"userID": 9
+"customerID": null
+"rowID": 1079
+"orderID": 1013
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
9 => {#2268 ▼
+"userID": 9
+"customerID": null
+"rowID": 1089
+"orderID": 1014
+"FirstnameUser": "Client"
+"LastnameUser": "Test"
+"FirstnameCustomer": null
+"LastnameCustomer": null
+"Laboratory": "Boulanger [Claude];Cuisine"
+"ownerID": null
+"StatusID": 2
+"Status": "Non traitée"
+"DeliveryDate": "2019-06-30"
+"Type": "Pain-surprise"
+"Name": "Mélangé"
+"Servings": null
+"Message": null
+"DecoChocoFruits": "Pas de décoration chocolat et fruits"
+"DecoSmallFlowers": "Pas de décoration petites fleurs"
+"Decoration": null
+"Portions": "100"
+"Weight": "2.5 Kg"
+"Assortment1": "Confiture Fraise"
+"Assortment2": "Foie gras"
+"Assortment3": "Gruyère"
+"Assortment4": "Jambon"
+"RibbonColor": "Blanc"
+"Cut": "Pas de coupe"
+"BreadQuantity": null
+"CanapésAssortment": null
+"MiniPuffsAssortment": null
+"VolsAuVentAssortment": null
+"AppetisersAssortment": null
+"SweetAssortment": null
+"SweetQuantity": null
+"SavouryQuantity": null
+"Price": "89.00"
+"Surcharge": "0.00"
+"DecoSurcharge": "0.00"
+"Total": "89.00"
}
]
}
解决方案
我怀疑有相同的用户具有相同的 customerId。尝试添加这些。
->where('orders_detail_new.customerID', '!=', 'users_1.userID')
->where('orders_detail_new.userID', '!=', 'users_2.userID')
或者
->where('orders_detail_new.customerID', '!=', 'users_1.userID')
或者
->where('orders_detail_new.userID', '!=', 'users_2.userID')
最后一个选项
->leftJoin('sweet_sweets','orders_detail_new.sweetID', '=', 'sweet_sweets.id')
->distinct()
->select(
推荐阅读
- java - 如何将Java中的“Color.rgb”设置为随机？
- java - 按下按钮时如何在Java中销毁CoundownTimer？
- sql - 在 SQL (ORACLE) 上组合和检查表值
- c# - Asp .Net Core 单元测试模拟 UserManager.CreateAsync 并返回身份结果
- sql-server - 如何将多个表中的属性分组到一个表中
- php - 来自 mysql 的 Echo SUM
- arrays - 如何从带有条件的 React Native 中的 API 数组结果中获取记录？
- reactjs - React import .css 根据 process.env.REACT_APP_THEME
- matlab - 如何组合来自不同矩阵的行
- camunda - 历史外部任务日志