2

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:

View Preview

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!

  • Hi, Zisis. Perhaps a long shot, but have you tried adding 'nid' to the array of columns retrieved by your query? I'm only suggesting this because Views does this by default and maybe some inner mechanism requires it to properly map to your fields. – othermachines Aug 6 '15 at 6:29
  • That was actually a brilliant idea! Thanks. Although the rest of the fields do not show, after adding the nid in the UI and the query, it showed up! So, it was a step in the right direction. Any more ideas are welcome! – Zisis Koukoumakis Aug 6 '15 at 7:11
  • And another update, by giving the title an alias of "node_title" it showed up too. The custom fields refuse to show even if I copy the aliases from the same view, created using the UI. – Zisis Koukoumakis Aug 6 '15 at 7:47
0

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.

| improve this answer | |

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.