Я создаю веб-приложение для управления расходами.
У меня есть аналитика раздела, и я работаю над фильтрами для этой страницы.
Параметры фильтра отправляются на сервер правильно, но проблема в том, что необработанный SQL-запрос не включает эти параметры для фильтрации.
Мой код:
async getCategories() {
this.loading = true;
const { response } = await this.request('GET', '/get-other-categories', { params: { userId: this.activeUser.id, ...this.filters } });
this.categories = response.data;
this.expenses = this.categories.reduce((sum, item) => sum + item.total_expenses, 0);
this.loading = false;
},
async getIncomeCategory() {
this.loading = true;
const { response } = await this.request('GET', '/get-income-category', { params: { userId: this.activeUser.id, ...this.filters } });
this.incomeCategories = response.data;
this.income = this.incomeCategories.reduce((sum, item) => sum + item.total_income, 0);
this.loading = false;
},
filterRecordsByAccounts(accounts) {
const selectedAccounts = [...new Set(accounts)];
if (!selectedAccounts.length) {
this.filters.accounts = [];
this.getCategories();
this.getIncomeCategory();
return;
}
this.filters.accounts = selectedAccounts;
this.getCategories();
this.getIncomeCategory();
},
public function getIncomeCategory(Request $request): JsonResponse
{
$userId = $request->query('userId');
$startDate = $request->query('startDate');
$endDate = $request->query('endDate');
$accountIds = $request->query('accounts', []);
$categoryIds = $request->query('categories', []);
$labels = $request->query('labels', []);
$types = $request->query('types', []);
$paymentTypes = $request->query('paymentTypes', []);
$paymentStatuses = $request->query('paymentStatuses', []);
Log::info('received-parameters', [
'userId' => $userId,
'startDate' => $startDate,
'endDate' => $endDate,
'accountIds' => $accountIds,
'categoryIds' => $categoryIds,
'labels' => $labels,
'types' => $types,
'paymentTypes' => $paymentTypes,
'paymentStatuses' => $paymentStatuses,
]);
$query = Category::with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('visible', true)
->with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('user_id', $userId)
->where('visible', true)
->with(['records' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('user_id', $userId)
->whereBetween('date', [$startDate, $endDate])
->when($accountIds, function ($query, $accountIds) {
$query->whereIn('account_id', $accountIds);
})
->when($categoryIds, function ($query, $categoryIds) {
$query->whereIn('category_id', $categoryIds);
})
->whereHas('labels', function ($q) use ($labels) {
$q->whereIn('label_id', $labels);
})
->when($types, function ($query, $types) {
$query->whereIn('type', $types);
})
->when($paymentTypes, function ($query, $paymentTypes) {
$query->whereIn('payment_type', $paymentTypes);
})
->when($paymentStatuses, function ($query, $paymentStatuses) {
$query->whereIn('payment_status', $paymentStatuses);
});
}]);
}]);
}])
->whereNull('parent_id')
->where('visible', true)
->where(function ($query) {
$query->whereRaw("json_extract(name, '$.en') = 'Income'")
->orWhereRaw("json_extract(name, '$.lv') = 'Ienākumi'");
});
Log::info('query', [$query->toSql()]);
$categories = $query->get();
return response()->json(CategoryResource::collection($categories));
}
public function getOtherCategories(Request $request): JsonResponse
{
$userId = $request->query('userId');
$startDate = $request->query('startDate');
$endDate = $request->query('endDate');
$accountIds = $request->query('accounts', []);
$categoryIds = $request->query('categories', []);
$labels = $request->query('labels', []);
$types = $request->query('types', []);
$paymentTypes = $request->query('paymentTypes', []);
$paymentStatuses = $request->query('paymentStatuses', []);
Log::info('received-parameters', [
'userId' => $userId,
'startDate' => $startDate,
'endDate' => $endDate,
'accountIds' => $accountIds,
'categoryIds' => $categoryIds,
'labels' => $labels,
'types' => $types,
'paymentTypes' => $paymentTypes,
'paymentStatuses' => $paymentStatuses,
]);
$query = Category::with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('visible', true)
->with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('user_id', $userId)
->where('visible', true)
->with(['records' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
$query->where('user_id', $userId)
->whereBetween('date', [$startDate, $endDate])
->when($accountIds, function ($query, $accountIds) {
$query->whereIn('account_id', $accountIds);
})
->when($categoryIds, function ($query, $categoryIds) {
$query->whereIn('category_id', $categoryIds);
})
->whereHas('labels', function ($q) use ($labels) {
$q->whereIn('label_id', $labels);
})
->when($types, function ($query, $types) {
$query->whereIn('type', $types);
})
->when($paymentTypes, function ($query, $paymentTypes) {
$query->whereIn('payment_type', $paymentTypes);
})
->when($paymentStatuses, function ($query, $paymentStatuses) {
$query->whereIn('payment_status', $paymentStatuses);
});
}]);
}]);
}])
->whereNull('parent_id')
->where('visible', true)
->where(function ($query) {
$query->whereRaw("json_extract(name, '$.en') != 'Income'")
->orWhereRaw("json_extract(name, '$.lv') != 'Ienākumi'");
});
Log::info('query', [$query->toSql()]);
$categories = $query->get();
return response()->json(CategoryResource::collection($categories));
}
Журналы возвращают это, когда я пытаюсь фильтровать по банковскому счету:
[2024-07-05 02:44:21] local.INFO: received-parameters {"userId":"1","startDate":null,"endDate":null,"accountIds":["1"],"categoryIds":[],"labels":[],"types":[],"paymentTypes":[],"paymentStatuses":[]}
[2024-07-05 02:44:21] local.INFO: query ["select * from `categories` where `parent_id` is null and `visible` = ? and (json_extract(name, '$.en') != 'Income' or json_extract(name, '$.lv') != 'Ienākumi') and `categories`.`deleted_at` is null"]
[2024-07-05 02:44:24] local.INFO: received-parameters {"userId":"1","startDate":null,"endDate":null,"accountIds":["1"],"categoryIds":[],"labels":[],"types":[],"paymentTypes":[],"paymentStatuses":[]}
[2024-07-05 02:44:24] local.INFO: query ["select * from `categories` where `parent_id` is null and `visible` = ? and (json_extract(name, '$.en') = 'Income' or json_extract(name, '$.lv') = 'Ienākumi') and `categories`.`deleted_at` is null"]
Подробнее здесь: https://stackoverflow.com/questions/787 ... ect-values
