1
user SubmissionId FormId FieldName FieldValue
bob 2 16 state mystate1
bob 2 16 qda myqad1
bob 2 16 city mycity1
rob 3 16 state mystate2
rob 3 16 qda myqad1
rob 3 16 city mycity1
jac 4 16 state mystate1
jac 4 16 qda myqad3
jac 4 16 city mycity1
sara 5 20 state mystate1
sara 5 20 qda myqad1
sara 5 20 city mycity2

I want to count the number of submissions where formid is 16, state is mystate1, qda is myqad1 , and city is mycity1.

In my example, the count should be 1 because only SubmissionId number 2 satisfies all requirements.

$query = $db->getQuery(true);
//$query->select('COUNT(*)');
$query->select($db->quoteName('FieldValue') . ' IN ("mycity1")');

$query->from($db->quoteName('#__rsform_submission_values'));
$query->where($db->quoteName('formid') .' IN ("16")');
$query->where($db->quoteName('FieldName') .' IN ("state","qda","city")');
$query->where($db->quoteName('FieldValue') . ' IN ("mystate1","myqad1","mycity1")');
$db->setQuery($query);
$results = $db->loadObjectList();

foreach($results as $value) {
    foreach($value as $key => $data) {
        echo $data."<br />";
    }
}

Here's my attempted query, but it doesn't produce the count that I want:

SELECT COUNT(SubmissionId)  
FROM my_submission_values 
WHERE FormId = '16' 
   AND FieldName ='state' 
   AND FieldValue = 'mystate1' 
   AND FieldName = 'qda' 
   AND FieldValue = 'myqad1' 
   AND FieldName = 'city' 
   AND FieldValue ='mycity1' 
0
0

For all future mysql support questions (whether on Stack Overflow or on Joomla Stack Exchange), you will enjoy more, faster and more accurate answers if you include a fiddle of your table schema in an online demo link. For example: db-fiddle

As for this specific task, RSForm's #__rsform_submission_values table is built as a Unified Content Model. This means you will need to group related rows and use aggregate functions to filter and isolate what you need. This technique is commonly referred to as a PIVOT. You can find my other Joomla-related pivot queries on Joomla Stack Exchange.

I am rather surprised that your submission_values table contains a user column. This is not the standard table structure designed by RSForm. I am going to write my answer with the knowledge that the user column comes from a related table. I am going to show this best practice so that:

  1. you have an opportunity to un-hack your table structure if you have hacked it and
  2. researchers who have a standard table structure can immediately use my snippets.

There will be a few ways to execute the required technique. I will show a classic pivot query with an INNER JOIN and another query that uses a subquery with EXIST to isolate the unique counts. If Username is not a unique field in the submissions table, then find a unique field in that table to count. I have tested these queries and php scripts to be successful.

Raw Query via INNER JOIN:

SELECT COUNT(DISTINCT s.Username)
FROM lmonp_rsform_submissions s
INNER JOIN lmonp_rsform_submission_values sv
    ON s.FormId = sv.FormId
        AND s.SubmissionId = sv.SubmissionId
WHERE s.FormId = 16
GROUP BY s.Username
HAVING MAX(IF(FieldName = 'state', FieldValue, NULL)) = 'mystate1'
    AND MAX(IF(FieldName = 'qda', FieldValue, NULL)) = 'myqad1'
    AND MAX(IF(FieldName = 'city', FieldValue, NULL)) = 'mycity1'

Raw Query via EXISTS(subquery):

SELECT COUNT(1)
FROM lmonp_rsform_submissions s
WHERE s.FormId = 16
    AND EXISTS (
        SELECT FormId
        FROM lmonp_rsform_submission_values sv
        WHERE s.FormId = sv.FormId
            AND s.SubmissionId = sv.SubmissionId
        GROUP BY s.Username
        HAVING MAX(IF(FieldName = 'state', FieldValue, NULL)) = 'mystate1'
            AND MAX(IF(FieldName = 'qda', FieldValue, NULL)) = 'myqad1'
            AND MAX(IF(FieldName = 'city', FieldValue, NULL)) = 'mycity1'
    )

Now, I'll present the Joomla query builder syntax to generate the above queries.
Both snippets will share these variables as a means to allow you to customize the sql conditions without scanning through the full query block.

$formId = 16;
$having = [
   'state' => 'mystate1',
   'qda' => 'myqad1',
   'city' => 'mycity1'
];

Joomla Query Builder with INNER JOIN:

$db = JFactory::getDbo();
$query = $db->getQuery(true)
   ->select("COUNT(DISTINCT s.Username)")
   ->from("#__rsform_submissions s")
   ->innerJoin("#__rsform_submission_values sv ON s.FormId = sv.FormId AND s.SubmissionId = sv.SubmissionId")
   ->where("s.FormId = " . $formId)
   ->group("s.Username");
foreach ($having as $column => $value) {
   $query->having("MAX(IF(FieldName = " . $db->q($column) . ", FieldValue, NULL)) = " . $db->q($value));
}

//echo $query->dump();  // uncomment to see the rendered sql
$db->setQuery($query);
echo $db->loadResult();

Joomla Query Builder with EXISTS(subquery):

$db = JFactory::getDbo();
$subquery = $db->getQuery(true)
   ->select("FormId")
   ->from("#__rsform_submission_values sv")
   ->where(["s.FormId = sv.FormId", "s.SubmissionId = sv.SubmissionId"])
   ->group("s.Username");
foreach ($having as $column => $value) {
   $subquery->having("MAX(IF(FieldName = " . $db->q($column) . ", FieldValue, NULL)) = " . $db->q($value));
}
$query = $db->getQuery(true)
   ->select("COUNT(1)")
   ->from("#__rsform_submissions s")
   ->where(["s.FormId = " . $formId, "EXISTS ($subquery)"]);

//echo $query->dump();  // uncomment to see the rendered sql
$db->setQuery($query);
echo $db->loadResult();

I did not benchmark these queries, so if you find one of them to be noticeably slow, you might try the other.

@itoctopus/@itoctopus seems rather scathing of use of DISTINCT, so it may be less performant.

1
  • 1
    yes User is not my field in table i add this to any body that not work by rsform @mickmackusa
    – saeed
    Jan 31 at 4:51
0

tank you @mickmackusa I change to this code

    SELECT count(DISTINCT a.SubmissionId) 
    FROM `#__rsform_submission_values` a 
    JOIN `jwqt5_rsform_submission_values` b 
    ON(a.submissionid=b.submissionid) 
    JOIN `#__rsform_submission_values` c 
    ON(b.submissionid=c.submissionid) 
    WHERE a.FormId= '16' 
    AND a.FieldName='state' AND a.FieldValue='mystate1' 
    AND b.FieldName='qda' AND b.FieldValue='myqad1' 
    AND c.FieldName='city' AND c.FieldValue='mycity1'

my total count of (state is mystate1 and qda is myqad1 and city is mycity1) is ok (count is 1)

I want to count mycity1 and qda is myqad1 (count is 2)Separated (by variable) and Separated count mystate1 that qda is myqad1 (count is 2) (by variable)

1
  • I do not recommend this technique. You are joining a very large (or will be eventually very large) table to itself 2 additional times. I expect my solution to be more efficient. The implementation of a "pivot query" is the professional way to consolidate the related rows and deliver the desired result.
    – mickmackusa
    Jan 31 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.