How to count aggregate data from RSForm submission values with Joomla's query building methods?

Question

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' 

Answer 1

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.

Answer 2

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)