Laravel 5.5: Query Building with Join and Where Clause

Question

I have Two Tables

'Orders' with Columns 

['subtotal', 'taxes', 'delivery_fee', 'total', 'discount', 'status', 'delivery_status','payment_status', 'special_instructions', 'address_id', 'store_id', 'user_id', 'delivery_profile_id','payment_method_id', 'type', 'scheduled_on'];

And

'Stores' with Columns

['name', 'tagline', 'image_url', 'delivery_time', 'minimum_order','delivery_fee', 'details', 'delivery_limit', 'area', 'address', 'longitude', 'latitude', 'preorder','serves_non_veg', 'cost_for_two', 'status', 'owner_id', 'apibot', 'admin_id','admin_mobile', 'opens_at', 'closes_at'];

Iam running a Laravel 5.5 Framework and Having Tough time to build a Query. Below is my Complete Code fr returning the Order List according to requested Status from my Mobile API

        $orders = Order::where('store_id', Auth::user()->store->id);

        if($request->status) {
            $orders = $orders->where('status', $request->status);
        }

        // filter for active orders
        if($request->active_orders) {
            $orders = $orders->whereIn('status', ['new', 'accepted', 'preparing', 'dispatched']);
        }

        // for delivery tab in store app
        if($request->deliveries) {
            $orders = $orders->whereIn('status', ['accepted', 'preparing', 'dispatched'])
                ->where('delivery_profile_id', '<>', null)
                ->whereIn('delivery_status', ['allotted', 'started', 'complete']);
        }

In the Below Code I need to put a Join and Where Clause combination to Join Store with Orders

$orders = Order::where('store_id', Auth::user()->store->id);

For Example: General SQL Query as Follows

   Select * From Orders, Stores Where Stores.id=Orders.store_id and Stores.admin_id = 17

I tried with Possible solutions but not successful. Thank you in Advance.

thisiskelvin · Accepted Answer · 2019-03-20 17:10:20Z

1

You will first need to create a belongsTo on the Store model to create a relationship:

public function store() {
   return $this->belongsTo(\App\Store::class);
}

Then your query will look like:

$orders = Order::with(['store' => function($query) {
    return $query->where('admin_id', auth()->user->id)
}]);

edited Mar 20, 2019 at 17:10

answered Mar 20, 2019 at 16:37

thisiskelvin

4,2021 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

E2Get Over a year ago

Hi Sorry to Say its not Working I already Had public function store() { return $this->belongsTo('App\Models\Store', 'store_id'); } after your proposed changes My APi returns Error

E2Get Over a year ago

Hai Fixed the Error Auth() was the error changed to Auth::user()

thisiskelvin Over a year ago

Apologies. You should also be able to use the auth() helper. I will update the code.

E2Get Over a year ago

I have issue with FCM request for Push Notifications $this->pushNotifications[] = new PushNotification($this->order->store->user->fcm_registration_id, 'New Order', 'You have received new order 00'. $this->order->id , ["order_id" => $this->order->id]); How do I call admin_id based User->fcmRegistration?

Leonardo Pinho · Accepted Answer · 2019-03-20 19:57:37Z

0

See if it works like this for you:

$result = DB::table('Orders as o')
->join('Stores as s', 's.id', '=', 'o.store_id')
->where('s.admin_id', auth()->user->id)
->get();

edited Mar 20, 2019 at 19:57

answered Mar 20, 2019 at 16:38

Leonardo Pinho

114 bronze badges

1 Comment

E2Get Over a year ago

Not Working Sorry

E2Get · Accepted Answer · 2019-03-21 04:23:38Z

0

This code worked

$orders = Order::with(['store' => function($query) {
return $query->where('admin_id', auth()->user->id)}]);

New Query regarding the same Join and WHere Clause

$this->pushNotifications[] = new PushNotification($this->order->store->user->fcm_registration_id, 'New Order', 'New Order');

How do I call admin_id based User->fcmRegistration? I tried with $this->order->store->'admin_id'->user->fcm_registration_id had No Luck.

Kindly Help.

answered Mar 21, 2019 at 4:23

E2Get

234 bronze badges

Collectives™ on Stack Overflow

Laravel 5.5: Query Building with Join and Where Clause

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related