livewire optimize query structure (lower amount of queries)

@t0berius Try this

$transactions = OrderTransaction::with(['order:id'])
    ->whereHas('order', function ($query) use ($search) {
        $query
            ->where('currency', 1)
            ->when($search != '', function($qr) use ($search) {
                $qr->where(function($q) use ($search) {
                    $q
                        ->where('address', $search)
                        ->orWhere('paymentRef', $search)
                        ->when(is_numeric($search), function ($qry) use ($search) {
                            $qry->orWhere('id', $search);
                        });
                });
            })
        ;
    })
    ->latest();

@achatzi

I've modified my code to:

            $transactions = OrderTransaction::with(['order:id']);

            if($this->search !== '')
                //if we query for order transactions, we can search either for address, paymentRef or by ID
                $transactions->whereHas('order', function($query) use ($search) {
                        $query->currencyEUR()->where('address', $search);
                })
                //only attach search by id if entered value is numeric
                ->when(is_numeric($search), function ($query) use ($search) {
                    $query->orWhere('id', $search);
                })
                ->orWhere('paymentRef', $search);
            else
                $transactions->whereHas('order', function ($query) {
                    $query->currencyEUR();
                });

Is there any way to make this shorter into "one" block so I can exclude the else logic? Be aware, address is part of order relation, paymentRef and id are part of OrderTransaction itself.

Do you think this may be the way to go?

                $transactions->whereHas('order', function($query) use ($search) {
                    $query->currencyEUR()
                    ->when($search != '', function($query) use ($search) {
                        $query->where('address', $search);
                    });
                })
                //only attach search by id if entered value is numeric
                ->when(is_numeric($search), function ($query) use ($search) {
                    $query->orWhere('id', $search);
                })
                ->when($search != '', function($query) use ($search) {
                    $query->orWhere('paymentRef', $search);
                });

@t0berius Sorry, I missed that.

You need the transactions of the orders that have currency 1 and either of one of the other options/filters, correct? Then the other options/filters must be in a parenthesis in the sql. This is why I used this

$qr->where(function($q) use ($search) {
                    $q
                        ->where('address', $search)
                        ->orWhere('paymentRef', $search)
                        ->when(is_numeric($search), function ($qry) use ($search) {
                            $qry->orWhere('id', $search);
                        });
                });

I put all the filters in one where, so Eloquent will put it in parenthesis.

In your case, I can't see other way except using joins.

$transactions = OrderTransaction::query()
    ->join('orders', 'order_transactions.order_id', '=', 'orders.id')
    ->select('order_transactions.*')
    ->where('orders.currency', 1)
    ->when($search != '', function($query) use ($search) {
        $query->where(function($qr) use ($search) {
            $qr
                ->where('orders.address', $search)
                ->orWhere('order_transactions.paymentRef', $search)
                ->when(is_numeric($search), function ($q) use ($search) {
                    $q->orWhere('order_transactions.id', $search);
                });
        });
    });

If you use php8

$transactions = OrderTransaction::query()
    ->join('orders', 'order_transactions.order_id', '=', 'orders.id')
    ->select('order_transactions.*')
    ->where('orders.currency', 1)
    ->when(
        $search != '',
        fn($query) => $query->where(
            fn($qr) => $qr
                ->where('orders.address', $search)
                ->orWhere('order_transactions.paymentRef', $search)
                ->when(is_numeric($search), fn($q) => $q->orWhere('order_transactions.id', $search))
        )
    );

This way you don't have to add the use ($search) all the time.

This is translated to the following sql

SELECT
order_transactions.*
FROM
order_transactions
INNER JOIN orders ON order_transactions.order_id = orders.id
WHERE
orders.currency = 1 AND
(
	orders.address = 'search' OR
	order_transactions.paymentRef = 'search' OR
	order_transactions.id = search
)

It is important to put the address, paymentRef, id in parenthesis in order to search for the orders that have either one of these.

Since you only eager load the order id from the orders, I assume you do not need it or if you do you can get it from $transaction->order_id

Let me know if this works for you.

@achatzi

You need the transactions of the orders that have currency 1 and either of one of the other options/filters, correct? => Yes.

See my code, this should do the trick using eloquent:

            //use this to make sure we got records even if the search string is empty (initial page load)
           $transactions = OrderTransaction::with(['order:id']);

            $transactions->whereHas('order', function($query) use ($search) {
                $query->currencyEUR()
                ->when($search != '', function($query) use ($search) {
                    $query->where('address', $search);
                });
            })
            //only attach search by id if entered value is numeric
            ->when(is_numeric($search), function ($query) use ($search) {
                $query->orWhere('id', $search);
            })
            ->when($search != '', function($query) use ($search) {
                $query->orWhere('paymentRef', $search);
            });

Do you agree?

@t0berius No, this will not work. Your code translates in this sql

select * from `order_transactions` where exists (select * from `orders` where `order_transactions`.`order_id` = `orders`.`id` and `currency` = ? and `address` = ?) or `id` = ? or `paymentRef` = ?

So, if you type in your search 123, this will get you the order transactions that have an order with currency 1 AND address 123 OR all transactions that have id 123 OR paymentRef 123 even if the order that the transactions belongs to does not have a currency of 1.

Do you understand the difference? I hope I am making this clearer now.

@achatzi

I understand what you mean, thank you. I edited the query a bit, since I want to stick to eloquent:

            $transactions->whereHas('order', fn ($query) =>
                $query->currencyEUR()
                ->when($search != '', fn ($query) =>
                    $query->where(fn ($query) =>
                        $query->where('orders.address', $search)
                        ->when(is_numeric($search), fn ($query) =>
                            $query->orWhere('orders.id', $search)
                        )
                        ->orWhere('order_transactions.paymentRef', $search)
                    )
                )
             );

This seems to be valid to me.

@t0berius Sorry for the late reply. I don't know if you solved this yet but what you have posted should work.

Do a dd() in order to check the sql that is created

$transactions->whereHas('order', fn ($query) =>
                $query->currencyEUR()
                ->when($search != '', fn ($query) =>
                    $query->where(fn ($query) =>
                        $query->where('orders.address', $search)
                        ->when(is_numeric($search), fn ($query) =>
                            $query->orWhere('orders.id', $search)
                        )
                        ->orWhere('order_transactions.paymentRef', $search)
                    )
                )
             )->dd();

I believe you should get this

select * from `order_transactions` where exists (select * from `orders` where `order_transactions`.`order_id` = `orders`.`id` and `currency` = ? and (`address` = ? or `orders.id` = ? or `order_transactions.paymentRef` = ?))

You will have to do some testing to see which query is more expensive, the one using join or the one using where exists.