How to get last updated record with FIND_IN_SET in laravel?

Question

I am using find_in_set with my query to get data from comma separated field. But it shows me error.

$results = DB::select("SELECT basic.updated_at,basic.updated_by,,ccbi.team_group_id,basic.id
FROM basic_info as  basic 
INNER JOIN emp_info  as  emp_details 
ON basic.emp_master_id =  emp_details.emp_id
WHERE basic.id IN (SELECT basic.id FROM basic_info as basic WHERE basic.updated_at = (SELECT MAX(basic.updated_at) FROM basic_info as basic)) AND basic.team_id =  '1' AND ('FIND_IN_SET(?,emp_details.emp_grp)' , '18')   ORDER BY basic.id DESC LIMIT 1");

above query shows me error of :

Cardinality violation: 1241 Operand should contain 1 column(s)

If i will remove AND ('FIND_IN_SET(?,emp_details.emp_grp)' , '18') this part it will return correct result.

To my way of thinking, if you find yourself using find_in_set, then you've already lost. :-( - Although I will concede that it *is* blisteringly quick — Strawberry, Jan 27 '19 at 08:25
You have an extra `,` in your query near `,,ccbi.team_group_id`. Also where is `ccbi` coming from? — Mozammil, Jan 27 '19 at 08:51

Mozammil · Accepted Answer · 2019-01-27T09:30:27.967

0

There were several typos in your query. Instead of adding everything into a DB::select(), I also cleaned your query a bit:

return DB::table('basic_info')
    ->select('basic_info.updated_at', 'basic_info.updated_by', 'basic_info.id')
    ->join('emp_info', 'basic.emp_master_id', 'emp_info.emp_id')
    ->whereRaw("basic_info.id IN (SELECT basic_info.id FROM basic_info WHERE basic_info.updated_at = (SELECT MAX(basic_info.updated_at) FROM basic_info) AND basic_info.team_id =  '1' AND FIND_IN_SET('18',emp_details.emp_grp) ORDER BY basic_info.id DESC LIMIT 1)")
    ->get();

Would the following work for you?

edited Jan 27 '19 at 09:30

answered Jan 27 '19 at 09:06

Mozammil

8,520
15
29

thanks for your answer bro but its not working getting error of `Syntax error or access violation` – amit sutar Jan 27 '19 at 09:29
Sorry, forgot to remove one statement. Edited my answer. Does the updated one work? – Mozammil Jan 27 '19 at 09:31

How to get last updated record with FIND_IN_SET in laravel?

1 Answers1