Thanks this is Gonna be cool !

@MIS1st awww thank you so much I greatly appreciate it my friend. =)

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

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

thank you, I tried this one too there were no use

@dantegalindocruz You are Welcome ! hope you learn as fast as possible :D and make few good things

@MIS1st thank you so much for mentioning Youtube because I kept repeatedly getting a playback error for this episode and now I'm able to watch it on YouTube thanks to you!

Try this

1. 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)
    );
}

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

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

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

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