0

I have a data variable named $staff_id and I have a notice table in database, in table has a "can_see" column, this column contains data as an id string separated by commas, I want to get the rows has $staff_id of that string, i want to use the Laravel query builder and I don't want to use the Stored procedure.

enter image description here

DB::table('tasks')->where("staff_id in can_see column!!")->get();
Mehravish Temkar
  • 4,275
  • 3
  • 25
  • 44
newsshare24h
  • 69
  • 1
  • 11
  • 2
    This is one of the main reasons why you should _normalise_ your tables. As it currently stands, you now have to write application code to split out those comma-separated values. I'd take the time now to split those values out into a new table. That being said, you could use `IN` or `find_in_set` to scan those values - https://stackoverflow.com/q/2674011/296555 – waterloomatt Aug 21 '19 at 13:38
  • Thanks for your answer, I could use find_in_set, but I've simplified my tables. – newsshare24h Aug 21 '19 at 14:38

1 Answers1

0

You can try this

DB::table('tasks')->whereRaw(DB::raw('find_in_set("'.$staff_id.'", can_see) > 0'))->get();

If you save your values as '6','7','8','9','10', you could easily use LIKE to filter the records.

DB::table('tasks')->where('order_id', 'LIKE', "%'".$staff_id."'%")->get();
Mehravish Temkar
  • 4,275
  • 3
  • 25
  • 44