Laravel Search Functionality - Reference Second Model/Collection/Table

Question

I'm trying to implement search functionality within my blade for my Script model. It performs fine for everything related to searching directly within the Script collection/table. However, my users also will need to be able to enter in a Patient first_name or last_name and search for the script records within the Script table that belong to the Patient being searched for. These are connected with a hasMany/belongsTo relationship. Can someone assist me in figuring out how to reference a parent table to display current table results?

Models

Patient hasMany Script
Script belongsTo Patient          (patient_id)

Script Blade

{{ Form::text('search', $search, ['class' => 'form-control form-control-sm', 'placeholder' => 'Search Scripts...']) }}
{{Form::submit('Search', ['class' => 'btn btn-primary btn-sm'])}}

ScriptController

$search = $request->search;
$patients = Patient::all();
$scripts = Script::
    when($search, function ($query) use ($search) {
        $query->where(function ($query) use ($search) {
            $query
                ->where('prescribe_date', 'LIKE', '%' . $search . '%')
                ->orWhere('status', 'LIKE', '%' . $search . '%')
                ->orWhere('efax_reference', 'LIKE', '%' . $search . '%')
                ->orWhere('efax_confirmation', 'LIKE', '%' . $search . '%');
            });
        })
        ->paginate(25);

I'm not sure this is clear. Are you trying to show the patient name and lastname that the scripts found belong to? Or are you trying to show the scripts that belong to the patient found? — danboh
– danboh, Commented Feb 11, 2019 at 21:54
@danboh I am trying to show the scripts that belong to the patient found. — Xerakon
– Xerakon, Commented Feb 11, 2019 at 21:55

danboh · Accepted Answer · 2019-02-11 22:08:20Z

0

This is high level, but you could iterate through the found patient's script, assuming that you have a blade where you'd be handling the response:

$patient = Patient::where('first_name', 'LIKE', '%' . $search . '%')->orWhere('last_name', 'LIKE', '%' . $search . '%')->first();

return view('patient')->with(compact('patient'));

Then in the blade, go through the response:

@foreach ($patient->scripts as $script)
    {{ $script->name }}
@endforeach

Or if you're returning multiple patients then you could do:

@foreach ($patients as $patient)
    {{ $patient->name }}
    @foreach ($patient->scripts as $script)
        {{ $script->name }}
    @endforeach
@endforeach

edited Feb 11, 2019 at 22:08

answered Feb 11, 2019 at 22:03

danboh

7783 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danboh Over a year ago

@Leorent look at the comment above: "I am trying to show the scripts that belong to the patient found"

Leo · Accepted Answer · 2019-02-11 22:37:26Z

0

Essentially you need to eagerload Patient model in search with an orWhereHas like below:

$scripts = Script::
    when($search, function ($query) use ($search) {
    $query->where(function ($query) use ($search) {
        $query
            ->where('prescribe_date', 'LIKE', '%' . $search . '%')
            ->orWhere('status', 'LIKE', '%' . $search . '%')
            ->orWhere('efax_reference', 'LIKE', '%' . $search . '%')
            ->orWhere('efax_confirmation', 'LIKE', '%' . $search . '%');
    });
})
    ->orWhereHas('patients', function ($query) use ($search) {
        $query->where('first_name', 'like', '%' . $search . '%')
            ->orWhere('last_name', 'like', '%' . $search . '%');
    })
    ->paginate(25);

answered Feb 11, 2019 at 22:37

Leo

7,4105 gold badges28 silver badges48 bronze badges

1 Comment

Xerakon Over a year ago

I am getting the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'scripts.patients_id' in 'where clause' (SQL: select count(*) as aggregate from

scripts` where exists (select * from patients where scripts.patients_id = patients.id and (first_name like %% or last_name like %%)))` from that. When I try changing 'patients' to 'patient', it gives the error Call to undefined method App\Script::patient(). the column in scripts that links to patient is patient_id

Collectives™ on Stack Overflow

Laravel Search Functionality - Reference Second Model/Collection/Table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related