CODEWITHSUNDEEP
mysqllaravelquery-builder
Paul Gwamanda
Paul Gwamanda

Reputation: 95

How to get data from related tables in laravel 5?

I have a search form on my page that searches through several tables, how do i do the below and search correctly? I want to be able to search the agency name, client name and type name along with username and password:

my view looks like below

@foreach ($accounts as $account)
                <tr>
                    <td> {{$account->client->name}} </td>
                    <td> {{$account->agency->name}} </td>
                    <td> {{$account->type->name}} </td>
                    <td> {{$account->username}} </td>
                    <td> {{$account->password}} </td>
                    <td><a href="{{route('accounts.edit',$account->id)}}"><span class="fa fa-edit"></span></a></td>
                </tr>
                @endforeach

Username and password searches correctly but the joins dont bring any results

    public function index(){

    $search = \Request::get('search');

    $accounts =
        Account::where('clients.name','like','%'.$search.'%')
        ->orWhere('agencies.name','like','%'.$search.'%')
        ->orWhere('types.name','like','%'.$search.'%')
        ->orWhere('username','like','%'.$search.'%')
        ->orWhere('password','like','%'.$search.'%')
        ->Join('types', 'accounts.id', '=', 'accounts.type_id')
        ->Join('agencies', 'accounts.id', '=', 'accounts.agency_id')
        ->Join('clients', 'accounts.id', '=', 'accounts.client_id')
        ->paginate(20);

      return view('accounts',compact('accounts'));
    }

UPDATE:: This worked: 

       ->Join('types', 'accounts.type_id', '=', 'types.id')
       ->Join('agencies', 'accounts.agency_id', '=', 'agencies.id')
       ->Join('clients', 'accounts.client_id', '=', 'clients.id')

Upvotes: 0

Views: 45

Answers (1)

Perdeep Singh
Perdeep Singh

Reputation: 492

Your join seems to be wrong

->Join('types', 'accounts.id', '=', 'accounts.type_id')
->Join('agencies', 'accounts.id', '=', 'accounts.agency_id')
->Join('clients', 'accounts.id', '=', 'accounts.client_id')

see how on both side of '=' you using accounts table you need to have one side to have types/agencies/clients for join to work.

So it would be something like

->Join('types', 'accounts.id', '=', 'types.type_id')
->Join('agencies', 'accounts.id', '=', 'agencies.agency_id')
->Join('clients', 'accounts.id', '=', 'clients.client_id')

saying that would need your db structure to pin point why you not getting result from join query search.

Upvotes: 1

Related Questions