Hello,

I have a very complex query which doesn't seem possible to do using views, so I've had to make my own version of it. Now I want to allow users to search and filter using my custom form and I want to know the right way to do this. The only way I've been able to make it work so far is having a page which checks post variables for anything posted back, pops it into the query which retrieves the content if there is anything, and then renders the form:

The menu item:

function workflow_menu() {
  $items = array();
  $items['admin/content/workflow/mycontent'] = array(
    'title' => 'My content',
    'file' => 'pages/workflow_mycontent.pages.inc',
    'page callback' => 'workflow_mycontent_display',
    'type' => MENU_LOCAL_TASK,
    'weight' => -9,
    'access arguments' => array('access workflow'),
  );
  return: items;
}

The display page:

/** 
 * Display and theme the content retrieved
 */
function workflow_mycontent_display() {
	// Get all of the user's content
	if(isset($_POST['content_type'])) {
		if(isset($_POST['content_title']))
			$mycontent = _workflow_get_mycontent($_POST['content_type'], $_POST['content_title']);
		else
			$mycontent = _workflow_get_mycontent($_POST['content_type']);
	}
	else
		$mycontent = _workflow_get_mycontent();	
	if(empty($mycontent)) 
		return '<p class="defaultnotavailable">You do not have any content</p>';
		
	$content = drupal_get_form('workflow_mycontent_form');
	$content .= '<div id="mycontent-wrapper">' . $mycontent . '</div>';
	return $content;
}

The form:

/**
 * Form for filtering content 
 */
function workflow_mycontent_form(&$form_state){
	$contenttypes = _workflow_get_content_types();
	$contenttypeoptions['all'] = 'All';
	foreach($contenttypes as $contenttype) {
		$contenttypeoptions[$contenttype] = str_replace('_', ' ', $contenttype);
	}

	$default = isset($form_state['post']['content_type']) ? $form_state['post']['content_type'] : 'all';
	$form['content_type'] = array(
		'#type' => 'select',
		'#title' => 'Filter by content type',
		'#default_value' => $default,
		'#options' =>  $contenttypeoptions,
		'#description' => 'Filter by content type',
		'#tree' => true,
	);
	$form['submit'] = array(
    '#type' => 'submit',
    '#value' => 'Filter',
  );
	$form['redirect'] = null;
	
	// There must be a better way...
	$_POST = array();
	return $form;
}

The query:

/**
 * Query the database to get current user's tasks in table form
 * Retrieves all pages, images, documents, news articles, links, events, garden events 
 * which currently have a value in the workflow taxonomy AND have a user reference to the current user 
 */
function _workflow_get_mycontent($type = null, $placeholders = null) {
  // Post variables
  $contenttypes = _workflow_get_content_types();
  if (isset($type) && in_array($type, $contenttypes))
    $contenttypessql = _workflow_get_content_types('sql', array($type));
  else
    $contenttypessql = _workflow_get_content_types('sql');
	
  // Build query to retrieve all tasks. 
  ............ lots of complex query stuff
  return theme_table($header, $mycontent, array('id' => 'mycontent', 'class' => 'workflow')); 
}

I'm pretty sure this is the "wrong" way to do it because in the documentation and on the forums people repeatedly say do not use post variables, plus I end up having to clear them at the bottom of the form function but I just CANNOT find another way to make this work. Can anybody tell me a better way to do this?

(And no, using form_state as a parameter in the form function to catch the post vars and then put them into the query results do not work, the form always submits twice, once with the correct variables and once without)

Thanks,
Rukaya

Comments

rukaya’s picture

Anybody? :(

rukaya’s picture

Ok then does anybody know how I could use views to build this query:

SELECT node_revisions.vid AS node_revisions_vid,
 node_revisions.nid AS nid,
 node.type AS node_type,
 node.status AS node_status,
 node.created AS node_created,
 node.changed AS node_changed,
 node_revisions.title AS node_title,
 node_revisions.timestamp AS node_revisions_timestamp,
 content_field_deadline.field_deadline_value AS field_deadline_value,
 term_data.name AS term_data_name,
 term_data.description AS term_data_description,
 content_field_publisher_deadline.field_publisher_deadline_value AS field_publisher_deadline_value,
 content_field_author.field_author_uid AS field_author_uid,
 content_field_viewer.field_viewer_uid AS field_viewer_uid,
 content_field_approver.field_approver_uid AS field_approver_uid,
 content_field_owner.field_owner_uid AS field_owner_uid FROM node_revisions 
LEFT JOIN node ON node.nid = node_revisions.nid 
LEFT JOIN term_node ON node_revisions.vid = term_node.vid 
LEFT JOIN term_data ON term_node.tid = term_data.tid 
LEFT JOIN content_field_deadline ON node_revisions.vid = content_field_deadline.vid 
LEFT JOIN content_field_publisher_deadline ON node_revisions.vid = content_field_publisher_deadline.vid 
LEFT JOIN content_field_author ON node_revisions.vid = content_field_author.vid 
LEFT JOIN content_field_viewer ON node_revisions.vid = content_field_viewer.vid 
LEFT JOIN content_field_approver ON node_revisions.vid = content_field_approver.vid 
LEFT JOIN content_field_owner ON node_revisions.vid = content_field_owner.vid 
WHERE node_revisions.vid in ( 
	SELECT max(node_revisions.vid) 
	FROM node_revisions 
	LEFT JOIN node ON node.nid = node_revisions.nid 
	LEFT JOIN term_node ON node_revisions.vid = term_node.vid 
	LEFT JOIN term_data ON term_node.tid = term_data.tid 
	LEFT JOIN content_field_author ON node_revisions.vid = content_field_author.vid 
	LEFT JOIN content_field_viewer ON node_revisions.vid = content_field_viewer.vid 
	LEFT JOIN content_field_approver ON node_revisions.vid = content_field_approver.vid 
	LEFT JOIN content_field_owner ON node_revisions.vid = content_field_owner.vid 
	WHERE (term_data.vid in ("5")) 
	GROUP BY node_revisions.nid 
) 
AND 
(term_data.vid in ("5")) 
AND 
(node.type = "page" OR node.type = "news_article" OR node.type = "document" OR node.type = "image" OR node.type = "external_link" OR node.type = "general_event") 
AND 
(content_field_author.field_author_uid = 6 OR content_field_viewer.field_viewer_uid = 6 OR content_field_approver.field_approver_uid = 6 OR content_field_owner.field_owner_uid = 6) 
AND node_revisions.title LIKE '%%%s%%' 
ORDER BY node_type ASC 
krishnaa’s picture

I am not sure whats your exact requirement,but i think that you want to use the views to build your query,so what you can do is you can create a view according to your requirement when you will see the preview of your view,it will show you the complete SQL query.

To alter this query you can create a module and then you have to use the hook_views_pre_execute(&$view) in the module.

This hook is called right before the execution process. The query is now fully built, but it has not yet been run through db_rewrite_sql.

I hope it will help you.

Krishna