Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 weeks ago.
0

Hi All!,

in found out how to querying in subquery, but my mutator doesn't work att all, here is my query builder code :

        $test = DB::table('tb_orders')
            ->select(DB::raw('order_id,
                                order_date,
                                order_no,
                                order_status,
                                order_ori_region,
                                order_ori_address,
                                order_ori_postcode,
                                order_dest_name,
                                order_dest_region,
                                order_dest_address,
                                order_dest_postcode,
                                order_dest_contact,
                                order_add_info,
                                order_price,
                                order_tax,
                                order_surcharge,
                                order_surtrans,
                                order_total,
                                tb_orders.client_id,
                                tb_clients.client_name,
                                truck_id,
                                agent_id,
                                payment_method,
                                payment_code,
                                created_date,
                                surrogate_key,
                                IF(surrogate_key != "", surrogate_key, order_no) AS skey'))
            ->leftJoin('tb_clients', 'tb_orders.client_id', '=', 'tb_clients.client_id')
            ->whereNotNull('order_id')
            ->whereRaw('agent_id =' . $agent->agent_id)
            ->whereRaw('order_id IN (1,2,3)')
            ->toSql();

        $pickup = DB::table(DB::raw('(' . $test . ')  as tab1'))
            ->select(DB::raw('*,COUNT(skey) as sk'))
            ->groupBy('skey')
            ->paginate($limit);

i have a mutator in my tb_orders model, but it doen't work if i querying like this. here is my model :

  public function getOrderOriAddressAttribute($value)
    {
        return json_decode(str_replace('~', '"', $value));
    }

    public function getOrderDestAddressAttribute($value)
    {
        return json_decode(str_replace('~', '"', $value));
    }

    public function getOrderStatusAttribute($value)
    {
        return $this->__statusEnum($value);
    }

    private function __statusEnum($status)
    {
        switch ($status) {
            case 0:
                return 'pending';
                break;
            case 1:
                return 'waiting';
                break;
            case 2:
                return 'Pick-up Assigned';
                break;
            case 3:
                return 'Picked-Up';
                break;
            case 4:
                return 'Transferred To Truck';
                break;
            case 5:
                return 'Delivery Assigned';
                break;
            case 6:
                return 'Delivery In Progress';
                break;
            case 7:
                return 'Delivered';
                break;
            case 8:
                return 'Cancelled';
                break;
            case 9:
                return 'Removed';
                break;
            default:
                return 'pending';
        }
    }

hope somebody can help me., thanks in advance

Regards

0

Hi All... at last, i found out how to show the query result with mutator's result though it is a subquery.. LOL here is my query :

$pickup = orders::select(DB::raw('*,COUNT(skey) as sk'))
            ->from(DB::raw('(SELECT a.*
                             ,IF(surrogate_key="",order_no,surrogate_key) as skey
                             ,b.client_name
                             FROM tb_orders a
                             LEFT JOIN tb_clients b ON a.client_id=b.client_name
                             WHERE order_status IN (1,2,3)
                             AND agent_id=' . $agent->agent_id . ') as tab1'))
            ->groupBy('skey')
            ->orderBy('order_id', 'desc')
            ->paginate($limit);

        $data = $pickup->toArray();

at first i should write this :

orders::

although it doesn't really mean source of this query is from "orders" table, because the source of my query shall be from this line:

 ->from()

i am so glad.. :) after few days of struggle i finally figure it out..

Last updated 6 years ago.
0

Sign in to participate in this thread!

Cloudways

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

The Laravel portal for problem solving, knowledge sharing and community building.

© 2022 Laravel.io - All rights reserved.