Search Data from multiple tables laravel

Question

I am trying to search multiple data from two related tables. To be specific I want to get only "name column" from the users table and the rest of the columns from the posts table. But whenever I tried to search it prints the following error "Trying to get property 'name' of non-object"

Below is my user model

<?php

namespace App;

use App\Mail\NewUserWelcomeMail;
use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Support\Facades\Mail;

class User extends Authenticatable
{
    use Notifiable;

    protected $fillable = [
        'name', 'email','phone', 'username', 'password', 
        'admin', 'address', 'description', 'approved_at',
    ];

    protected $hidden = [
        'password', 'remember_token',
    ];

    public function posts()
    {
        return $this->hasMany(Post::class)->orderBy('created_at', 'DESC');
    }
}

And post model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $guarded = [];

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

And my Controller

public function showcampaign(User $user) {
    $q = Input::get( 'q' );

    if( !empty( $q ) ) {
        $showcampaign = User::join('posts','posts.user_id','users.id')
            ->where('name','LIKE','%'.$q.'%')
            ->orWhere('caption','LIKE','%'.$q.'%')
            ->orWhere('description','LIKE','%'.$q.'%')
            ->orWhere('duration','LIKE','%'.$q.'%')
            ->orWhere('amount','LIKE','%'.$q.'%')
            ->get();

        if(count($showcampaign) > 0) {
            return view('admin.campaignreport', ['show' => $showcampaign]);
        } else { 
            return redirect('/campaignreport')->with('status', 'No Details found. Try to search again !');
        }
    } else { 
        $showcampaign = Post::all();

        return view('admin.campaignreport')->with('show', $showcampaign);
    }
}

Please help thanks

@Owdenpk You need to use `table_name.column_name` syntax in the where query. — Amit Senjaliya, Nov 25 '19 at 10:07
from the error it seems you are getting this error on the view file but not in the controller. right?? — zahid hasan emon, Nov 25 '19 at 10:07
Thanks very much @zahidhasanemon now i get a clue where the problem is. i checked my view and find out i was calling name with @foreach($show as $show) {{$show->user->name}} instead of $show->name. — Owdenpk, Nov 25 '19 at 11:18

score 5 · Answer 1 · answered Nov 25 '19 at 10:15

5

As you have already declared the relations within the Model So you can use whereHas and also orWhereHas

So

$showcampaign = SampleReception::query()
            ->whereHas('posts',function(\Illuminate\Database\Eloquent\Builder $query) use ($q){
                return $query->where('caption', 'LIKE','%'.$q.'%')
                ->orWhere('description', 'LIKE','%'.$q.'%')
                ->orWhere('duration', 'LIKE','%'.$q.'%') 
                ->orWhere('amount', 'LIKE','%'.$q.'%');
            })
            ->orWhere('name','LIKE','%'.$q.'%')
            ->get();

For any issues leave a comment

answered Nov 25 '19 at 10:15

ManojKiran A

5,896
4
30
43

it still prints the same error "Trying to get property 'name' of non-object" – Owdenpk Nov 25 '19 at 10:47
can you post the table structure of posts and users table – ManojKiran A Nov 25 '19 at 11:12

score 2 · Answer 2 · answered Nov 25 '19 at 09:50

2

Try.. use where instead of orwhere

$showcampaign = User::join('posts','posts.user_id','users.id')
                ->where('name','LIKE','%'.$q.'%')
                ->Where('caption','LIKE','%'.$q.'%')
                ->Where('description','LIKE','%'.$q.'%')
                ->Where('duration','LIKE','%'.$q.'%') 
                ->Where('amount','LIKE','%'.$q.'%')->get();

answered Nov 25 '19 at 09:50

VIKAS KATARIYA

5,867
3
17
34

please mark as a helpful if you get your answer THANK YOU @Owdenpk – VIKAS KATARIYA Nov 25 '19 at 09:54
1

where and orwhere has nothing to do with the error message – ManojKiran A Nov 25 '19 at 09:54
1

if I tried to replace orWhere with where, it does not match any information from both tables even if with an existing records - @VikasKatariya – Owdenpk Nov 25 '19 at 10:00

Amit Senjaliya · Answer 3 · 2019-11-25T10:43:11.307

I think you need to use a reference table for where clause.

$showcampaign = User::join('posts','posts.user_id', '=', 'users.id')
            ->where('users.name','LIKE', '%'.$q.'%')
            ->orWhere('posts.caption', 'LIKE','%'.$q.'%')
            ->orWhere('posts.description', 'LIKE','%'.$q.'%')
            ->orWhere('posts.duration', 'LIKE','%'.$q.'%') 
            ->orWhere('posts.amount', 'LIKE','%'.$q.'%')
            ->get();

If you define relationship correctly then use:

$showcampaign = SampleReception::with(['posts' => function($query) use($q) {
            return $query->where('caption', 'LIKE','%'.$q.'%')
            ->orWhere('description', 'LIKE','%'.$q.'%')
            ->orWhere('duration', 'LIKE','%'.$q.'%') 
            ->orWhere('amount', 'LIKE','%'.$q.'%');
        }])
        ->orWhere('name','LIKE','%'.$q.'%')
        ->get();

Thanks @Amit senjaliya. I have tried adding references as you recommended and the error has gone, but now it can match any data from both tables. Even if i try to search an existing record, it can't match — Owdenpk, Nov 25 '19 at 10:37

Haroon Mirza · Answer 4 · 2022-02-09T08:51:26.907

I use scope query to create search functions. It makes my code more flexible and easy to understand. Here is how you can do this.

In your "user" Model, you have to write "scopeFunctionName". you can write any functionName with scope. And in your Controller, you can simply call this function whenever you need as functionName(). and pass here your search term from request as functionName(request(['search']))

And now as you have declared relations for these tables to search data from related tables, try using this code in your user Model

public function scopeFilter($query, array $filters)
{
    /* 
    Hey $query! whenever u get 'search term' in URL, call the function.
    If ['search'] exists or != null, then pass its value to the function
    else return false and don't execute the $query. If value exists,
    Then the function will execute the query and returns a result.
    */
    $query->when(
        $filters['search'] ?? false,
        fn ($query, $search) =>
        $query
            ->whereHas('users', function ($query) use ($search) {
                return $query
                    ->where('name', 'like', '%' . $search . '%')
            })
            ->where('name', 'like', '%' . $search . '%')
            ->orWhere('caption', 'like', '%', . $search . '%')
            ->orWhere('description', 'like', '%', . $search . '%')
            ->orWhere('id', $search)
    );
}

And in Controller, you can pass the value to the scope function as:

public function index()
{
    return view('posts.index', [
        'show' => Post::all()->filter(request(['search']))->with('user')
    ]);
}

Note: The code above has not been tested but it might solve your problem.

Search Data from multiple tables laravel

4 Answers4