CODEWITHSUNDEEP
mysqllaravellaravel-5
Md Sabbir Ahmed
Md Sabbir Ahmed

Reputation: 31

Laravel ORM + Raw Query Table alias problem

This is my base query:

 $base_query = TableOne::join('table_two as p1', 'p1.order_id', '=', 'table_ones.id')
               ->join('table_threes as j1', 'p1.id', '=', 'j1.partner_order_id')
               ->select('table_ones.*')
               ->groupBy('table_ones.id', 'j1.status');

When someone need to filter some data like partner_id on table_two table, we add some extra column like this,

$base_query->where(function ($query) {
                   $query->whereNull('p1.cancelled_at');
                   $query->orWhere('p1.cancelled_at', '=', DB::select(DB::raw("SELECT MAX(p2.cancelled_at) FROM partner_orders p2 WHERE p2.order_id = p1.order_id")));
                   $query->whereNotExists(function ($query) {
                       DB::select(DB::raw("SELECT * FROM partner_orders p3 WHERE p3.order_id = p1.order_id AND p3.cancelled_at IS NULL"));
                   });
               });

But after run this query, their is an error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p1.order_id' in 'where clause' (SQL: SELECT MAX(p2.cancelled_at) FROM partner_orders p2 WHERE p2.order_id = p1.order_id)

i think, their is some issue on that query.

$base_query->where(function ($query) {
    $query->whereNull('p1.cancelled_at');
    $query->orWhere('p1.cancelled_at', '=', DB::select(DB::raw("SELECT MAX(p2.cancelled_at) FROM partner_orders p2 WHERE p2.order_id = p1.order_id")));
    $query->whereNotExists(function ($query) {
        DB::select(DB::raw("SELECT * FROM partner_orders p3 WHERE
            p3.order_id = p1.order_id AND p3.cancelled_at IS NULL"));
        });
    });

`

Upvotes: 1

Views: 232

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

DB::select() directly executes the query.

In the case of orWhere(), only use a raw expression.

$query->orWhere('p1.cancelled_at', '=', DB::raw("(SELECT MAX(p2.cancelled_at) [...])"));

In the case of whereNotExists(), use whereRaw():

$query->whereRaw("NOT EXISTS(SELECT * [...])");

In both cases, you can also use a closure and build the query manually:

$query->orWhere('p1.cancelled_at', '=', function($query) {
    $query->from('partner_orders')->select([...])->where([...]);
})

$query->whereNotExists(function($query) {
    $query->from('partner_orders as p3')->where([...]);
})

Upvotes: 1

Related Questions