I have two tables that I am querying (please see the schema below). What I need to do is get the sum of price by the order_id which I can get with this query:
public function spendPerCatalogNumber(Collection $orders, array $suppliers): array
{
$data = DB::table('ordered_items')
->whereIn('order_id', $orders)
->whereIn('supplier', $suppliers)
->select(
[
'supplier_sku',
DB::raw('SUM(price) as cogs'),
DB::raw('SUM(quantity) as qty'),
DB::raw('(SUM(price::numeric) / SUM(quantity)) as avg')
]
)
->groupBy('supplier_sku')
->get();
But trying to get the costs (doesn't work and is very messy). For more details, the costs to sum are sent from the client in an array like so: ['fees', 'shipping_cost']
$concatCosts = '';
if (count($costs) > 0) {
$concatCosts = array_reduce($costs, function ($carry, $item) {
return $carry . ($carry ? ' ' : '') . $item;
});
}
$orderCosts = '';
if (count($costs)) {
$orderCosts = DB::table('orders')
->whereIn('id', $orders)
->select(
'id',
DB::raw('sum (' . $concatCosts . ') as costs')
)
->groupBy('id')
->get();
}
Schemas
Ordered_items
labspend> \d ordered_items;
---------------------- -------------------------------- -------------------------------------------------------------
| Column | Type | Modifiers |
|---------------------- -------------------------------- -------------------------------------------------------------|
| id | integer | not null default nextval('ordered_items_id_seq'::regclass) |
| created_at | timestamp(0) without time zone | |
| updated_at | timestamp(0) without time zone | |
| order_id | integer | not null |
| bid_id | integer | |
| quantity | integer | not null default 1 |
| is_returned | boolean | not null default false |
| is_received | boolean | not null default false |
| name | text | |
| price | money | |
| fees | money | |
| shipment | character varying(255) | |
| manufacturer | character varying(255) | |
| manufacturer_sku | character varying(255) | |
| supplier | character varying(255) | |
| supplier_sku | character varying(255) | |
| size | character varying(255) | |
| size_unit | integer | |
| vendor_ref_number | character varying(255) | |
| url | text | |
| deleted_at | timestamp(0) without time zone | |
| quote_request_id | integer | |
| ordered_item_savings | money | |
---------------------- -------------------------------- -------------------------------------------------------------
Orders
labspend> \d orders;
------------------------------------- -------------------------------- ------------------------------------------------------
| Column | Type | Modifiers |
|------------------------------------- -------------------------------- ------------------------------------------------------|
| id | integer | not null default nextval('orders_id_seq'::regclass) |
| created_at | timestamp(0) without time zone | |
| updated_at | timestamp(0) without time zone | |
| submitter_user_id | integer | not null |
| is_hidden_admin | boolean | not null default false |
| has_invoice | boolean | not null default false |
| stripe_charge_id | character varying(255) | |
| order_number | character varying(255) | |
| shipping_address_id | integer | |
| billing_address_id | integer | |
| shipping_method | character varying(255) | |
| shipping_account_number | character varying(255) | |
| sales_tax | money | |
| shipping_cost | money | |
| is_paid | boolean | not null default false |
| external_email | text | |
| owner_type | character varying(255) | not null default 'App\User'::character varying |
| owner_id | integer | |
| is_manually_purchased | boolean | not null default false |
| is_hidden | boolean | not null default false |
| manual_date | date | |
| manual_total | money | |
| po_number | character varying(255) | |
| deleted_at | timestamp(0) without time zone | |
| fees | money | |
| three_way_does_invoice_match | boolean | |
| three_way_invoice_issue | character varying(255) | |
| three_way_does_packing_status_match | boolean | |
| three_way_packing_issue | character varying(255) | |
| stripe_invoice_id | character varying(255) | |
| stripe_status | character varying(255) | |
------------------------------------- -------------------------------- ------------------------------------------------------
To help with the Eloquent query, I did come up with this MySQL query which gives me the correct results:
SELECT
ordered_items.supplier,
sum(orders.fees) as feestotal,
sum(orders.shipping_cost) as shippingtotal,
sum(orders.sales_tax) as salestaxtotal FROM orders
INNER JOIN
ordered_items ON orders.id=ordered_items.order_id
GROUP By
ordered_items.supplier
ORDER BY
ordered_items.supplier;
------------------- ----------- --------------- ---------------
| supplier | feestotal | shippingtotal | salestaxtotal |
|------------------- ----------- --------------- ---------------|
| Sigma Aldrich | $715.55 | $725.10 | $309.80 |
| Thomas Scientific | $168.00 | $255.72 | $182.55 |
| VWR | $800.00 | $259.65 | $377.25 |
------------------- ----------- --------------- ---------------
CodePudding user response：
try sum(fees) sum(shipping_cost) as costs
CodePudding user response：
I actually decided to go see if I could find a solution via a MySQL query (which I posted in my original answer) and that forced me to ask if I can use joins in Eloquent (duh!).
Here is the solution I was looking for:
private function spendPerSupplier(Collection $orders, array $suppliers, array $costs): array
{
$concatCosts = '';
if (count($costs)) {
$concatCosts = array_reduce($costs, function ($carry, $item) {
return $carry . ($carry ? ' ' : '') . ('orders.' . $item);
});
}
$data = DB::table('ordered_items')
->join('orders', 'ordered_items.order_id', '=', 'orders.id')
->whereIn('ordered_items.order_id', $orders)
->whereIn('ordered_items.supplier', $suppliers)
->select(
'ordered_items.supplier as suppliers',
DB::raw('SUM(ordered_items.price' . ($concatCosts ? ' ' . $concatCosts : '') . ') as total'),
)
->groupBy('ordered_items.supplier')
->get();