There have been many times I've wanted to get data from a form and the existing formidable APIs (at least the ones that I know of) don't get me there.  Also, there are times when I'm using Datatables and I want to create a view from a SQL select so that I can use server-side processing.  The following code snippet can take a list of fields and column names (and additional where items) and generate that nasty-looking SQL command that you need for this.

DISCLAIMER: There's no error-checking and there are several ways it could've been written to be more flexible, but I didn't have unlimited time :).

HINT: The output will be messy.  You can feed it through an online syntax checker to see it in a nicely formatted way.

/**
  * Name: ff_buildGetObjectSQL
  * Author: Rob LeVine
  * Description: Creates a big SQL query to get any/all of the item meta values of fields across all items (potentially limited by an additional where)
  * 
  * Parameters:
  *  $fields - array of field ids and column names, e.g., $fields = array( array("id"=>"load_id"), array("name"=>"company_name"),  ... )
  *  $addlWhere - array operators, field ids and values, e.g.,  $addlWhere = array(array("operator" => "AND", field_id" => $load_field_id, "value" => $value));  
  *  $order_by - order conditionm e.g., "created_date DESC"
  * 
  * Return: string (sql command)
  * Assumptions:  
  *    1. All the fields in $fields are in the same form, i.e., attached to the same item
  *    2. The field in $addlWhere has to be in $fields as well
  *
  */

function ff_buildGetObjectSQL($fields, 
			      $addlWhere=null, 
			      $order_by=null) {
    global $wpdb;
	
    $wpdb_prefix = $wpdb->prefix;
    $table_item_metas = $wpdb_prefix . 'frm_item_metas';

    $select = "SELECT ";
    $from ="FROM ";
    $count = count($fields);
    for($i=0;$i<$count-1;$i++) {
	$from .= "(";
    }
    $where = " WHERE ";
    $firsttime = true;
	foreach($fields as $field) {
		$field_id = $field["field_id"];
		$select .= "t" . $field_id . ".meta_value AS " . $field["column_name"] . ",";
		if ($firsttime) {
			$mainFieldId = $field_id;
			$mainField = "t" . $field_id;
			$from .= "$table_item_metas $mainField";
			$firsttime = false;
		}
		else{
			$from .= " LEFT JOIN $table_item_metas t"  .  $field_id . " ON (t" . $field_id . ".item_id = $mainField.item_id AND t" . $field_id . ".field_id = " . $field_id. "))";
		}
	}
	// remove excess string and space
	$select = substr($select, 0, -1);
	$sql = "$select $from";
	$where .= " $mainField.field_id = $mainFieldId ";
	if (isset($addlWhere)) {
		foreach($addlWhere as $singleWhere) {
			$where .= " " . $singleWhere["operator"] . " t" . $singleWhere['field_id'] . ".meta_value = " . $singleWhere['value'];
		}
	}
	$sql .= $where;
	if (isset($order_by)) {
		$sql .= " ORDER BY $order_by";
	}
	
	return $sql;
}