Joomla mysql query - group_contact

Question

I have mysql query:

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

$formId = 21;

$query = "select * from #__rsform_submission_values WHERE `FormId` = $formId order by `SubmissionId` desc limit 30";

$db->setQuery($query);
$results = $db->loadObjectList();

It loads specific data from above table (see screenshot)

what I want is to load data like:

  0 => 
    object(stdClass)[427]
      public 'SubmissionValueId' => string '20006' (length=5)
      public 'FormId' => string '21' (length=2)
      public 'SubmissionId' => string '1122' (length=4)
      public 'FieldName' => string 'session_date' (length=12)
      public 'session_date' => string '17-03-2022 20:00' (length=16)
  1 => 
    object(stdClass)[426]
      public 'SubmissionValueId' => string '20007' (length=5)
      public 'FormId' => string '21' (length=2)
      public 'SubmissionId' => string '1122' (length=4)
      public 'FieldName' => string 'city' (length=4)
      public 'city' => string 'Hørsholm Kommune' (length=17)

so instead of FielValue = example it should be called session_date = example (replace all FieldValue to WITH FieldNames)

Answer 1

I really can't see the use in retrieving the SubmissionValueIds or FormIds associated with the rows of data after you pivot into an associative structure. When you fold the original rows together, you are going to lose the relationship to the original values anyhow.

If you already know all of the FieldName values that you are going to processing, you can most simply use a pivot technique -- here are MANY demonstrations that I've posted. From what I see in your resultset screenshot, you want to capture the data for session_date, city, group, and status. I don't see any use for submit or formId -- in fact this redundant storage is unnecessarily bloating a table that is notoriously prone to becoming VERY bloated. I recommend adjusting your RSForm form so that the submit and formId values are not stored in the database table at all. With my current understanding of your requirement, this should do:

$query = $db->getQuery(true)
    ->select([
        "a.SubmissionId",
        "a.DateSubmitted",
        "MAX(CASE WHEN b.FieldName = 'status' THEN b.FieldValue ELSE NULL END) AS status",
        "MAX(CASE WHEN b.FieldName = 'session_date' THEN b.FieldValue ELSE NULL END) AS session_date",
        "MAX(CASE WHEN b.FieldName = 'city' THEN b.FieldValue ELSE NULL END) AS city",
        "MAX(CASE WHEN b.FieldName = 'group' THEN b.FieldValue ELSE NULL END) AS " . $db->qn('group')
    ])
    ->from("#__rsform_submissions a")
    ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
    ->where("a.FormId = " . (int)$formId)
    ->group("a.SubmissionId DESC");  // yes, you can declare the sort order here

// echo $query->dump();  // uncomment if you want to confirm the rendered query
try {
    $db->setQuery($query);
    $result = $db->loadObjectList();
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public / in production
}

---

If you need your script to remain fully dynamic and you don't want to perform an earlier query on the table to collect all of the qualifying FieldName values to set up a dynamic pivot query, then you can just iterate over your result set and do the grouping/pivoting in PHP.

$result = [];
foreach ($db->loadObjectList() as $obj) {
    $result[$obj->SubmissionId][$obj->FieldName] = $obj->FieldValue;
}
return $result;