CODEWITHSUNDEEP
phpmysqldatabaselaravelrelation
Guilhem V
Guilhem V

Reputation: 381

Laravel | Search in 2 tables

I'm having a problem with my Search functionnality on my website, I have 2 tables: user and review , In my review table, the owner column is equal to the username column in user table, I want to be able to return in the same result the username of the user table and just below the number of review which I can get with:

Review::where('owner', '=', xxx)->where('invitation_id', '')->count();

The xxx should be equal to the username in the user table
And I have to do this to get the username:

User::where('username', '=', xxx)->first();

What I would like to do (I know this is wrong):

$result = User::where('email','LIKE','%'.$search_key.'%')
        ->orWhere('username','LIKE','%'.$search_key.'%')
        AND
        Review::where('username', '=',  *$result->username* )
        ->get();

And I would like to be able to return the search result like this in my result.blade.php:

<h3>Username: {{ user->username }}</h3>
<h3>Username: {{ review->number_review }}</h3>

I checked on the Laravel docs to make a relationship between these 2 tables but can't figure it out, I hope what I said is understandable.

Upvotes: 0

Views: 2221

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You achieve the required matching criteria by using join and parameter grouping clause

$result = DB::table('users as u')
            ->join('review as r', 'u.username', '=', 'r.owner')
            ->where('email','LIKE','%'.$search_key.'%')
            ->orWhere(function ($query) {
                $query->where('u.username','LIKE','%'.$search_key.'%')
                      ->where('r.owner','LIKE','%'.$search_key.'%');
            })
        ->get();

Which will produce where clause as 

WHERE u.email LIKE '%somevalue%' OR (r.owner LIKE '%somevalue%' AND u.username LIKE '%somevalue%')

For review count 

$result = DB::table('users as u')
            ->select('u.*',DB::raw("COUNT(*) as review_count"))
            ->join('review as r', 'u.username', '=', 'r.owner')
            ->where('u.email','LIKE','%'.$search_key.'%')
            ->orWhere(function ($query) {
                $query->where('u.username','LIKE','%'.$search_key.'%')
                      ->where('r.owner','LIKE','%'.$search_key.'%');
            })
            ->groupBy('u.username')
        ->get();

Upvotes: 1

FGDeveloper
FGDeveloper

Reputation: 1050

You can use eloquent relationship.

// app/Review.php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Review extends Model
{

    public function users()
    {
        return $this->hasOne('App\User', 'owner', 'username');
    }
}

I do not suggest two table relation with username/owner. I suggest to you relation with user primary_id

You can get user info with following code;

Review::where('owner', '=', xxx)->where('invitation_id', '')->with('users')->count();

It getting user info with ->with('users') condition in Review model.

Upvotes: 1

meda
meda

Reputation: 45490

You will need to join your user table to the review table.

Something along these lines, might need tweaking.

$result = User::query()
            ->join('review', 'owner', 'username')
            ->where('email','LIKE','%'.$search_key.'%')
            ->orWhere('username','LIKE','%'.$search_key.'%')
            ->orWhere('username', $result->username)
            ->orWhere('owner', $result->username)
            ->get();

Upvotes: 0

Related Questions