Map Views fields from custom query

Question

I have a view and I want to override the query by writing my own using the Database API of Drupal. I already wrote a function that overrides the query and returns the result set I need:

function kviews_views_pre_execute(&$view) {

if($view->name === 'custom_fans_evolution'){

    $sql = db_select('node', 'n'); //Initial table select

    $sql->join('field_data_field_metric', 'm', 'n.nid = m.entity_id'); //Join the metrics table
    $sql->join('field_data_field_values', 'v', 'n.nid = v.entity_id'); //Join the values table
    $sql->join('field_data_field_page_connection', 'p', 'n.nid = p.entity_id'); //Join the pages table
    $sql->join('field_data_field_value_date_date', 'd', 'n.nid = d.entity_id'); //Join the dates table

    $sql->fields('n', array('title')) //Add fields - chained
    ->fields('d', array('field_value_date_date_value'))
        ->fields('v', array('field_values_value'))
        ->condition('m.field_metric_value', 'page_fans', 'LIKE')
        ->condition('p.field_page_connection_tid', 69, '=')
        ->condition('d.field_value_date_date_value', strtotime(date('Y-m-d')), '<')
        ->condition('d.field_value_date_date_value', strtotime(date('Y-m-d').' -8 days'), '>')
        ->orderBy('field_value_date_date_value', 'ASC') //Order results - chained
        ->range(0,15); //Limit results - chained

    $view->build_info['query'] = $sql;

}
}

The query executes successfully, in PHPMyAdmin it returns the right data and in the preview window of the view it displays the right amount of rows. All I get though is the label of the field I choose in the UI:

How can I map the fields I get from the query to the fields I add to the Views UI? Or maybe I need to add the fields to the view using a hook?

Thanks!

Answer 1

One solution I found is to add PHP fields for the data you want to present into the view. The variable $data that holds what was retrieved from the database, per row, is printed this way:

print $data->[alias];

Where [alias] is the field alias from the database, without the brackets (ex. in $sql->addField('n', 'title', 'node_title') the alias is node_title).

This solution is not as clean as I would like so I will wait for more, "cleaner" answers before accepting this one.