How to hook_views_query_alter arbitrary field and use it in view GUI

Question

For the life of me I can't figure this out.

I have vast and complicated drupal 7 site and among other things I'm using profile2 module. Not sure why, but a few years back I've added field directly to 'profile' table. It is used to store some number and adding it looked like quick fix back then.

Now I need to get some info using views and all is fine except this one field which I absolutely have to take into account, but I am not able to.

What I did so far is:

function modulename_views_api(){    
    return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'modulename'),
  ); 
}

in my custom module,

function modulename_views_query_alter(&$view, &$query){
    if ($view->name == 'viewname') {
        $query->add_field(NULL, 'some_field', array());
    }
}

in modulename.views.inc

What I'm trying to do is either add this field to display directly, or add it to the list of fields, or replacement patterns, or anything I could use.

modulename_views_data() isn't useful for me, because I would have to recreate whole table structure created (and frequently changed) in GUI.

I tried _views_pre_view, _views_pre_render, relationships, query rewrites, all to no avail. I've run out of ideas...

Answer 1

You could add a normal number field to the user profile in the drupal ui, then translate the rogue column into this normal drupal field by populating the field from the table column with an SQL query (or several queries via a looped php script) on your database.

This would mean that the field would become available in the normal drupal way within views, and would also avoid you having these sorts of issues on another occasion.

I am sure it does not need to be said, but obviously you would backup first and perform this operation in a local development to test.

Note: It should even be possible to export the numeric value against uid and use feeds to bring the value into a profile2 field using the uid as unique, if that felt more comfortable than the naked sql population.