多个字段的Laravel SUM使用原始查询返回null [英] Laravel SUM of multiple fields return null with raw query
问题描述
以下是我的查询:
$ sales = DB :: table('sales')
- > leftJoin('category_sales','category_sales.sale_id','=','sales.id')
- > leftJoin('department_sales','department_sales.sale_id','=','sales。 id')
- > leftJoin('store_configs','store_configs.id','=','sales.store_config_id')
- > select('sales.date',
DB :: raw('store_configs.store_dba'),
DB :: raw('sales.id'),
DB :: raw('(sales.taxable + sales.non_taxable + category_sales。金额+ department_sales.amount)作为total_sales'),
DB :: raw('0.0825 *(sales.taxable + category_sales.amount + department_sales.amount)as total_tax'))
- > groupBy 'date') - > orderBy('date','desc')
- > get();
当我在 category_sales和department_sales
金额值, total_sales
和 total_tax
为null。
我的问题是:如果数据存在,我如何仍然总结字段的值?
应税,不可扣款和金额$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ > category_sales和department_sales
是整数,
默认值
到 0
我的表结构只是一个想法,与department_sales类似:
CREATE (`)`````````````````````````````````````` unsigned DEFAULT NULL,
`sale_id` int(10)unsigned DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`category_sales_category_id_index`(`category_id`),
KEY `category_sales_sale_id_index`(`sale_id`),
CONSTRAINT`category_sales_category_id_foreign` FOREIGN KEY(`category_id`)参考`类别`(`id`)ON DELETE CASCADE,
CONSTRAINT`category_sales_sale_id_foreign` FOREIGN KEY _id`)参考`sales`(`id`)ON DELETE CASCADE)ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
可以将可空字段包装到IFNULL()函数中,这样做:
DB :: raw('(IFNULL(sales.taxable,0)
+ IFNULL sales.non_taxable,0)
+ IFNULL(category_sales.amount,0)
+ IFNULL(department_sales.amount,0)
)as total_sales'),
DB :: raw ('0.0825 *(IFNULL(sales.taxable,0)
+ IFNULL(category_sales.amount,0)
+ IFNULL(department_sales.amount,0))作为total_tax'))
The following is my query :
$sales = DB::table('sales')
->leftJoin('category_sales', 'category_sales.sale_id', '=', 'sales.id')
->leftJoin('department_sales', 'department_sales.sale_id', '=', 'sales.id')
->leftJoin('store_configs', 'store_configs.id', '=', 'sales.store_config_id')
->select('sales.date',
DB::raw('store_configs.store_dba'),
DB::raw('sales.id'),
DB::raw('(sales.taxable + sales.non_taxable + category_sales.amount + department_sales.amount) as total_sales'),
DB::raw('0.0825*(sales.taxable + category_sales.amount + department_sales.amount) as total_tax'))
->groupBy('date')->orderBy('date', 'desc')
->get();
I get the right value when I have values on category_sales and department_sales
table. Lets say, I do not have any amount value for the sales_id in category_sales
table, the final result for total_sales
and total_tax
is null.
My question is : how would I still sum the values of fields if the data is present ?
taxable, non_taxable, and amount'
in category_sales and department_sales
are integer with defaults
to 0
My table structure just for an idea and is similar with department_sales:
CREATE TABLE `category_sales` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`amount` int(11) NOT NULL DEFAULT '0',
`category_id` int(10) unsigned DEFAULT NULL,
`sale_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `category_sales_category_id_index` (`category_id`),
KEY `category_sales_sale_id_index` (`sale_id`),
CONSTRAINT `category_sales_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
CONSTRAINT `category_sales_sale_id_foreign` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
You can wrap nullable fields into IFNULL() function, something like this:
DB::raw('(IFNULL(sales.taxable,0)
+ IFNULL(sales.non_taxable,0)
+ IFNULL(category_sales.amount,0)
+ IFNULL(department_sales.amount,0)
) as total_sales'),
DB::raw('0.0825*(IFNULL(sales.taxable,0)
+ IFNULL(category_sales.amount,0)
+ IFNULL(department_sales.amount,0)) as total_tax'))
这篇关于多个字段的Laravel SUM使用原始查询返回null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!