Access column name into another relational query function

I have a scenario where I need to check the column from two different table (code snippets below) into the additional query. But payment_methods.last_activated is not accessible into the closure. My requirements are to fetch all payment methods as well as all the payment which has been made with that method after it has been activated. How can I do it? Below code is not working.

$query = PaymentMethod::query()
            ->with(['option.country'])
            ->with('payments', function ($query) {
                $query->where('status', 'success')
                    ->whereColumn('processed_at', '>=', 'payment_methods.last_activated');
            })
            ->get();

It is returning

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'payment_methods.last_activated' in 'where clause' (Connection: mysql, SQL: select * from `payments` where `payments`.`payment_method_id` in (3, 4) and `status` = success and `processed_at` >= `payment_methods`.`last_activated`)

Thanks in advanced ☺

tykus

1 year ago

Best Answer

Level 104

You need a JOIN but eager-loading doesn't provide it by default.

$query = PaymentMethod::query()
            ->with(['option.country'])
            ->with('payments', function ($query) {
                $query->join('payment_methods', 'payment_methods.id', 'payments. payment_method_id')
                    ->where('status', 'success')
                    ->whereColumn('processed_at', '>=', 'payment_methods.last_activated');
            })
            ->get();

pranaycb

1 year ago

Level 1

@tykus Thank you sir. It works. But needed to do some little changes.

$query = PaymentMethod::query()
            ->with(['option.country'])
            ->with('payments', function ($query) {
                $query->join('payment_methods', 'payment_methods.id', 'payments.payment_method_id')
                    ->where('payments.status', 'success')
                    ->whereColumn('payments.processed_at', '>=', 'payment_methods.last_activated');
            });

Thanks a lot. 🥰

tykus

1 year ago

Level 104

@pranaycb no worries; please mark the best reply above.

Aside, I don't think the processed_at column needs to be fully qualified unless there is a similarly named column on payment_methods

Please or to participate in this conversation.