Alex0608's avatar

Fetching data from a cell

Hello. Help me with solving the problem I'm facing. I have a "tasks" table in which tasks are stored, and for each task there are their own performers, responsible, observers and co-executors.There may be one responsible person as well as the performer, but there may be many performers and observers.So the question is how do I make a selection if in two different cells the user IDs have the form "-1-10-20-30-40-50-60-70-"? The cell name f8840 is the Co-Executors, and f10140 is the observers. Please help me, I understand that such storage is wrong, but I got such an architecture from the last CRM....

0 likes
7 replies
tykus's avatar

When you say "cell" do you mean table column? And, is f8840 the column name???

I don't know what query you are attempting to execute, so can't give a more detailed example. You can use mysql's FIND_IN_SET function to search for records with a given value inside a comma-delimited string. However, since your delimiter is a dash, you would also need to replace the dash with a comma, e.g.:

SELECT *
FROM `table_name`
WHERE FIND_IN_SET(10, replace(`f8840`, '-', ',')) > 0

If you are no longer using the last CRM; then consider taking some time to normalize your schema!

1 like
Alex0608's avatar

@tykus Thank you for responding, yes the column name is f8840(Co-Executors) and f10140(Observers)

Alex0608's avatar

@tykus I need to get the tasks of the current user where he is responsible, executor, co-executor or observer. Now the request looks like this: $tasks = Tasks::with('uprcomp','street','houses', 'otvets', 'created_user') ->where(['status' => 0]) ->orderBy('id', 'desc');

tykus's avatar
tykus
2 years ago
Best Answer
Level 104

@Alex0608 this should do it:

$tasks = Tasks::with('uprcomp','street','houses', 'otvets', 'created_user')
    ->where('status', 0)
    ->where(function (Builder $builder) {
        $builder->whereRaw('FIND_IN_SET(?, REPLACE(f8840, "-", ",")) > 0',  [auth()->id()])
            ->orWhereRaw('FIND_IN_SET(?, REPLACE(f10140, "-", ",")) > 0', [auth()->id()])
    })
    ->orderBy('id', 'desc')
    ->get();
1 like

Please or to participate in this conversation.