CODEWITHSUNDEEP
phplaravelsearchormeloquent
gyo
gyo

Reputation: 1701

Laravel search DB with multiple tables

I have a table named records with a user_id column which links to a users table to set ownership.

I can correctly filter the records by title with a search string:

$records->where('title', 'LIKE', '%'.$search.'%');

But I'd like to return also the results containing the users.firstname and users.lastname, this is my (awful) join attempt:

$records->join('users', 'users.id', '=', 'records.user_id')
        ->where('users.firstname', 'LIKE', '%'.$search.'%')
        ->orWhere('users.lastname', 'LIKE', '%'.$search.'%')
        ->orWhere('title', 'LIKE', '%'.$search.'%');

// SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous

Upvotes: 1

Views: 6075

Answers (3)

Emil Eremiev
Emil Eremiev

Reputation: 338

If I understand you want to return result from Records by filtering with $search and also want to show users info for this records. You can use Eloquent. Your model must be:
In User model:

public function records()
    {
        return $this->hasMany(Record::class);
    }

In Record model:

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

And in Controller:

   Record::where('title', 'LIKE', '%'.$search.'%')
           ->with('user')
           ->first();

Upvotes: 0

gyo
gyo

Reputation: 1701

While I wait for a better answer I found a solution which works but it's not optimal since it involves one extra query to gather the author user_id and use it subsequently to query the records:

// Get the author ID
$author = DB::table('users')
                ->select(DB::raw('CONCAT_WS(" ",`firstname`,`lastname`) as `fullname`,id'))
                ->having('fullname', 'LIKE', '%'.$search.'%')
                ->first();

// $author output:
stdClass Object
(
    [fullname] => John Doe
    [id] => 35
)

// Query the records using the gathered ID
$records->where('user_id', $author->id)
        ->orWhere('title', 'LIKE', '%'.$search.'%');

Problems with this solution: Apart from the extra query, if somebody searches for John Doe or Some Title the results are correct. But if searching for John Doe Some Title, nothing is shown because the author and the title can't be found.

Upvotes: 1

Jean Marcos
Jean Marcos

Reputation: 1177

You need to set that also use the search parameter in your inner query:

$records->join('users', function($join) use ($search)
{
    $join->on('users.id', '=', 'records.user_id')
         ->where('users.firstname', 'LIKE', '%'.$search.'%')
         ->orWhere('users.lastname', 'LIKE', '%'.$search.'%');
});

Upvotes: 0

Related Questions