Monitor Reply

Support

Filtering form results - API

Permalink Browser Info Environment April 27, 2015 at 9:49 AM

This is for some sub-contracted work where the site is using formidable (not the license noted above)

I need to search the results for a specific form 'form_name' to list all results where a named field 'field_name1' has a value 'value_1' and a second named field 'field_name2' has a value 'value_2'.

Can you help with a quick generic example of setting up such a list query.

Thanks

John

Type:	Discussion
Status:	Resolved

JohntheFish replied on Apr 27, 2015 at 9:53 am Permalink Reply

More info - should have made it clear.

I am writing some custom code that needs to make the query, presumably using FormidableResultsList.

DeWebmakers replied on Apr 29, 2015 at 3:26 am Permalink Reply

Hi John,

I'll add this to an update later, but for now....
Add the following code to the models/formidable/result.php:

public function filterByElementHandle($handle, $value, $comp = '=') {
      $db = Loader::db();
      if ($handle == false) {
           $this->filter(false, $value);
      } else {
         $comp = (is_null($value) && stripos($comp, 'is') === false) ? (($comp == '!=' || $comp == '<>') ? 'IS NOT' : 'IS') : $comp; 
         $this->filter(false, "fas.answerSetID = (SELECT answerSetID 
                                        FROM FormidableAnswers
                                        LEFT JOIN FormidableFormElements ON FormidableAnswers.elementID = FormidableFormElements.elementID                        
                                        WHERE FormidableAnswers.formID = ".$this->form->formID." 
                                        AND FormidableFormElements.label_import = '".$handle."'
                                        AND FormidableAnswers.answerSetID = fas.answerSetID 
                                        AND FormidableAnswers.answer_formated ".$comp." ".$db->quote($value).")");   
      }
   }

Viewing 15 lines of 28 lines. View entire code block.

 
public function filterByElementHandle($handle, $value, $comp = '=') {
      $db = Loader::db();
      if ($handle == false) {
           $this->filter(false, $value);
      } else {
         $comp = (is_null($value) && stripos($comp, 'is') === false) ? (($comp == '!=' || $comp == '<>') ? 'IS NOT' : 'IS') : $comp; 
         $this->filter(false, "fas.answerSetID = (SELECT answerSetID 
                                        FROM FormidableAnswers
                                        LEFT JOIN FormidableFormElements ON FormidableAnswers.elementID = FormidableFormElements.elementID                        
                                        WHERE FormidableAnswers.formID = ".$this->form->formID." 
                                        AND FormidableFormElements.label_import = '".$handle."'
                                        AND FormidableAnswers.answerSetID = fas.answerSetID 
                                        AND FormidableAnswers.answer_formated ".$comp." ".$db->quote($value).")");   
      }
   }
 
   public function filterByElementID($id, $value, $comp = '=') {
      $db = Loader::db();
      if ($id == false) {
           $this->filter(false, $value);
      } else {
         $comp = (is_null($value) && stripos($comp, 'is') === false) ? (($comp == '!=' || $comp == '<>') ? 'IS NOT' : 'IS') : $comp;          
         $this->filter(false, "fas.answerSetID = (SELECT answerSetID FROM FormidableAnswers 
                                        WHERE formID = ".$this->form->formID." 
                                        AND elementID = ".$element->elementID." 
                                        AND answerSetID = fas.answerSetID 
                                        AND answer_formated ".$comp." ".$db->quote($value).")");   
      }
   }

Then you can use filterByElementHandle or filterByElementID to filter the results.

Best,

Corretje

JohntheFish replied on Apr 29, 2015 at 3:41 am Permalink Reply

Thanks for that. Looks much neater than some of the solutions I have been experimenting with.

On the subject of sub-classing and extending the database models, I have been running into problems because the ID of forms, replies, elements, answers etc... are all private, so any extension class cannot use it.

Can you please either make the ID attributes available (protected/public), or probably safer from your point of view, provide some getID() methods for all the models.

John

JohntheFish replied on Apr 29, 2015 at 3:45 am Permalink Reply

PS, should the code above be added to the class 'FormidableResultsList' or to class FormidableResult?

(I am guessing at FormidableResultsList)

JohntheFish replied on Apr 29, 2015 at 5:24 am Permalink Reply

I am getting an error testing this

mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND elementID = AND answerSetID = fas.answerSetID AND ans' at line 14] in EXECUTE("SELECT fas.answerSetID AS answerSetID, fas.submitted AS a_submitted, fas.ip AS a_ip, fas.collectionID AS a_collectionID, fas.answerSetID AS a_answerSetID, fas.userID AS a_userID, fas.browser AS a_browser, fas.platform AS a_platform, fas.resolution AS a_resolution FROM FormidableAnswerSets AS fas where 1=1 and fas.answerSetID = (SELECT answerSetID FROM FormidableAnswers WHERE formID = AND elementID = AND answerSetID = fas.answerSetID AND answer_formated = 'AZ') limit 0,100 ")

I think the critical part is

WHERE formID = AND elementID = AND

from the code

WHERE formID = ".$this->form->formID." 
AND elementID = ".$element->elementID."

For element ID, I suspect that is a typo and should be simply $id

formID is a bit more difficult. It is set from the SESSION, presumably on the assumption that this is only used a dashboard form list page.

So the class needs a means to identify the form being searched before it can be used in other situations.

JohntheFish replied on Apr 29, 2015 at 5:34 am Permalink Reply

In filterByElementHandle there is a similar problem with the form ID. Also should it be label_import (which appends the id) or simply label in the query?

DeWebmakers replied on Apr 29, 2015 at 6:20 am Permalink Reply 1 Attachment

Hi John,

I found a bug in the code... The ID of the element isn't set correctly. Also the construct of the FormidableResultsList requires a session variable. I changed this as well.

Download the attachment for a new version of the results model.
Using the following code returns results for me:

Loader::model('formidable/result', 'formidable');
// FormID can be set now
$list = new FormidableResultsList($_REQUEST['formID']);
// FieldID = 3, Value = "Cor", Comparison = LIKE
$list->filterByElementID(3, '%Cor%', 'LIKE');
// Or use
// FieldID = 3, Value = "Cor", Comparison = LIKE
// $list->filterByElementHandle('your-name_3', '%Cor%', 'LIKE');
$list->debug();
var_dump($list->get());

Also added getID methods to the form, elements, mailings and results. Will be in the next update.

Best,

Corretje

JohntheFish replied on Apr 29, 2015 at 6:38 am Permalink Reply

Thanks, filterByElementID is working fine now. What timescale are you looking at for the official update?

DeWebmakers replied on May 7, 2015 at 3:20 am Permalink Reply

Hi John,

The filter is in the latest update.

Thanks,

Corretje

Formidable

Support

Filtering form results - API

Issue Reply

concrete5 Environment Information

Browser User-Agent String

Hide Post Content

Request Refund

Code

Delete Post