CODEWITHSUNDEEP
phplaravellaravel-5.8
Owdenpk
Owdenpk

Reputation: 97

Search Data from multiple tables laravel

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

Upvotes: 8

Views: 14079

Answers (4)

Haroon Mirza
Haroon Mirza

Reputation: 267

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.

Upvotes: 0

Amit Senjaliya
Amit Senjaliya

Reputation: 2945

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

Upvotes: 2

ManojKiran
ManojKiran

Reputation: 6341

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

Upvotes: 5

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

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

Upvotes: 2

Related Questions