How to search through two tables in one query?

The current query, a user will have to have both doctors and friends.

If its either, then use orWhereHas for the second search

Somehow, If I dd($result), I only get User.php records.

If I dd($query) I get the Model related records, also for Doctor.

How can I add another layer to the methods? Because actually I only want friends and doctors ->where('accepted',1)?

$friends = Auth::user()->friends()->where('accepted',1)->orderBy('created_at', 'desc');
$doctors = Auth::user()->doctors()->where('accepted',1)->orderBy('created_at', 'desc');

and then add the $month logic, and concatenate the query to paginate them in one $result

So there is a difference where between whereHas and with.

whereHas only check if the relationship has some kind of condition, but it doesn't return it with make the check for the condition as well, but it also returns the given objects.

For your case you need with instead of whereHas. Not that you will still get two collections, one for doctors and one for friends.

@BOBBYBOUWMANN - Thank you. With the following logic, I get the records atm.

public function search(Request $request){

$month = $request->input('month'); 

$friends = Auth::user()->friends()->where('accepted',1)->orderBy('created_at', 'desc');
$result = $friends->where(DB::raw("CONCAT(date)"), 'LIKE', "%{$month}%")->whereMonth('date', '=', $month)->paginate(7);


return view ('search.result', compact('result'));

}

Is it then even possible to add the doctors logic and concatenate the query?

Here I can't seem to find the way.

 $result = Auth::user()->whereHas('doctors', function($query) use ($month) {
    $query->whereMonth('date', '=', $month);
  })->orWhereHas('friends', function($query) use ($month) {
    $query->whereMonth('date', '=', $month);
  })->paginate(10);

I am checking within the foreach loop on the type of Model, if doctor or friend to output model related stuff.

But I lack the way to concatenate the query, for the search, where I check on the month (which is working).

Please describe in plain words what you want?

@SNAPEY - Sorry for the confusion.

I am trying to create one Collection out of $result and $result2

public function search(Request $request){
 
$month = $request->input('month');  // Have a dropdown with Months, 01-12 as the search instance to check on friends and doctors.
 
$friends = Auth::user()->friends()->where('accepted',1)->orderBy('created_at', 'desc');
$doctors = Auth::user()->doctors()->where('accepted',1)->orderBy('created_at', 'desc');


$result = $friends->whereMonth('date', '=', $month)->paginate(7);

$result2 = $doctors->whereMonth('date', '=', $month)->paginate(7);


return view ('search.result', compact('result', 'result2));

}

I am lacking the knowledge to search through two tables, or in other words, define tables within search, rather then just calling DB::raw.

You posted that before and the intent was not clear, so I asked

Please describe in plain words what you want?

I don't want to see any code.

@SNAPEY - Sorry, again. As a logged in User, I want to search/filter through friends and doctors with the help of a dropdown to limit the result by a month-related record on both of my friends, and doctors. How can I see friends and doctors in one collection with the month, I searched for?

Considerations

a) will you list all friends and then all doctors or do you expect them to be mixed together?

b) how different are the details you need to list between friends and doctors?

c) do you need to search month and year (eg for one off dates) or month only (eg for anniversaries). I'm thinking you need to search for year also.

d) I don't understand what CONCAT(date) is doing

@SNAPEY - Thank you, for the patience.

a) I am checking within the foreach loop on the type of Model, if doctor or friend to output model related stuff.

b) Those are two models. Two tables. users and doctors. Both with a date column.

c) Search is already working. I just asked if it is possible to search through two tables (b) within one request, to then trigger (a)

d) A mistake, as I copied the default search logic from other tests.

You can obviously merge the collections, but I don't see how you would ever be able to paginate the results.

$friends = Auth::user()
        ->friends()
        ->where('accepted',1)
        ->whereMonth('date', '=', $request->month)
        ->get();

$result = $friends->merge(
            Auth::user()
            ->doctors()
            ->where('accepted',1)
            ->whereMonth('date', '=', $request->month)
            ->get()
        )
        ->sortBy('created_at','desc');

return view ('search.result', compact('result'));

For pagination, I think you would need to use joins to create one resultset.

@SNAPEY - Thank you. It worked. Also for always coming back in patience. @snapey

Incase you want to go back to two separately paginated lists;

http://novate.co.uk/using-multiple-pagination-links-on-one-page/

@SNAPEY - Thank you, already used this pagination logic. (5, ['*'], 'test')