CODEWITHSUNDEEP
phpmysql
user007
user007

Reputation: 3243

MySql - FIND_IN_SET check if all exist

I want to check in mysql if all given keys exists in set or not. like:

    $comma_separted_user_ids = "20,2,9,8,31,1";

    $query ="SELECT conversation_id FROM message 
             WHERE FIND_IN_SET($comma_separted_user_ids, user_ids) ";

    // data of user_ids = "1,2,8,9,20,31";

I want to check if all user id exist in user_ids column or not, user_ids are not properly ordered.

Please suggest a solution, thanks.

Upvotes: 2

Views: 2103

Answers (4)

RandomSeed
RandomSeed

Reputation: 29779

While it is technically feasible:

$query = 
  'SELECT conversation_id FROM message'
  . 'WHERE FIND_IN_SET(' 
  . str_replace(
      ',',
      ', user_ids) AND FIND_IN_SET('
      $comma_separted_user_ids
  )
  . ', user_ids)' ;

... you should never do this!

Instead, create a new table to model the many-to-many relationship that exists between your user and message entities (e.g. participant). This is basic normalisation.

Then the query becomes trivial and performant:

SELECT conversation_id FROM participant
WHERE user_id IN ($comma_separted_user_ids)
GROUP BY conversation_id
HAVING COUNT(user_id) = [number of items in $comma_separted_user_ids]

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can do this:

SELECT conversation_id
FROM message 
WHERE FIND_IN_SET($comma_separted_user_ids, user_ids) > 0
GROUP BY conversation_id
HAVING count(distinct user_id) = 1 + (length($comma_separted_user_id) - length(replace($comma_separted_user_id, ',', '')))

The having clause is counting the number of elements in the comma separated list.

If you are creating the SQL, you should consider using a table to store the values instead of a list. A join approach can take advantage of indexes, which find_in_set() cannot.

Upvotes: 1

RickN
RickN

Reputation: 13500

Since you don't know the ordering, I don't see a way around FIND_IN_SET. Like others said, it'd be far better to normalise your table structure.

But in the interest of providing an answer to the question, you'll need to create a list of FIND_IN_SET operators. 

// A list of IDs.
$comma_separated_user_ids = "20,2,9,8,31,1";

// The TRUE string will make sure that the array 
// always contains at least one item.
$where = array("TRUE");

// Iterate over the IDs and create strings such as
// "FIND_IN_SET(1, column_name_here)"
foreach(explode(",", $comma_separated_user_ids) as $id) {
    $where[] = "FIND_IN_SET($id, user_ids)";
}

Then it's a simple matter of joining the strings together:

// Join everything together with AND (&&).
// Since "0" is considered FALSE, this works.
$where = implode(" && ", $where);

// Query for rows.
$query ="SELECT conversation_id FROM message WHERE ($where) ";

Don't use this if you don't need to. It won't scale very well.

Upvotes: 1

may saghira
may saghira

Reputation: 564

i guess you should write it like this :

$comma_separted_user_ids = "20,2,9,8,31,1";

    $query ="SELECT conversation_id FROM message 
             WHERE user_id IN ($comma_separted_user_ids) ";

Upvotes: -1

Related Questions