I am helping with a Drupal 6 / Ubercart site that has tons of spam user accounts registered.
I want to create a view that will list spam accounts. My thinking is that users who have registered but never bought anything in Ubercart have a high probability of being spam so I want to create this view. However, I can't figure out how to filter my user view to list only users who have not completed an order.
Is this possible?
To save to anyone else the trouble, this is the view I created to achieve the above.
$view = new view;
$view->name = 'Spambots';
$view->description = '';
$view->tag = '';
$view->base_table = 'users';
$view->core = 6;
$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('relationships', array(
'uid' => array(
'label' => 'Order user',
'required' => 0,
'id' => 'uid',
'table' => 'uc_orders',
'field' => 'uid',
'relationship' => 'none',
),
));
$handler->override_option('fields', array(
'mail' => array(
'label' => 'E-mail',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'absolute' => 0,
'link_class' => '',
'alt' => '',
'rel' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'hide_alter_empty' => 1,
'link_to_user' => 'mailto',
'exclude' => 0,
'id' => 'mail',
'table' => 'users',
'field' => 'mail',
'relationship' => 'none',
),
'created' => array(
'label' => 'Created date',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'absolute' => 0,
'link_class' => '',
'alt' => '',
'rel' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'hide_alter_empty' => 1,
'date_format' => 'small',
'custom_date_format' => '',
'exclude' => 0,
'id' => 'created',
'table' => 'users',
'field' => 'created',
'relationship' => 'none',
),
'login' => array(
'label' => 'Last login',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'absolute' => 0,
'link_class' => '',
'alt' => '',
'rel' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'hide_alter_empty' => 1,
'date_format' => 'small',
'custom_date_format' => '',
'exclude' => 0,
'id' => 'login',
'table' => 'users',
'field' => 'login',
'relationship' => 'none',
),
'edit_node' => array(
'label' => 'Edit link',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'absolute' => 0,
'link_class' => '',
'alt' => '',
'rel' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'hide_alter_empty' => 1,
'text' => '',
'exclude' => 0,
'id' => 'edit_node',
'table' => 'users',
'field' => 'edit_node',
'relationship' => 'none',
),
'delete_node' => array(
'label' => 'Delete link',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'absolute' => 0,
'link_class' => '',
'alt' => '',
'rel' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'hide_alter_empty' => 1,
'text' => '',
'exclude' => 0,
'id' => 'delete_node',
'table' => 'users',
'field' => 'delete_node',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'uid' => array(
'order' => 'DESC',
'id' => 'uid',
'table' => 'users',
'field' => 'uid',
'relationship' => 'none',
),
));
$handler->override_option('filters', array(
'order_id' => array(
'operator' => 'empty',
'value' => array(
'value' => '',
'min' => '',
'max' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'order_id',
'table' => 'uc_orders',
'field' => 'order_id',
'relationship' => 'none',
),
'rid' => array(
'operator' => 'not',
'value' => array(
3 => '3',
6 => '6',
4 => '4',
5 => '5',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'rid',
'table' => 'users_roles',
'field' => 'rid',
'relationship' => 'none',
'reduce_duplicates' => 0,
),
));
$handler->override_option('access', array(
'type' => 'role',
'role' => array(
3 => 3,
5 => 5,
),
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('header', 'The following accounts have no orders associated with them and are not assigned a specific role. Therefore it is safe to assume they are spambots.');
$handler->override_option('header_format', '1');
$handler->override_option('header_empty', 1);
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 20);
$handler->override_option('use_pager', '1');
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
'grouping' => '',
'override' => 1,
'sticky' => 0,
'order' => 'asc',
'summary' => '',
'columns' => array(
'mail' => 'mail',
'login' => 'login',
'uid' => 'uid',
'delete_node' => 'delete_node',
),
'info' => array(
'mail' => array(
'sortable' => 0,
'separator' => '',
),
'login' => array(
'sortable' => 0,
'separator' => '',
),
'uid' => array(
'sortable' => 0,
'separator' => '',
),
'delete_node' => array(
'separator' => '',
),
),
'default' => '-1',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'spambots');
$handler->override_option('menu', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
'name' => 'navigation',
));
$handler->override_option('tab_options', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
'name' => 'navigation',
));
It will need tweaking to your needs because I've got it set up to filter based on user role too but should give you a good base to start with.
Also the SQL is:
SELECT users.uid AS uid, users.mail AS users_mail FROM users users LEFT JOIN uc_orders uc_orders ON users.uid = uc_orders.uid LEFT JOIN users users_uc_orders ON uc_orders.uid = users_uc_orders.uid LEFT JOIN users_roles users_roles ON users.uid = users_roles.uid AND (users_roles.rid = 3 OR users_roles.rid = 6 OR users_roles.rid = 4 OR users_roles.rid = 5) WHERE (uc_orders.order_id IS NULL) AND (users_roles.rid IS NULL) ORDER BY uid DESC
first you will have to add a relationship linking ubercart orders to a user, then filter by the order number that is not greater than 0.
