英文:
query for select fields from two tables joined with pivot lave_mysql_laravel query builder
问题
我有两个表
- PURCHASE TABLE（购买表）
| id | Country | Medicine | Quantity | Purchase date |
|---|---|---|---|---|
| 0 | Canada | Aspirin | 9 | 26/01/2022 14:16:59 |
| 1 | Canada | VitaminD | 10 | 19/07/2021 14:16:59 |
| 2 | Usa | Calcium | 4 | 19/06/2021 14:16:59 |
| 3 | Canada | VitaminC | 8 | 06/08/2022 14:16:59 |
| 4 | Argentina | Calcium | 10 | 05/12/2021 14:16:59 |
- PRICES TABLE（价格表）
| id | Country | Year | Medicine | Price |
|---|---|---|---|---|
| 0 | USA | 2020 | Aspirin | 14 |
| 1 | Canada | 2020 | Aspirin | 18 |
| 2 | Mexico | 2020 | Aspirin | 10 |
| 3 | Brazil | 2020 | Aspirin | 11 |
| 4 | Argentina | 2021 | Aspirin | 18 |
- PRICE_PURCHASE TABLE (PIVOT TABLE)（价格购买关联表）
| price_id | purchase_id |
|---|
我想要从购买表中选择purchase.country、purchase.medicine和purchase.quantity，以及从价格表中选择prices.price。我的关联表为空，我应该如何编写查询（使用MySQL和Laravel查询生成器），以获取所有这些字段（筛选与购买表的国家、药品和购买日期匹配的价格）。
非常感谢您的时间。
英文:
I have two tables
1.PURCHASE TABLE
| id | Country | Medicine | Quantity | Purchase date |
|---|---|---|---|---|
| 0 | Canada | Aspirin | 9 | 26/01/2022 14:16:59 |
| 1 | Canada | VitaminD | 10 | 19/07/2021 14:16:59 |
| 2 | Usa | Calcium | 4 | 19/06/2021 14:16:59 |
| 3 | Canada | VitaminC | 8 | 06/08/2022 14:16:59 |
| 4 | Argentina | Calcium | 10 | 05/12/2021 14:16:59 |
2.PRICES TABLE
| id | Country | Year | Medicine | Price |
|---|---|---|---|---|
| 0 | USA | 2020 | Aspirin | 14 |
| 1 | Canada | 2020 | Aspirin | 18 |
| 2 | Mexico | 2020 | Aspirin | 10 |
| 3 | Brasil | 2020 | Aspirin | 11 |
| 4 | Argentina | 2021 | Aspirin | 18 |
- PRICE_PURCHASE TABLE (PIVOT TABLE)
| price_id | purchase_id |
|---|
I want to select purchase.country, purchas.medicine, purchase.quantity from purchases table and prices.price from prices table. My pivot table is empty, how can I do the query (mysql and with laravel query builder too) to catch all this fields, (filtering prices.price that matches with puchases table country, medicine and purchase data)
Thank you very much for you time
答案1
得分: 1
你的Laravel数据库查询代码如下：
DB::table('purchases')->join('prices', function ($join) {
$join->on('purchases.country', '=', 'prices.country')
->on(DB::raw('YEAR(purchases.purchase_date)'), '=', 'prices.year')
->on('purchases.medicine', '=', 'prices.medicine');
})->select('purchases.country', 'purchases.medicine', 'purchases.quantity', 'prices.price')->get();
英文:
For that your db query for Laravel is like:
DB::table('purchases')->join('prices', function ($join) {
$join->on('purchases.country', '=', 'prices.country')->on(DB::raw('YEAR(purchases.purchase_date)'), '=', 'prices.year')->on('purchases.medicine', '=', 'prices.medicine');
})->select('purchases.country', 'purchases.medicine','purchases.quantity', 'prices.price')->get();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论