CCK address field in views filters [workaround solution provided]

That's funny. I thought I had an issue for this already. Turns out this has only really come up in regards to my cck_fullname module, but the issue is the same - i.e. I haven't had much time to try and figure out Views integration and so I just sort of threw something together. So I'm not surprised that it isn't entirely working. With that said, I don't really have time in the next several weeks to get to something like this. If you know how to code and can fix it, that would be great.

It may be that CCK itself is to blame for the wrong-ness of how my module is interacting with Views. For some reason (and this still has not been addressed by the CCK team) CCK modules are expected to have a single value and not be compound values like addresses are. In other words, CCK only recognizes the first column stored as a valid (sortable) value. This caused me to reorder the columns in my cck_fullname module so that last name was first - which would produce more natural alphabetizing results than sorting by first name!

Anyway, my lame answer for now is that I'm not sure whether modules such as mine can work properly at all with Views so long as CCK itself is the way it is - assuming I'm right about how CCK is. If someone out there that knows the Views code and/or CCK code better than I do has any answers for this module, I'm all ears.

Maybe you could get someone at CCK to look at this issue and help us out. That would be nice.

If there was an obvious choice for a field to sort on, I could re-order the columns like I did for cck_fullname, but I could see that people would complain no matter what I chose. On the one hand, international sites would want to sort by country, U.S. users possibly by State, local/regional users by city or ZIP, and focused sites by street. So until CCK is fixed, I fear we can't do anything about it. :-((

Dang ... I was hoping to be able to create a sort/selection in VIEWs based on state then city. I guess I might have to go back to separate fields for the diff parts of the address.

But then again maybe I'm lucky and there has been some activity on this front that I missed. Fingers are crossed.

Dang ... I was hoping to be able to create a sort/selection in VIEWs based on state then city. I guess I might have to go back to separate fields for the diff parts of the address.

But then again maybe I'm lucky and there has been some activity on this front that I missed. Fingers are crossed.

No luck. I've been swamped and haven't even tried to understand what needs fixing in CCK to get such things as this to work. IIRC, there is an issue in CCK's queue regarding Views and CCK fields with multiple sub-fields, but I haven't seen any activity on that either. This is a big PITA, isn't it? If enough people would get on CCK's case, this could get fixed.

This is not an endgame-solution, but possibly viable for someone looking to solve a specific problem in a very specific situation.

I have one CCK Address field called field_address that is shared by multiple content types, thus it has its own "content_field_address" table. I have hard-coded this to offer that table up to Views using the define() below.

For my use, I have placed this code in modules/cck_address/views_cck_address.inc:

// $Id: views_cck_address.inc $

define('CCK_ADDRESS_TABLE', 'content_field_address');

/**
 * This include file implements views functionality on behalf of cck_address.module
 */

function cck_address_views_tables() {
  $tables[CCK_ADDRESS_TABLE] = array(
    'name' => CCK_ADDRESS_TABLE,
    'provider' => 'internal', // won't show up in external list.
    'join' => array(
      'type' => 'inner',
      'left' => array(
        'table' => 'node',
        'field' => 'vid'
      ),
      'right' => array(
        'field' => 'vid'
      ),
    ),
    'fields' => array(
      'field_address_street1' => array(
        'name' => t('CCK Address: Street 1'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('This will display Street 1 of this CCK Address field.'),
      ),
      'field_address_street2' => array(
        'name' => t('CCK Address: Street 2'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display Street 2 of the CCK Address field.'),
      ),
      'field_address_apt' => array(
        'name' => t('CCK Address: Apt #'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display Apt # of the CCK Address field.'),
      ),
      'field_address_city' => array(
        'name' => t('CCK Address: City'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display City of the CCK Address field.'),
      ),
      'field_address_state' => array(
        'name' => t('CCK Address: State Abbreviation'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display State Abbreviation of the CCK Address field.'),
      ),
      'field_address_zip' => array(
        'name' => t('CCK Address: Zip Code'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display Zip of the CCK Address field.'),
      ),
      'field_address_country' => array(
        'name' => t('CCK Address: Country'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display Country of the CCK Address field.'),
      ),
      'field_address_other' => array(
        'name' => t('CCK Address: Other'),
        'handler' => 'views_handler_field_value',
        'sortable' => true,
        'help' => t('Display Other of the CCK Address field.'),
      ),
    ),
    'sorts' => array(
      'field_address_city' => array(
        'name' => t('CCK Address: City'),
        'help' => t('This allows you to sort by city.'),
      ),
      'field_address_state' => array(
        'name' => t('CCK Address: State'),
        'help' => t('Sort by state.'),
      ),
      'field_address_zip' => array(
        'name' => t('CCK Address: Zip'),
        'help' => t('This allows you to sort by zip.'),
      ),
    ),
    'filters' => array(
      'field_address_city' => array(
        'name' => t('CCK Address: City'),
        'operator' => 'views_handler_operator_like',
        'handler' => 'views_handler_filter_like',
        'help' => t('Filter by CCK Address: City field.'),
      ),
      'field_address_state' => array(
        'name' => t('CCK Address: State'),
        'operator' => 'views_handler_operator_like',
        'handler' => 'views_handler_filter_like',
        'help' => t('Filter by CCK Address: State field.'),
      ),
      'field_address_zip' => array(
        'name' => t('CCK Address: Zip'),
        'operator' => 'views_handler_operator_like',
        'handler' => 'views_handler_filter_like',
        'help' => t('Filter by CCK Address: Zip field.'),
      ),
    )
  );

  return $tables;
}

and then, somewhere in the top of cck_address.module:

/**
 * Include the Views hack for multiple fields.
 */

 require_once('views_cck_address.inc');

After clearing the views cache, I now have CCK Address fields split out for use in the "Table", "List" or "CSV Export" (bonus pack) types -- it should also be sortable, but have not tested.

You can use City/State/Zip as filters and expose them. Arguments are not implemented here, but should be trivial to support.

--

I will not be updating, maintaining or submitting a patch for this -- if someone can make the code a bit more intelligent (i.e., have it find the proper cck_address field automatically), it could be useful to the public.

Thanks,
Jason

Here's an updated version of the above which works with (some) arguments. Currently, city, state, and an abbreviated state work.

Thanks!
--Andrew

It does not work in my case (I tried the last patch). Tried to add exposed filters of Address: State and Address: City and it doesn't find anything.

Maybe the issue for the bug is that in my case there are 2 different fields of the type Address in the underlying content type?!?

After applying the patch, make sure you empty the 'cache_views' table. (And thanks, you guys!)

NOTE: As implied by the comment above, this patch *does* require that your field name is standard, ie: 'field_address' not 'field_location' or anything else.

Yes - I have it as a define() so you can change it, but hopefully someone who knows more about CCK knows of a better (dynamic) way to do it.

I needed an argument by the starting state letters, so I copied this from taxonomy and modified it:

   $arguments['field_address_state'] = array(
      'name' => t('CCK Address: State Letter'),
      'handler' => 'views_handler_arg_stateletter',
      'option' => 'string',
      'help' => t('The argument will filter by a state name. For this argument, set the option to the number of characters, using 0 for full state; use 1 for an A/B/C style glossary.'),
    );

And then the function. I probably don't need everything in there but left it in anyway:

function views_handler_arg_stateletter($op, &$query, $argtype, $arg = '') {
  static $field = NULL;
  switch($op) {
    case 'summary':
      $query->add_table('cck_address_states', true);
      $len = intval($arg);
      $field = $fieldinfo['field'] = ($len <= 0 ? "cck_address_states.state_name" : "LEFT(cck_address_states.state_name, $len)");

      $fieldinfo['fieldname'] = 'letter';
      $query->add_field('state_id', 'cck_address_states');
      $query->add_where('cck_address_states.state_name IS NOT NULL');
      return $fieldinfo;
      break;
    case 'sort':
      $query->add_orderby('', $field, $argtype, 'letter');
      break;
    case 'filter':
      $len = intval($argtype['options']);
      $query->add_table('cck_address_states', true);

      if ($len <= 0) {
        $query->add_where("cck_address_states.state_name = '%s'", $arg);
      } else {
        $query->add_where("LEFT(cck_address_states.state_name, $len) = '%s'", $arg);
      }
      break;
    case 'link':
      return l($query->letter, "$arg/$query->letter");
    case 'title':
      return check_plain($query);
  }
}

What I couldn't figure out how to adapt is the group-by sort for sorting by state abbreviation. I would appreciate any help on that.

Here's the page:

http://sineny.bigcitydev.com/store-locator

The address field is also printing the blank address lines, but that's another issue.

Thanks,

Aaron

I need to create a list of businesses (content type) that is filterable by City. Right now, there is no obvious way to do this. Is there any recommended configuration of Drupal / Views / Address Field CCK / CCK that will help me achieve this result? I need an easy to understand solution that will upgrade. I am willing to test anything someone produces.