How to use LIKE operator with AND operator in a query with Laravel

Question

I am a newbe about Laravel. In the View I have a form with a dropdown menu that have many participants. When the form is submitted the value of the participant (for example "7") goes to the controller, in which there is a query that should retrieve from the DB all the rows that have "7" in the column "id_participants". Unfortunately the column "id_participants" has strings in this format "3;8;65;4;34".

I tried in many ways to solve this problem, but I was only able to retrieve the rows that have a record equals to "7".

public function fetch_table(Request $request)
{
    $this->validate($request, [
        'participants' => 'required',
        'start_date' => 'required|date',
        'end_date' => 'required|date',
        'rooms' => 'required',
    ]);

    $start_date = $request['start_date'];
    $end_date = $request['end_date'];
    $participants = $request['participants'];
    $room = $request['rooms'];

    $rooms = Room::all();
    $users = User::all()->where('is_active', '1')->sortBy('surname'); 
    $meetings = Meeting::all()
    ->where('is_active', '1')
    ->where('id_room', $room)
    ->where('date', '<', $end_date)
    ->where('date', '>', $start_date)
    ->where('id_participants', $participants); //look at here

    return view('reportArea', compact('users','rooms','meetings'));
}

So finally I should be able to do a similar query: SELECT * FROM meetings WHERE id_participants like "%;7" or id_participants like "7;%" or id_participants like "%;7;%" or id_participants = 7. Obviously 7 is only an example... the query should be dynamic. Is anyone able to help me?

UPDATE:

I solved the issue after many attempts substituting the "meetings" query with the following:

$meetings = DB::table('meetings')
            ->where('is_active', '1')
            ->where('id_room', $room)
            ->where('date', '>', $start_date)
            ->where('date', '<', $end_date)
            ->where(function ($query) use($participants) {
              $query->where('id_participants', $participants)
                    ->orWhere('id_participants', 'like', '%;'.$participants)
                    ->orWhere('id_participants', 'like', $participants.';%')
                    ->orWhere('id_participants', 'like', '%;'.$participants.';%');
            })
            ->get();

@elias No because in a meeting could participate many participants, not only one. I cannot imagine another data format. — Michele Della Mea
– Michele Della Mea, Commented Oct 30, 2019 at 0:40
Create a second table for storing participants. Use laravel relationships properly and you will avoid a lot of code. — Elias Soares
– Elias Soares, Commented Oct 30, 2019 at 0:45
You really should be using a many to many relationship. In general avoid adding arrays, objects etc to the database unless completely necessary, as the problem you are facing now is the reason we separate this. Check out database normalization, laravel foreign key migrations & relationships as well. — Shib
– Shib, Commented Oct 30, 2019 at 2:24

Qonvex620 · Accepted Answer · 2019-10-30 17:24:02Z

1

You can nest your queries: (related answer)

$meetings = Meeting::all()
    ->where('is_active', '1')
    ->where('id_room', $room)
    ->where('date', '<', $end_date)
    ->where('date', '>', $start_date)
    ->where(function($query){
        $query->where('id_participants', $participants);
        $query->orWhere('id_participants', 'like', '%;'.$participants);
        $query->orWhere('id_participants', 'like', $participants.';%');
        $query->orWhere('id_participants', 'like', '%;'.$participants.';%');
    });

However - as the comments point out - storing relations like this is very inefficient. I suggest you create a separate many-to-many table for participants. (it seems that's how they would be related)

https://laravel.com/docs/6.x/eloquent-relationships#many-to-many

edited Oct 30, 2019 at 17:24

answered Oct 30, 2019 at 3:11

Qonvex620

3,9801 gold badge12 silver badges17 bronze badges

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

1 Comment

Michele Della Mea Over a year ago

are you sure that it works? I saw this error copying and pasting your code: explode() expects parameter 2 to be string, object given

Collectives™ on Stack Overflow

How to use LIKE operator with AND operator in a query with Laravel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related