Mysql find_in_set with Join not working in postgres

Question

My mysql query is working proper in SQL database server but when i run same query in Postgres database than find set is not working. below is my query :

SELECT omh.id as idd, omh.modified as modified, omh.modified as modified1, 
DATE_FORMAT(omh.modified, '%m/%d/%Y') as only_date, DATE_FORMAT(omh.modified, '%h:%i %p') as only_time

FROM order_memo_history as omh

LEFT JOIN users as usr ON (FIND_IN_SET(usr.user_id, omh.users_id) != 0)

INNER JOIN service_order as so ON omh.order_id = so.o_id
LEFT JOIN users as u ON omh.user_id = u.user_id
WHERE (omh.modified BETWEEN "2017-09-01 06:00:00" AND "2017-10-27 05:59:00")
AND IF((so.merge_company_id='0'),(omh.company_id = 2819), ((omh.order_id = so.o_id AND omh.company_id = so.merge_company_id) OR (so.merge_company_id = 2819 
        AND (omh.group_id = 2819 OR omh.group_id = '0'))))
GROUP BY idd ORDER BY modified ASC

I am getting error

ERROR:  function find_in_set(integer, text) does not exist
LINE 6: LEFT JOIN users as usr ON (FIND_IN_SET(usr.user_id, omh.user...

I have try with string_to_array but not worked

@FahadAnjum :- yes but in postgres database use string_to_array but it not working in my case. — Praveen Kumar, Oct 26 '17 at 11:59
Why are you using find in set instead you can directly right usr.user_id = omh.users_id ? — Fahad Anjum, Oct 26 '17 at 12:02
https://stackoverflow.com/questions/32391486/rails4-find-in-set-with-pg-not-working , it may helpful to you. — Asif Thebepotra, Oct 26 '17 at 12:04
@FahadAnjum :- **omh.users_id** this filed have comma separated value so we need to use that function . so you know any function for behave like find_in_set function in postgres sql — Praveen Kumar, Oct 26 '17 at 12:07

score 0 · Answer 1 · answered Oct 26 '17 at 13:12

Postgresql Solution:
Use of functions string_to_array and any can help you in getting the desired result.

Following example can give you some understanding on how to apply the function.

In the example below, assume that the string '2,4,11,5,8,6' is comma separated user ids from the field omh.users_id and values 11, 1, 9 are user ids from the field usr.user_id.

select string_to_array( '2,4,11,5,8,6', ',' ) as sa
     , '11' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as matched
     , '1' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as un_matched_1
     , '9' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as un_matched_2

Result would be like this:

+----------------+---------+--------------+--------------+
| sa             | matched | un_matched_1 | un_matched_2 |
| text[]         | boolean | boolean      | boolean      |
+----------------+---------+--------------+--------------+
| {2,4,11,5,8,6} | t       | f            | f            |
+----------------+---------+--------------+--------------+

ravinder-reddy :- I need to check two filed in two different table with **!=0**. How to use this in your syntax **9 = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as un_matched_2** — Praveen Kumar, Oct 30 '17 at 13:15
Do you know that how can I convert this kind of query in the Node js sequelize method? — Yugma Patel, Feb 03 '20 at 09:47

Mysql find_in_set with Join not working in postgres

1 Answers1