Does anyone know what would cause this or how I'd unset it? It's not the distinct field and most filters are optional. Thanks

SELECT users.uid AS uid,
   users.name AS users_name,
   location.street AS location_street,
   location.additional AS location_additional,
   location.city AS location_city,
   location.province AS location_province,
   location.country AS location_country,
   location.postal_code AS location_postal_code,
   'ep_user_location_table2:default' AS view_name
 FROM d_users users 
 INNER JOIN d_users_roles users_roles ON users.uid = users_roles.uid
 LEFT JOIN d_location_instance location_instance ON users.uid = location_instance.uid
 LEFT JOIN d_location location ON location_instance.lid = location.lid
 WHERE (users.status <> 0) AND (users_roles.rid IN (3, 4, 5)) AND (0)

Comments

nicknickoli’s picture

nicknickoli commented

Here's what the view export looks like -- 

$view = new view;
$view->name = 'ep_user_location_table2';
$view->description = 'User location table';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Name',
    'link_to_user' => 1,
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'street' => array(
    'label' => 'Street',
    'style' => 'both',
    'exclude' => 0,
    'id' => 'street',
    'table' => 'location',
    'field' => 'street',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'city' => array(
    'label' => 'City',
    'exclude' => 0,
    'id' => 'city',
    'table' => 'location',
    'field' => 'city',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'province' => array(
    'label' => 'Province',
    'style' => 'name',
    'exclude' => 0,
    'id' => 'province',
    'table' => 'location',
    'field' => 'province',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'postal_code' => array(
    'label' => 'Postal Code',
    'exclude' => 0,
    'id' => 'postal_code',
    'table' => 'location',
    'field' => 'postal_code',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'country' => array(
    'label' => 'Country',
    'style' => 'name',
    'exclude' => 0,
    'id' => 'country',
    'table' => 'location',
    'field' => 'country',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'status' => array(
    'operator' => '=',
    'value' => 1,
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'users',
    'field' => 'status',
    'relationship' => 'none',
  ),
  'rid' => array(
    'operator' => 'or',
    'value' => array(
      '3' => '3',
      '4' => '4',
      '5' => '5',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => 'rid_op',
      'label' => 'User: Roles',
      'use_operator' => 0,
      'identifier' => 'rid',
      'optional' => 1,
      'single' => 0,
      'remember' => 0,
      'reduce' => 0,
    ),
    'id' => 'rid',
    'table' => 'users_roles',
    'field' => 'rid',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
    'reduce_duplicates' => 0,
  ),
  'value' => array(
    'operator' => 'word',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'value_op',
      'identifier' => 'tags',
      'label' => 'Interests and Skills:',
      'optional' => 1,
      'remember' => 0,
    ),
    'case' => 0,
    'id' => 'value',
    'table' => 'profile_values_ep_profile_freeform',
    'field' => 'value',
    'relationship' => 'none',
  ),
  'city' => array(
    'operator' => 'word',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'city_op',
      'identifier' => 'city',
      'label' => 'City:',
      'optional' => 1,
      'remember' => 0,
    ),
    'case' => 0,
    'id' => 'city',
    'table' => 'location',
    'field' => 'city',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
  'distance' => array(
    'operator' => 'dist',
    'value' => array(
      'latitude' => '',
      'longitude' => '',
      'postal_code' => '',
      'country' => '',
      'search_distance' => '100',
      'search_units' => 'mile',
    ),
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'distance_op',
      'identifier' => 'distance',
      'label' => '',
      'optional' => 1,
      'remember' => 0,
    ),
    'type' => 'postal_default',
    'identifier' => 'dist',
    'id' => 'distance',
    'table' => 'location',
    'field' => 'distance',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'User Locations and Search');
$handler->override_option('empty', 'No users found for the given criteria. ');
$handler->override_option('empty_format', '1');
$handler->override_option('use_pager', '1');
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'name' => 'name',
    'street' => 'street',
    'city' => 'city',
    'province' => 'province',
    'postal_code' => 'postal_code',
    'country' => 'country',
  ),
  'info' => array(
    'name' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'street' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'city' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'province' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'postal_code' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'country' => array(
      'sortable' => 1,
      'separator' => '',
    ),
  ),
  'default' => '-1',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'users/location');
$handler->override_option('menu', array(
  'type' => 'normal',
  'title' => 'User location table',
  'weight' => '0',
  'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
vesapalmu’s picture

vesapalmu commented

I get the same thing when trying to do proximity searches with location module and views. My best guess is that this is an issue with location module, not views.

vesapalmu’s picture

vesapalmu commented
Project: Views (for Drupal 7) » Location
Version: 6.x-2.2 » 6.x-3.x-dev
Component: search data » Code

After some research I figured out what was causing this in my case. Location module comes with a number of include files for each supported country. I was trying to do proximity searches in Finland and required functions were not found at location.fi.inc file. Instead of an error I just ended up with very strange SQL for a view. I was able to fix this problem by creating some missing functions to location.fi.inc.

However based on your country I assume you are trying to use Views + Location in U.S. which should be supported. I would definitely point a finger at Location module anyway and there are some previous issues like this one in it's issue queue. Try installing the very latest dev version of Location module, it could solve this issue.

I am also moving this to Location module's issue queue, I hope you'll find help in there.

yesct’s picture

yesct commented
Title: View query has wierd "AND (0)" and no results » View query has wierd "AND (0)" and no results (might be missing functions in location.countrycode.inc)
Status: Active » Postponed (maintainer needs more info)
Issue tags: +location still an error question

nicknickoli, does the latest dev version fix this for you?

yesct’s picture

yesct commented
Title: View query has wierd "AND (0)" and no results (might be missing functions in location.countrycode.inc) » View query has wierd "AND (0)" and no results (might be missing functions in location.xx.inc)

people seem to represent trouble with the country files with location.xx.inc so I'm adding that here so this issue shows up in searches

yesct’s picture

yesct commented
Status: Postponed (maintainer needs more info) » Closed (fixed)

nicknickoli, Closing this, if you are still having trouble just post back.