Level 73
Do you have more than one id for the same invoice?
I would suggest doing ->toSql() on the query and then run explain on it.
Jan 18, 2026
10
Level 4
Best query Distinct Approch
hello every one i have done this
public function index(TenantBranchCustomerMoneyExchangeFilter $filter): JsonResponse
{
$moneyExchange = TenantBranchCustomerMoneyExchange::query()
->whereIn('id', function ($q) {
$q->selectRaw('MAX(id)')
->from('tenant_branch_customer_money_exchanges')
->groupBy('invoice_number');
})
->filter($filter)
->paginate($this->getPaginatedSize(request('paginate')));
return $this->succeed(
TenantBranchCustomerMoneyExchangeApiResource::collection($moneyExchange)
);
}
this takes a lot of time to load like 30 sec while i have 1m row in mysql while removing the whereIn it will become like 1 sec what is the best way to distinct according to invoice_number thank you all for your time
Level 4
yes , invoice number is not unique so its possible having more than one id doing DB::enableQueryLog(); and collect(DB::getQueryLog()) ->groupBy('query') ->map(function ($queries, $sql) { return [ 'query' => $sql, 'count' => $queries->count(), 'total_time' => $queries->sum('time'), 'avg_time' => round($queries->avg('time'), 2), ]; }) ->sortByDesc('total_time') ->values() ->dd();
so that i know the query i get
array:11 [ // vendor\laravel\framework\src\Illuminate\Collections\Traits\EnumeratesValues.php:246
0 => array:4 [
"query" => "select count() as aggregate from tenant_branch_customer_money_exchanges where id in (select MAX(id) from tenant_branch_customer_money_exchanges group by invoice_number) and tenant_branch_customer_money_exchanges.tenant_plant_id = ?"
"count" => 1
"total_time" => 51806.61
"avg_time" => 51806.61
]
1 => array:4 [
"query" => "select * from tenant_branch_customer_money_exchanges where id in (select MAX(id) from tenant_branch_customer_money_exchanges group by invoice_number) and tenant_branch_customer_money_exchanges.tenant_plant_id = ? limit 5 offset 0"
"count" => 1
"total_time" => 26983.92
"avg_time" => 26983.92
]
2 => array:4 [
"query" => "select purchase_price from tenant_plant_currency_prices where tenant_plant_currency_prices.tenant_plant_currency_id = ? and tenant_plant_currency_prices.tenant_plant_currency_id is not null and is_deleted = ? and tenant_plant_currency_prices.tenant_plant_id = ? order by id desc limit 1"
"count" => 20
"total_time" => 63.24
"avg_time" => 3.16
]
3 => array:4 [
"query" => "select count() as aggregate from tenant_branch_customer_money_exchanges where is_deleted = ? and invoice_number = ? and tenant_branch_customer_money_exchanges.tenant_plant_id = ?"
"count" => 5
"total_time" => 44.49
"avg_time" => 8.9
]
4 => array:4 [
"query" => "select * from tenant_plant_currencies where tenant_plant_id = ? and system_currency_id = ? and is_deleted = ? and status = ? and tenant_plant_currencies.tenant_plant_id = ? order by id desc limit 1"
"count" => 10
"total_time" => 34.86
"avg_time" => 3.49
]
5 => array:4 [
"query" => "select * from tenant_plant_currency_prices where tenant_plant_currency_prices.tenant_plant_currency_id = ? and tenant_plant_currency_prices.tenant_plant_currency_id is not null and is_deleted = ? and tenant_plant_currency_prices.tenant_plant_id = ? order by id desc limit 1"
"count" => 10
"total_time" => 34.13
"avg_time" => 3.41
]
showing that quick explanation i convert currencies i have main currency $ with id 1 so if its from $ or to $ its direct converting but if its like pound to euro i have to convert pound to $ then $ to euro so that will be come 2 row same invoice number but i want to show only one of the row
Level 30
Try to add index to tenant_branch_customer_money_exchanges table by invoice_number column.
Level 4
thank you i actually have tried indexing invoice_number it actually helped a big time but i am still getting
"query" => "select count(*) as aggregate from `tenant_branch_customer_money_exchanges` where `id` in (select MAX(id) from `tenant_branch_customer_money_exchanges` group by `invoice_number`) and `tenant_branch_customer_money_exchanges`.`tenant_plant_id` = ?"
"count" => 1
"total_time" => 13457.56
"avg_time" => 13457.56
]
1 => array:4 [
"query" => "select * from `tenant_branch_customer_money_exchanges` where `id` in (select MAX(id) from `tenant_branch_customer_money_exchanges` group by `invoice_number`) and `tenant_branch_customer_money_exchanges`.`tenant_plant_id` = ? limit 5 offset 0"
"count" => 1
"total_time" => 6100.57
"avg_time" => 6100.57
]
that is 20 seconds
Level 30
Can you describe what data you're trying to extract? I mean logically, not in sql statements.
Level 75
Curious, how is an invoice number not unique?
1 like
Level 4
i am converting money so i have main currency if its from or to the main currency i store one record one invoice number but sometime its 2 currency neither of them are main currency so i have to change first currency to main then main to the other currency i hope its clear
Level 11
Try this
- Add Database Index
Schema::table('tenant_branch_customer_money_exchanges', function (Blueprint $table) {
$table->index(['invoice_number', 'id']);
});
public function index(TenantBranchCustomerMoneyExchangeFilter $filter): JsonResponse
{
$moneyExchange = TenantBranchCustomerMoneyExchange::query()
->join(DB::raw('(SELECT invoice_number, MAX(id) as max_id
FROM tenant_branch_customer_money_exchanges
GROUP BY invoice_number) as latest'), function($join) {
$join->on('tenant_branch_customer_money_exchanges.id', '=', 'latest.max_id');
})
->filter($filter)
->paginate($this->getPaginatedSize(request('paginate')));
return $this->succeed(
TenantBranchCustomerMoneyExchangeApiResource::collection($moneyExchange)
);
}
or try this
use Illuminate\Support\Facades\DB;
public function index(TenantBranchCustomerMoneyExchangeFilter $filter): JsonResponse
{
// 1. Define the subquery to get the latest IDs
$latestIds = DB::table('tenant_branch_customer_money_exchanges')
->selectRaw('MAX(id) as max_id') // We only need the ID
->groupBy('invoice_number');
// 2. Perform the join using the subquery
$moneyExchange = TenantBranchCustomerMoneyExchange::query()
->joinSub($latestIds, 'latest_exchanges', function ($join) {
$join->on('tenant_branch_customer_money_exchanges.id', '=', 'latest_exchanges.max_id');
})
->filter($filter) // This is now safe from ambiguous column errors
->paginate($this->getPaginatedSize(request('paginate')));
return $this->succeed(
TenantBranchCustomerMoneyExchangeApiResource::collection($moneyExchange)
);
}
1 like
Level 4
thank you, I tried this one too there were no use
Level 4
Thank you all actually there were no way around it that i am aware of i know invoice_number usually is unique but this was a special case that sometimes i had 2 record that have same invoice_number the best way was to add a column is_main that by default is true when i have 2 record with same invoice_number i will make the second one is_main =0 so now i can filter by is_main = 1
Please or to participate in this conversation.