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

Answer 1

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

Answer 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();

Answer 3

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();