CODEWITHSUNDEEP
phphtmllaravel
Amartya Barua
Amartya Barua

Reputation: 155

Database Query Syntax

Solution: I used db facade, DB::select() to run raw sql query.

I have two tables users(id, username, city, country) and friends(id, user_id, friend_id, status). The 'users' table stores the user information and 'friends' table store the currently logged in user's id, friend_id is the id of the user who sent a friend request to the current user and status will be 'received'. I want to show information of the users to sent requests to the current user. Is my database query syntax correct? I can see correct output if I don't use the nested subquery. Thanks in advance!

My DB query:

$friendrequests = DB::table('users')->select(['username','city','country'])->where('id',DB::table('friends')->select('friend_id')->where([['status','received'],['user_id',$user->id]]));

My html:

@extends('layouts.master')

@section('title')
    Friend Requests
@endsection

@section('content')
    @include('includes.message-block')
    <section class="row requests">
        <div class="col-md-6 col-md-offset-3">
            <header><h3>Friend Requests</h3></header>
            <div class="requestlist">
                <header><h2>You have request from:</h2></header>
                @foreach($users as $user)
                    <p>Name: {{ $user->username }}<br />City: {{ $user->city }}<br />Country:{{ $user->country }}</p>
                    <div class="interaction">
                        //stuff here
                    </div>
                @endforeach
                <a href="{{ URL::previous() }}">Back</a>
            </div>
        </div>
    </section>
@endsection

The route:

Route::get('/friendrequests',[
        'uses' => 'FriendController@getFriendRequests',
        'as' => 'friendrequests',
        'middleware' => 'auth'
    ]);

The controller (works if I don't use the nested subquery):

public function getFriendRequests()
    {
        $user = Auth::user();
        $friendrequests = DB::table('users')
        ->select(['username','city','country'])
        ->where('id',
            DB::table('friends')->select('friend_id')->where([['status','received'],['user_id',$user->id]])->get())->get();

        return view('friendrequests',['users' => $friendrequests]);
    }

Upvotes: 1

Views: 66

Answers (2)

Jasmel Singh
Jasmel Singh

Reputation: 111

Please try this may be helpful

$friendrequests = DB::table('users')
    ->select(['username','city','country'])
    ->whereIn('id', function($query) use($user) {
        $query->select('friend_id')
        ->from('friends')
        ->where('status', 'received')
        ->where('user_id', $user->id);
    })->get();

Upvotes: 0

nahuelhds
nahuelhds

Reputation: 532

You can solve this problem with the approach of "hasManyThrough". In your User's model you should do something like this:

class User extends Model{

  //... your code here

  public function possibleFriends(){
    $this->hasManyThrough(User::class, Friend::class, 'friend_id', 'id')->wherePivot('status', 'received');
  }

}

More details in the official docs.

Upvotes: 0

Related Questions