Jason M5 replied 1 year ago

Thanks Victor! I've looked at this method but i don't know how to add the where clause for a specific field and value. Is there a code example somewhere showing how to use the where clause for a field and field value?

Victor Font replied 1 year ago

The answer is in the Formidable docs here: https://formidableforms.com/knowledgebase/php-examples/#kb-entries

This is the code in the article:

$entries = FrmEntry::getAll(array('it.form_id' => 5), ' ORDER BY it.created_at DESC', 8);

Formidable's documentation primarily shows the basics as in the above example. If you look at the entry in my Codex, the FrmEntry::getAll() method actually accepts up to 5 parameters:

getAll( $where, $order_by = '', $limit = '', $meta = false, $inc_form = true )

This is where a developer has to start reading code to get a complete understanding about how to use an object-oriented public or public static function without having to spawn a child class. If you open FrmEntry.php, the getAll() function declaration is found at line 473. The Codex example above is extracted programmatically directly from this line in the code. You're seeing it as the developer wrote it when you view it in the Codex

Unfortunately, one of the shortcomings I've found in Formidable's older code base is the complete lack of developer comments. I'm a firm believer that public and public static functions that are gifts to developers, should be very well commented. But, that's just my opinion.

To fully understand these concepts, you must be introduced to Formidable's metadata schema. Please review Formidable's table structure here: https://formidableforms.com/knowledgebase/database-schema/.

You must also have some familiarization with SQL query formation. Documentation for SQL queries is dependent on your host's "flavor" of database platform, i.e. MySQL, MariaDB, Percona, etc.

As already established, FrmEntry::getAll() expects 5 parameters: $where, $order_by, $limit, $meta, and $inc_form.

The first parameter to be examined is $limit because it's the first used in the function, even though it's the third parameter received in order. The global $wpdb object is called immediately. If limit is passed, the query record return limit is set in the SQL query Formidable is building. If you pass a limit of 10, only the first 10 that match the $where will be returned. The default limit is an empty string meaning "all records". ($limit = '')

Next, it does some background cache functions. If the entries aren't cached, the SQL gets built.

The $where parameter is checked whether the $where is sent through as a string or array(). If sent through as string, it is read and converted into an array(). It's best to send $where through as an array(), which is exactly what the Formidable KB article shows us with array('it.form_id' => 5).

At this point, you really SQL table aliases. Arrays are represented as key/value pairs. The key in the KB example is it.form_id and the value is 5. The it. prefix is how Formidable's SQL query identifies the frm_items table. In a metadata schema, the frm_ittems table may be regarded as a header record. All field(meta) data is captured frm_item_metas.

In your code, you definitely need to follow the KB example. Replace '5' with your form's id. With this alone, it returns everything entered through the form. It returns it as an associative array that you need to process. It has every field in frm_items.

It gets more complicated in your case because of the metadata, so I've decided to write this up as a tutorial instead of posting the entire answer here. I'll post the article when it's done.

Victor Font replied 1 year ago

Here's the tutorial I promised. You can take a look at the data returned from FrmEntry::getAll(). https://formidable-masterminds.com/how-frmentrygetall-works/

Jason M5 replied 1 year ago

This is amazing! Thank you so much for the tutorial...it's clear and concise and helped solved my issue!

I looped through the results of the method below and filtered them based on the meta data values.

$results = FrmEntry::getAll(array("it.form_id" => 123), "", "", true);

Much appreciated!

Julie Barkun replied 11 months ago

Hi Victor, that article was extremely helpful in my case too. Thank you for the thorough explanation!

I'm running into an issue filtering by meta value, and am wondering if you or Jason would happen to know what's going on?

Using the 'frm_handle_field_column_sort' filter, I appended ', (SELECT meta_value FROM wp_frm_item_metas WHERE field_id = 92 AND item_id = it.id) as meta_92'; to the $sort variable. I also changed the first parameter for FrmEntry::getall() to array('it.form_id' => 123, 'meta_92' => 1239).

I'm hoping to only return entries where the meta field with the ID 92 has the value of 1239. I'm not super familiar with SQL, but comparing my appended string with what was already added by formidable forms, it feels like it should work. But all I'm getting is the error "WordPress database error Unknown column 'meta_92' in 'where clause'". Is there an extra step that I'm missing?

Any thoughts would be appreciated!

Victor Font replied 11 months ago

Where did you find the details for using frm_handle_field_column_sort? It's not in the Formidable Knowledgebase and you don't have to use the filter at all. All you should have to do is pass meta_92 as the $order_by parameter for the FrmEntry::getAll() method.