How to relate comma-separated ids from one table to the ids of another table?

Question

I have a varchar(255) field in one of my Jdatabase tables containing values separated by commas. Precisely, it contains numbers separated by commas.

I want to retrieve the individual values using a custom PHP module. Actually, I need to select records (retrieve data) from another table which have an id that exists in the comma-separated list of ids.

What would be the best way to achieve this?

Answer 1

Just 2 simple queries, with first you select commas and then you check if IDs are inside that string. Just tested and it works using MySql IN() function with $commas string value fetched from $db->loadResult().

Can't thing about anything simpler.

// First get all commas
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('Commas'))
      ->from($db->qn('#__classes_attended'))
      ->where($db->qn('Id') . ' = 1');
$db->setQuery($query);

$commas = $db->loadResult();


// Select them from second table
$query = $db->getQuery(true);
$query->clear();
$query->select($db->qn('Data_needed'))
      ->from($db->qn('#__jdatabse_table_2'))
      ->where($db->qn('Id') . ' IN (' . $commas . ')');
$db->setQuery($query);

$results = $db->loadObjectList();

Answer 2

What would be the best way to achieve this?

There are two pieces of "best practice" that I am compelled to share for this question.

1. Don't make more trips to the database than you need to.
   
   Because your query logic can be baked into a single query, it should be. The hero to call upon in this instance is FIND_IN_SET(). It works in a similar fashion to IN, but IN won't work on a comma-delimited column value.
   
   Raw Query: (db-fiddle.com demo)

   SELECT name
   FROM `#__table2`
   WHERE FIND_IN_SET(id, (SELECT classes_attended FROM `#__table1` WHERE id = 1))
   

   *This could have been performed as a JOIN instead of a subquery (as shown in the stackoverflow link above my snippet), but I prefer this approach because you only need to relate to a single row one time.
   
   Joomla/PHP Syntax: (tested locally; includes query dump and error checking)

   $id = 1;
   try {
       $db = JFactory::getDbo();
   
       $subquery = $db->getQuery(true)
                      ->select($db->qn("classes_attended"))
                      ->from($db->qn("#__table1"))
                      ->where($db->qn("id") . " = " . (int)$id);
   
       $query = $db->getQuery(true)
                   ->select($db->qn("name"))
                   ->from($db->qn("#__table2"))
                   ->where("FIND_IN_SET(" . $db->qn("id") . ", (" . $subquery . "))");
   
       $db->setQuery($query);
       JFactory::getApplication()->enqueueMessage($query->dump(), 'info');  // don't show your rendered query to the public
       if (!$result = $db->loadColumn()) {
           echo "<p>No Rows Found</p>";
       } else {
           echo "<pre>";
           var_export($result);  // prints indexed single-dimensional array of values
           echo "</pre>";
       }
   } catch (Exception $e) {
       JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show $e->getMessage() to public
   }
   

2. Normalize your data structure.
   
   Using FIND_IN_SET() is a symptom that your database design is not optimized.
   
   Don't take my word for it, listen to Bill Karwin -- a man who literally wrote a book about bad SQL coding practices.

   You can't optimize this query with indexes. This is one of many reasons why storing comma-separated lists of values is a bad idea in a relational database.
   
   Source: https://stackoverflow.com/a/44183060/2943403

   Normalizing your data in this case means creating a new table and abstracting the classes_attended data into rows that contain id and class_attended. Doing so will make your data easier to query and allow you to manufacture more complex queries with greater efficiency.

       id    |    classes_attended
   -----------------------------------
        1    |        1,3,6,7
   

   becomes

       id    |    class_attended
   ---------------------------------
        1    |          1
        1    |          3
        1    |          6
        1    |          7
   

   Here is a beginner's link about Normalization.