SQL errors when using 'is_online' Views handler

Hi, I try to fix this problem in file views_handler_filter_is_online.inc
I commented out the following line in the code and problem has been resolved for me.

--- sites/all/modules/user_stats/views/views_handler_filter_is_online.inc	(revision 82836f7743ab21145838952882e6f200c55f50e6)
+++ sites/all/modules/user_stats/views/views_handler_filter_is_online.inc	(revision )
@@ -28,7 +28,7 @@
       // Is offline
       $sql = $sql_if_part . " = 0";
     }
-    $this->query->add_having($this->options['group'], $sql);
+//    $this->query->add_having($this->options['group'], $sql);
   }
 
   /**

There is a second way.
In Views display, configure filter criterion: Global: PHP >>> with this code:

return !user_stats_get_stats('online', $data->uid);

You must install and turn on this module before use Filter PHP
http://drupal.org/project/views_php

Hello,

for me this patch only hide the error message. But I still can not filter online user. In my view there is always shown one user witch is offline. This view have worked before. Don't know why is is so now.

So I set this thread to "acive"

here is my view, and there is also a screenshot:

$view = new view();
$view->name = 'online_relations';
$view->description = 'Show a list of online relations';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'Online relations';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Online relations';
$handler->display->display_options['css_class'] = 'online_relation';
$handler->display->display_options['use_ajax'] = TRUE;
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'access user profiles';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'picture' => 'picture',
  'name' => 'name',
  'is_online' => 'is_online',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'picture' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'name' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'is_online' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Relationship: User relationships: Requestee user */
$handler->display->display_options['relationships']['requestee_id']['id'] = 'requestee_id';
$handler->display->display_options['relationships']['requestee_id']['table'] = 'user_relationships';
$handler->display->display_options['relationships']['requestee_id']['field'] = 'requestee_id';
/* Relationship: User relationships: Requester user */
$handler->display->display_options['relationships']['requester_id']['id'] = 'requester_id';
$handler->display->display_options['relationships']['requester_id']['table'] = 'user_relationships';
$handler->display->display_options['relationships']['requester_id']['field'] = 'requester_id';
/* Relationship: User relationships: RTID */
$handler->display->display_options['relationships']['rtid']['id'] = 'rtid';
$handler->display->display_options['relationships']['rtid']['table'] = 'user_relationships';
$handler->display->display_options['relationships']['rtid']['field'] = 'rtid';
/* Field: User: Picture */
$handler->display->display_options['fields']['picture']['id'] = 'picture';
$handler->display->display_options['fields']['picture']['table'] = 'users';
$handler->display->display_options['fields']['picture']['field'] = 'picture';
$handler->display->display_options['fields']['picture']['relationship'] = 'requestee_id';
$handler->display->display_options['fields']['picture']['label'] = '';
$handler->display->display_options['fields']['picture']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['picture']['hide_empty'] = TRUE;
$handler->display->display_options['fields']['picture']['image_style'] = 'thumbnail_micro';
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['relationship'] = 'requestee_id';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['name']['hide_empty'] = TRUE;
/* Field: User: Is online */
$handler->display->display_options['fields']['is_online']['id'] = 'is_online';
$handler->display->display_options['fields']['is_online']['table'] = 'users';
$handler->display->display_options['fields']['is_online']['field'] = 'is_online';
$handler->display->display_options['fields']['is_online']['label'] = '';
$handler->display->display_options['fields']['is_online']['element_class'] = 'online_relation';
$handler->display->display_options['fields']['is_online']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['is_online']['not'] = 0;
/* Contextual filter: User relationships: Requester user */
$handler->display->display_options['arguments']['requester_id']['id'] = 'requester_id';
$handler->display->display_options['arguments']['requester_id']['table'] = 'user_relationships';
$handler->display->display_options['arguments']['requester_id']['field'] = 'requester_id';
$handler->display->display_options['arguments']['requester_id']['default_action'] = 'default';
$handler->display->display_options['arguments']['requester_id']['exception']['value'] = '';
$handler->display->display_options['arguments']['requester_id']['default_argument_type'] = 'user';
$handler->display->display_options['arguments']['requester_id']['default_argument_options']['user'] = FALSE;
$handler->display->display_options['arguments']['requester_id']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['requester_id']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['requester_id']['summary_options']['items_per_page'] = '25';
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: User: Current */
$handler->display->display_options['filters']['uid_current']['id'] = 'uid_current';
$handler->display->display_options['filters']['uid_current']['table'] = 'users';
$handler->display->display_options['filters']['uid_current']['field'] = 'uid_current';
$handler->display->display_options['filters']['uid_current']['value'] = '0';
/* Filter criterion: User: Is online */
$handler->display->display_options['filters']['is_online']['id'] = 'is_online';
$handler->display->display_options['filters']['is_online']['table'] = 'users';
$handler->display->display_options['filters']['is_online']['field'] = 'is_online';
$handler->display->display_options['filters']['is_online']['value'] = '1';
/* Filter criterion: User: Name */
$handler->display->display_options['filters']['uid']['id'] = 'uid';
$handler->display->display_options['filters']['uid']['table'] = 'users';
$handler->display->display_options['filters']['uid']['field'] = 'uid';
$handler->display->display_options['filters']['uid']['relationship'] = 'requestee_id';
$handler->display->display_options['filters']['uid']['operator'] = 'not empty';
$handler->display->display_options['filters']['uid']['value'] = '';

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;

Thanks for any help!
Best regards
Frank

:-( No support in that issue ?

Same problem here. The filter was working for me in Views 3.3, updating to Views 3.5 broke it. As Views 3.5 is out for 5 months now and the bug is still not fixed, I raised the priority. A module related to Views should really fix severe problems that pop up with a new Views version in a shorter time.

Looking at the issue queue of this module it seems like it's has major flaws without a chance that they are getting fixed soon. And it quite looks like this module ist about to be abandoned by its maintainer as many issue are still waiting for a response. That's to bad, because the module would be very useful - if the existing problems get fixed.

I'm puzzled by the fact that I couldn't find another module providing an "is online" filter to views. I need user pictures an other profile fields in such a block, so the default block of core does not fit my needs. Does anybody know another solution for this problem?

Anyway, I tested the views_php filter: it works for me for administrator accounts, but other roles can't see the block at all - although there is no special permission in place. Am I missing something here?

Hello markusk,

I am also sad that there in no activity in this module :-( I hope someone can fix this..I am not a real developer so it is not possible for me.

At the moment I use a workarraund based on the "user access filter"

I will attach my view so you can reproduce this...I hope this helps until there is a patch here..

Best regards
Frank

$view = new view();
$view->name = 'online_button';
$view->description = 'Show the Online-button when users are online';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'Online button';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Online button';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'access user profiles';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '1';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = TRUE;
$handler->display->display_options['fields']['name']['alter']['text'] = 'Online';
$handler->display->display_options['fields']['name']['element_class'] = 'profileonlinebutton';
$handler->display->display_options['fields']['name']['element_label_colon'] = FALSE;
/* Contextual filter: User: Uid */
$handler->display->display_options['arguments']['uid']['id'] = 'uid';
$handler->display->display_options['arguments']['uid']['table'] = 'users';
$handler->display->display_options['arguments']['uid']['field'] = 'uid';
$handler->display->display_options['arguments']['uid']['default_action'] = 'default';
$handler->display->display_options['arguments']['uid']['exception']['value'] = '';
$handler->display->display_options['arguments']['uid']['default_argument_type'] = 'user';
$handler->display->display_options['arguments']['uid']['default_argument_options']['user'] = FALSE;
$handler->display->display_options['arguments']['uid']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['uid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['uid']['summary_options']['items_per_page'] = '25';
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: User: Last access */
$handler->display->display_options['filters']['access']['id'] = 'access';
$handler->display->display_options['filters']['access']['table'] = 'users';
$handler->display->display_options['filters']['access']['field'] = 'access';
$handler->display->display_options['filters']['access']['operator'] = '>=';
$handler->display->display_options['filters']['access']['value']['value'] = '-5 minutes';
$handler->display->display_options['filters']['access']['value']['type'] = 'offset';

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$translatables['online_button'] = array(
  t('Master'),
  t('Online button'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Online'),
  t('All'),
  t('Block'),
);

Hello Frank,
thanks for your export, it's doing the trick for me! What I was missing was the ">=-5 minutes" filter.

Regards,
Markus

Hmm..it should be there..you should be able to set a filter "user last acces" with "Is greater than or equal to " option...?

Best regards
Frank

I was a bit unclear here: I did try it with other relative dates, but for some reason I didn't try the "-x minutes".

ok..now it woks?

Yes, like a charm.

Thanks very much for reporting this!

I'm marking: #1691964: Fehlermeldung SQLSTATE[42S22]: Column not found: 1054 Unknown column 'IF1342654577MAXsessions.timestamp300101' in 'having claus as a duplicate of this (and downgrading priority; critical is generally for stuff like white screens of death on the front page, insanity-level, 'OMG the world is ending!' kind of bugs).

A colleague and I struggled for most of yesterday with this, it's harder than you might think. We're pretty close to a solution though. :)

Marking #1539590: Views field is_online reduces result to one row as a duplicate of this issue.

And here's a patch!

Oh and I forgot to mention, this patch depends upon #1512488: Possible to use add_groupby without add_having? being fixed.

Hello,

Oh and I forgot to mention, this patch depends upon #1512488: Possible to use add_groupby without add_having? being fixed.

Does this mean this patch currently not working ans you wait for a solution in views?

Best regards
Frank

Does this mean this patch currently not working ans you wait for a solution in views?

Yes, although the Views issue has a patch, so you can apply that patch to Views and everything (should) work.

Ok,

thanks for yor work!

Best regards
Frank

Sorry for #18. By setting it to "critical" I referred to the issue queue handbook's item "blocks not displaying" which was exactly what was happening to me.
Anyway, very nice to see this problem fixed! Will have a look at it soon.

Markus

I just committed the update, and it seems to fix the issue. Please let me know if anyone discovers any issues.

Lucas (the colleague)

It is a little bit confusing that the last patch #21 has nothing to do with the committed update.
Nevertheless the committed update works like charm. :-)

Liam and I were working on the problem at the same time, and the code is different for filters and sort criteria, so there were other changes made since the patch was posted. Sorry for the confusion. On the bright side, Liam has more help with the module now. :)

Thanks again for the confirmation.

I was having the same issue with older dev, current (march 9th) release did NOT solved it

getting
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') < 900, 1, 0)) AS users_node_is_online FROM node node LEFT JOIN users users_no' at line 1

on any view - complex or simple

Thanks for reporting there's still a problem, just to make sure: did you apply the Views patch mentioned in #22?

no.

Ah, well sadly without that patch applied to Views the is_online filter won't work.

installed this module today and I also am getting the SQL error. Looked at the patch in #22 and it seems that most of it is already applied apart from on the views_handler_filter_is_online, but the code I have doesn't match what is in the patch so I can't apply it (I always add patches manually so might be missing something). Any help would be greatly appreciated! cheers.

Hiya saintslash, the patch has been committed to User Stats, it's Views that needs patching. Maybe you're looking at the wrong reply?

sorry your right I was trying to apply the patch in #21 :/!

Ive now applied the patch in the link on #22 and Im still getting the same syntax error.
Any ideas?
cheers

I suspect it's possibly something else then. Would you mind creating a new issue, with an export of your View (pasted into a .txt file) attached?

Thanks!

I have the same issue

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') < 600, 1, 0) = '1') )))) subquery' at line 2

Hello TheMGamer, thanks for reporting there's still a problem, just to make sure: did you apply the Views patch mentioned in #22?

Sadly, it appears the problem won't be fixed in Views without tests and I'm not sure what's required or how to write a test 'in order to be sure that your change fixes the issue.' The code is so obviously wrong, it's almost like writing a test to ensure 1 + 1 = 2 so I gave up at that point. But then, I probably just don't understand what's required.