Level 73
Show us a sample of your database and the desired result to make this more understandable.
May 27, 2021
7
Level 10
Need help cleaning up complicated query
The query I am having trouble with is getting a monthly sum of the unit_cases by recipe per scenario.
I have a Forecast model that every-time I generate it I create 24 Unit_Cases models (24 months and a case value for each)
Schema::create('unit_cases', function (Blueprint $table) {
$table->id();
$table->foreignId('forecast_id');
$table->unsignedInteger('cases')->default(0);
$table->dateTime('month');
$table->timestamps();
});
The Forecast is associated with a Recipe, Distributer and Customer and belongs to a Scenario. It shows the foreignIds as nullable because I let the user create the model and then select from a list of options for recipe, distributer, and customer.
Schema::create('forecasts', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id');
$table->foreignId('scenario_id');
$table->foreignId('recipe_id')->nullable();
$table->foreignId('distributor_id')->nullable();
$table->foreignId('customer_id')->nullable();
$table->unsignedInteger('store_count')->default(0);
$table->timestamps();
});
In a given scenario I need to look through all the forecasts and see if there are any that have the same recipe. Then I need to take the forecasts that all have the same recipe model and combine the sums of their unit_cases by month. So July would have 20 cases + 10 cases if there were two forecasts that had the same recipe. Then I need to return a list of recipes with the sums of the unit_cases. (This is what is catching me up because the unit_cases are related to the forecasts not the recipe).
What is the best way to achieve this?
Level 10
Hi @tray2 Thank you! Here is a sample of a scenario with 3 forecasts. I only show the first 5 cases per forecast as it would take up a ton of space on here to show all 24 for each,
[
{
"id": 1,
"name": "Aggressive Scenario",
"description": null,
"user_id": 1,
"team_id": 1,
"created_at": "2021-05-28T03:41:56.000000Z",
"updated_at": "2021-05-28T03:41:56.000000Z",
"forecasts": [
{
"id": 1,
"user_id": 1,
"team_id": 1,
"recipe_id": 1,
"distributor_id": 1,
"scenario_id": 1,
"customer_id": 1,
"velocity": "10.00",
"store_count": 20,
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:44:06.000000Z",
"cases": [
{
"id": 1,
"forecast_id": 1,
"cases": 12,
"month": "2021-01-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 2,
"forecast_id": 1,
"cases": 20,
"month": "2021-02-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 3,
"forecast_id": 1,
"cases": 19,
"month": "2021-03-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 4,
"forecast_id": 1,
"cases": 10,
"month": "2021-04-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 5,
"forecast_id": 1,
"cases": 13,
"month": "2021-05-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
////// remaining cases here up to 24
{},
],
"recipe": {
"id": 1,
"name": "Apple Pie",
"SKU": "appl",
"description": null,
"status": "d",
"team_id": 1,
"user_id": 1,
"product_line_id": null,
"created_at": "2021-05-28T03:40:42.000000Z",
"updated_at": "2021-05-28T03:40:42.000000Z",
"bom": {},
"ingredients": [],
"packaging": [],
"commercial": {},
"product_line": null
},
"customer": {
"id": 1,
"team_id": 1,
"channel": null,
"name": "Ralphs",
"slug": "ralphs",
"created_at": "2021-05-28T03:42:05.000000Z",
"updated_at": "2021-05-28T03:42:05.000000Z"
},
"distributor": {
"id": 1,
"team_id": 1,
"name": "Vanny's",
"slug": "vannys",
"created_at": "2021-05-28T03:42:18.000000Z",
"updated_at": "2021-05-28T03:42:18.000000Z"
}
},
{
"id": 2,
"user_id": 1,
"team_id": 1,
"recipe_id": 2,
"distributor_id": 2,
"scenario_id": 1,
"customer_id": 1,
"velocity": "20.00",
"store_count": 10,
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:58.000000Z",
"cases": [
{
"id": 25,
"forecast_id": 2,
"cases": 3,
"month": "2021-01-01 00:00:00",
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:38.000000Z"
},
{
"id": 26,
"forecast_id": 2,
"cases": 10,
"month": "2021-02-01 00:00:00",
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:38.000000Z"
},
{
"id": 27,
"forecast_id": 2,
"cases": 20,
"month": "2021-03-01 00:00:00",
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:38.000000Z"
},
{
"id": 28,
"forecast_id": 2,
"cases": 30,
"month": "2021-04-01 00:00:00",
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:38.000000Z"
},
{
"id": 29,
"forecast_id": 2,
"cases": 5,
"month": "2021-05-01 00:00:00",
"created_at": "2021-05-28T03:45:38.000000Z",
"updated_at": "2021-05-28T03:45:38.000000Z"
},
{},
/////// remaining cases here up to 48
{},
],
"recipe": {
"id": 2,
"name": "Banana Pie",
"SKU": "bnanpie",
"description": null,
"status": "d",
"team_id": 1,
"user_id": 1,
"product_line_id": null,
"created_at": "2021-05-28T03:45:12.000000Z",
"updated_at": "2021-05-28T03:45:12.000000Z",
"bom": {},
"ingredients": [],
"packaging": [],
"commercial": {},
"product_line": null
},
"customer": {
"id": 1,
"team_id": 1,
"channel": null,
"name": "Ralphs",
"slug": "ralphs",
"created_at": "2021-05-28T03:42:05.000000Z",
"updated_at": "2021-05-28T03:42:05.000000Z"
},
"distributor": {
"id": 2,
"team_id": 1,
"name": "Telunds",
"slug": "telunds",
"created_at": "2021-05-28T03:45:48.000000Z",
"updated_at": "2021-05-28T03:45:48.000000Z"
}
},
{
"id": 3,
"user_id": 1,
"team_id": 1,
"recipe_id": 1,
"distributor_id": 2,
"scenario_id": 1,
"customer_id": 1,
"velocity": "30.00",
"store_count": 20,
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:44:06.000000Z",
"cases": [
{
"id": 49,
"forecast_id": 3,
"cases": 12,
"month": "2021-01-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 50,
"forecast_id": 3,
"cases": 20,
"month": "2021-02-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 51,
"forecast_id": 2,
"cases": 19,
"month": "2021-03-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 52,
"forecast_id": 3,
"cases": 10,
"month": "2021-04-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{
"id": 53,
"forecast_id": 3,
"cases": 13,
"month": "2021-05-01 00:00:00",
"created_at": "2021-05-28T03:41:58.000000Z",
"updated_at": "2021-05-28T03:41:58.000000Z"
},
{},
///// remaining cases
{},
],
"recipe": {
"id": 1,
"name": "Apple Pie",
"SKU": "appl",
"description": null,
"status": "d",
"team_id": 1,
"user_id": 1,
"product_line_id": null,
"created_at": "2021-05-28T03:40:42.000000Z",
"updated_at": "2021-05-28T03:40:42.000000Z",
"bom": {},
"ingredients": [],
"packaging": [],
"commercial": {},
"product_line": null
},
"customer": {
"id": 1,
"team_id": 1,
"channel": null,
"name": "Ralphs",
"slug": "ralphs",
"created_at": "2021-05-28T03:42:05.000000Z",
"updated_at": "2021-05-28T03:42:05.000000Z"
},
"distributor": {
"id": 2,
"team_id": 1,
"name": "Telunds",
"slug": "telunds",
"created_at": "2021-05-28T03:45:48.000000Z",
"updated_at": "2021-05-28T03:45:48.000000Z"
}
},
]
}
]
Level 55
@chrisgrim something like
Recipe::select(['recipe.*', \DB::raw(
'(select sum(cases) from unit_cases join forecasts on forecasts.id = unit_cases.forecast_id where forecasts.recipe_id = recipes.id) as sum_unit_cases'
)])
->get();
Level 55
@chrisgrim did you try this?
Level 10
Hi @silencebringer Thanks so much for helping! I had to change recipe.* to recipes.* but it did return the two recipes with "sum_unit_cases". Very exciting. I am not very good at raw searches so it is cool to see it in action. Is there anyway to do a sum of the months instead of a total sum? Instead of "sum_unit_cases" : 200 it would be
"cases": [
{
cases: 25 (this is a sum of all the cases numbers for the first month for the same recipe in the scenario)
},
{
cases: 50 (this is a sum of all the cases numbers for the second month for the same recipe in the scenario)
},
{
cases: 35 (this is a sum of all the cases numbers for the third month for the same recipe in the scenario)
},
etc...
]
Level 55
@chrisgrim this will require another approach - grouping by recipes and months. Something like this, I think:
Recipe::select(['recipes.*', 'unit_cases.month', \DB::raw('sum(unit_cases.cases) as monthly_cases')])
->join('forecasts', 'forecasts.recipe_id', '=', 'recipes.id')
->join('unit_cases', 'unit_cases.forecast_id', '=', 'forecasts.id')
->groupBy('recipes.id', 'unit_cases.month')
->get();
Level 10
Oh interesting. That returned a ton of copies of the recipes but didn't attach the cases so didn't work exactly. Should it be this difficult to do this or am I setting it up incorrectly in the database @silencebringer
Please or to participate in this conversation.