0

Is there anyway for me to create a Drupal view from two sql queries and have two separate outputs. Here is the two SQL queries I want to create a view from:

SELECT f.delta AS field_data_field_session_start_date_delta , f.entity_id AS date_id_date_filter , f.delta AS date_delta_date_filter , n.title AS node_title , n.nid AS nid , n.language AS node_language , f.field_session_start_date_value AS field_data_field_session_start_date_field_session_start_date , f.field_session_start_date_value2 AS field_data_field_session_start_date_field_session_start_date2 , 'node' AS field_data_field_session_start_date_node_entity_type FROM node n JOIN field_data_field_session_start_date f ON f.entity_id = n.nid AND f.entity_type = 'node' AND f.deleted = '0' AND f.field_session_start_date_value >= NOW() WHERE n.status = '1' ORDER BY f.field_session_start_date_value ASC LIMIT 3 OFFSET 0

SELECT f.delta AS field_data_field_session_start_date_delta , f.entity_id AS date_id_date_filter , f.delta AS date_delta_date_filter , n.title AS node_title , n.nid AS nid , n.language AS node_language , f.field_session_start_date_value AS field_data_field_session_start_date_field_session_start_date , f.field_session_start_date_value2 AS field_data_field_session_start_date_field_session_start_date2 , 'node' AS field_data_field_session_start_date_node_entity_type FROM node n JOIN field_data_field_session_start_date f ON f.entity_id = n.nid AND f.entity_type = 'node' AND f.deleted = '0' AND f.field_session_start_date_value <= NOW() AND f.field_session_start_date_value2 > NOW() WHERE n.status = '1' ORDER BY f.field_session_start_date_value ASC LIMIT 3 OFFSET 0

  • 2
    Why cant you just use Views UI? – Alex Gill Mar 25 '14 at 14:33
  • 1
    Views is a query builder. You already have a query built. What are you trying to do? – beth Mar 25 '14 at 14:45
  • well basically I am trying to create view that uses two sql queries and have two outputs (i will update the question) – colinam1992 Mar 25 '14 at 14:47
  • Can you provide any context about what the larger goal is? – beth Mar 25 '14 at 15:01
  • well basically these are two queries on a events one brings up events that are on currently and the other is to bring events that are yet to start and I want to create a view that has 3 total fields with priority to the output from the now query – colinam1992 Mar 25 '14 at 15:04
0

Make 2 Views with your LIMIT 3 clauses from the SQL you've provided using the Views UI.

Then make a custom block in a module. Use views_get_view_result (see additional documentation and usage of views_get_view_result here) to call your 2 views programmatically from PHP code. Then in your block output content simply loop through upto 3 items in your NOW listing, then add any needed items from your UPCOMING listing if the max of 3 items hasn't been reached yet. An example of how to make a block in code by Four Kitchens can be seen here. Taking their example your hook_block_view may look like below.

function custom_block_block_view($delta='') {
  $block = array();
  switch($delta) {
  case 'my_block' :
    $block['content'] = my_three_upcoming_events();
    break;
  }
  return $block;
}

function my_three_upcoming_events() {
  $output = '';
  $now_events = views_get_view_result('VIEWNAME_FOR_NOW_EVENTS_VIEW');
  $upcoming_events = views_get_view_result('VIEWNAME_FOR_UPCOMING_EVENTS_VIEW');
  $max_events = 3;
  // Make a big list of events now; upcoming events will be on the end of this list.
  $all_events = $now_events + $upcoming_events;

  for ($i=0,$z=$max_events;$i<$z;$i++) {
    $node = node_load($all_events[$i]['nid']);
    $output .= '<div>' . l($node->title, '/node/'.$node->nid) . '</div>';
  }
  return $output;
}
  • Thanks tenken , to get this to work do I machine name of the two views, if I do what how do I get the machine name of the view. – colinam1992 Mar 25 '14 at 17:17
  • when you create a View you give it a Name; there is an option to view/edit the machine name when creating a view. For an existing View, when you Edit the view the machine name is in the url: /admin/structure/views/view/admin_faculty_listing/edit ... here admin_faculty_listing is the view machine name. – tenken Mar 25 '14 at 17:43
  • Hi Tenken, I have added the necessary part of the code the link example. I then added in the code above and changed the view names correctly, but the new block doesnt come up on the block listing. – colinam1992 Mar 26 '14 at 9:54
  • I am not familar with views_get_view_result as I am relatively new to drupal but would $view = views_get_view('views_name'); $view->execute(); print $view->render(); – colinam1992 Mar 26 '14 at 10:06
  • work in the same way – colinam1992 Mar 26 '14 at 10:07
0

I think the easiest way to create this in views will be to just manually read it and re-create using the views UI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.