How to create a drupal view using sql

Question

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

Answer 1

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;
}

Answer 2

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