Hi. I need to filter a field that has been stored as varchar using Views numeric operators (ideally the "Is Between" operator). I have run across several posts re: related situations but can't seem to figure out how to apply these ideas to my situation, which seems to have been complicated by the way the content type (Audio - the last, incomplete version) was designed (the Audio and ID3 modules work in concert to extract and store MP3 file metadata; the Audio module stores all metadata in a varchar column in a table "metadata"):

mysql> describe audio_metadata;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| vid   | int(10) unsigned | NO   | PRI | 0       |       | 
| tag   | varchar(45)      | NO   | PRI |         |       | 
| value | varchar(255)     | NO   | PRI |         |       | 
| clean | varchar(255)     | NO   | MUL |         |       | 
+-------+------------------+------+-----+---------+-------+

One thing I would really like to know: how/where/when does Views assign an operator type to a field?

The developer of the Audio module has completely stopped answering support requests, and for D7 the module is deprecated. However aside from this seemingly trifling issue, this module is perfect/working very well for the needs of this site which for various reasons pretty much needs to be in D6.

It seems that the prescribed D6 way of doing this sort of thing involves writing a custom module using hook_views_data_alter(). I could really use some help understanding how to apply this method to my use case - for one thing I don't really get how Views is able to see these metadata fields (but it can) as they are being stored separately from the available fields one can see when managing fields for the Audio content type - I really need to figure out how hook_views_data_alter() can get at these fields.

Or, there does seem to be a half implemented solution within the module itself... given the addition of a metadata tag to an array like so (code from audio.views.inc):

$numeric_tags = array('track', 'year');

... the author of the Audio module seems to have intended to provide a way of replacing other metadata filter handlers with numeric handlers for a given metadata "tag" type:

    // Use different handlers for numeric tags.
    if (in_array($tag, $numeric_tags)) {
      $tables["audio_metadata_$tag"]['sorts']['value']['handler'] = 'audio_views_sort_handler_numeric_tag';
      // Set notafield to TRUE so that our handler can add the field.
      $tables["audio_metadata_$tag"]['fields']['value']['notafield'] = TRUE;
      $tables["audio_metadata_$tag"]['fields']['value']['query_handler'] = 'audio_views_field_query_handler_numeric';
    }

... however this doesn't seem to work as is even for the 2 tag types the author left in the array. Any help understanding why not or troubleshooting that would be greatly appreciated.

Anyone? Thanks much for any help.

Comments

dawehner’s picture


      $tables["audio_metadata_$tag"]['sorts']['value']['handler'] = 'audio_views_sort_handler_numeric_tag';
      // Set notafield to TRUE so that our handler can add the field.
      $tables["audio_metadata_$tag"]['fields']['value']['notafield'] = TRUE;
      $tables["audio_metadata_$tag"]['fields']['value']['query_handler'] = 'audio_views_field_query_handler_numeric';

This really doesn't look like views integration for views2. Could it be that this is a leftover from drupal5?

butler’s picture

Thanks dereine. I think it is quite possible this is a leftover as parts of this last version of the Audio module are incomplete.

So... is there some way to change filter handler in Views 2 I might not have run across? I can't change the column type in the database as there are other metadata tags being stored in the same column which (for this project) must have text filter operators... any ideas? I hope it isn't the case that filter handler operator types are inflexibly tied to database column type?

dawehner’s picture

You definitive first should update to the dev version of the audio module, because it includes a big amount of good changes in the views integration.

  foreach (audio_get_tags_allowed() as $tag) {
    $data['audio_metadata_'. $tag]['table']['group']  = t('Audio');

    // For other base tables, explain how we join
    $data['audio_metadata_'. $tag]['table']['join'] = array(
      'node' => array(
        'left_field' => 'vid',
        'table' => 'audio_metadata',
        'field' => 'vid',
        'extra' => array(array('field' => 'tag', 'value' => $tag, 'operator' => '='))
       ),
      'node_revisions' => array(
        'left_field' => 'vid',
        'table' => 'audio_metadata',
        'field' => 'vid',
        'extra' => array(array('field' => 'tag', 'value' => $tag, 'operator' => '='))
       ),
    );

    $data['audio_metadata_'. $tag]['value'] = array(
      'title' => t('@tag', array('@tag' => drupal_ucfirst($tag))),
      'help' => t('The value of the @tag metadata field.', array('@tag' => $tag)),
      'field' => array(
        'handler' => 'views_handler_field_xss',
        'click sortable' => TRUE,
       ),
      'sort' => array(
        'handler' => 'views_handler_sort',
      ),
      'filter' => array(
        'handler' => 'views_handler_filter_string',
      ),
      'argument' => array(
        'handler' => 'views_handler_argument_string',
      ),
    );

But as everything is stored as a varchar it's somehow a bit idea to use numerical expressions.

butler’s picture

Thanks very much again for the reply dereine.

I'm a little bit afraid to upgrade this module as I have a lot of other stuff working well with it -resulting in kind of a tangle of new and old module versions... maybe I'll try it on a separate install.

dereine where you wrote above "it's somehow a bit idea" was "bit" a typo - meant "bad" maybe? Are you saying that it isn't possible (god I hope not the idea that something so basic could send this project back to the drawing board is terrifying), or that it doesn't sound like it makes sense?

To explain, the metadata "tag"/value I want to be able to sort numerically holds bpm (beats per minute or musical tempo) - it will always be an integer.

butler’s picture

Oh, I might not have made something clear - the value column in the Audio module audio_metadata table stores different types of data depending which "tag" is being stored. Would always be text for a title tag, always an integer for a bpm tag, etc., all being stored in the same column. I suppose this is why the module dev made this a varchar column.

butler’s picture

Looks like I lost dereine for now... can anybody point me to info re: how/where/when does Views 2 assign an operator type to a field? anybody?

Kars-T’s picture

Status: Active » Fixed

Hi

I am closing this issue to clean up the issue queue. Feel free to reopen the issue if there is new information and the problem still resides. If not please make sure you close your issues that you don't need any more.

Maybe you can get support from the local user group. Please take a look at this list at groups.drupal.org.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

butler’s picture

Status: Closed (fixed) » Active

I'm not sure how to respond to having my issue closed for not getting replies in order "to clean up the issue queue". Is this a policy the community has somehow agreed on - please advise/explain if so? Kars-T, this seems more like taking Drupal away from this person.

There is no new information because I can't get to square one. But I would really appreciate an answer and thus would appreciate it if this issue could be left open in case someone were to eventually respond.

Kars-T’s picture

Hi butler

I am closing support issues that are over some months old because the Views issue queue is flooded with request for forgotten or obsolete issues. By now I have closed hundreds of issues and there is only an "I am still interested" rate under 1% in all those issues. So I don't feel like I am taking Drupal away from the people but giving relieve to the maintainers. :)

In your case this is problematic to solve because dereine did point to that a varchar can't be used as an int and that now after nearly a year the modules probably have moved a lot.

So are you using the latest versions? Views 3? Update to Drupal 7?

Kars-T’s picture

Issue summary: View changes

typo correction

MustangGB’s picture

Status: Active » Closed (outdated)