Subscriptions View

There's My account | Subscriptions | Pages — is that not what you want?

I am trying to expose the subscriptions data to views. This is for the purpose of creating a calendar view that shows all events that a user has subscribed to (in a more general sense of registering an interest rather than the Subscriptions sense of registering to receive email updates).
A user can subscribe to receive email updates or subscribe with a send_interval of -1, in which case Subscriptions is only used to mark which nodes a user has "subscribed to", rather than actually send emails.
The problem is the nid of the nodes subscribed to are stored in a field as varchar and that field doesn't always contain the nid, so I can't join this table with the node table.

Any ideas? Any thoughts on other pitfalls and whether this seems an appropriate extension of the Subscriptions module would be appreciated.

Thanks
Matt

Having a views 2 integration for the subscriptions module that allows one to design views of subscribed nodes would be very useful.

I think that to implement this one would need to have a table with fields nid and uid which, for each user, lists all the nodes that the user is subscribed for. So for example, when a user subscribes to a category then all nodes in that category will be added to that new table. The table also needs to be updated each time a node is created or edited and when a node is tagged.

I'm interested in pursuing this, but I'm short of time right now.

The reason for the load_args being varchar is to allow other load_functions taking other (and possibly multiple) parameters than nid.

Yes, I think Subscriptions should provide much of the required functionality, and if anything is missing, we should be able to add it. I'm a complete views 2 newbie though...

I have attached a module that lets Views display all nodes that a user has subscribed to (either subscribed directly to the node or subscribed to the taxonomy term that is used to tag that node). Readme included.

Attachment	Size
subscriptions_views.tar_.gz	1.86 KB

I'm getting the following error:

* user warning: Table 'drupal6.D6subscriptions_nid' doesn't exist query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.title AS node_title, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp FROM D6node node LEFT JOIN D6subscriptions_nid subscriptions_nid ON node.nid = subscriptions_nid.nid INNER JOIN D6users users ON node.uid = users.uid LEFT JOIN D6node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE (node.status <> 0) AND (subscriptions_nid.uid = 1) ORDER BY node_comment_statistics_last_comment_timestamp DESC ) AS count_alias in /public_html/sites/all/modules/views/includes/view.inc on line 652.
* user warning: Table 'drupal6.D6subscriptions_nid' doesn't exist query: SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.title AS node_title, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp FROM D6node node LEFT JOIN D6subscriptions_nid subscriptions_nid ON node.nid = subscriptions_nid.nid INNER JOIN D6users users ON node.uid = users.uid LEFT JOIN D6node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE (node.status <> 0) AND (subscriptions_nid.uid = 1) ORDER BY node_comment_statistics_last_comment_timestamp DESC LIMIT 0, 25 in /public_html/sites/all/modules/views/includes/view.inc on line 677.

The View doesn't return any hits. I've subscribed to a few nodes, groups and users (although the readme.txt seems to indicate that this module doesn't yet support subscriptions to users' content), so there should have been some hits at least!

Sorry for taking so long to reply. I've tried this with 6.x-2.0-rc1, but I get the following error:

Apparently, I was missing the necessary db_user permissions. After successful installation, I get

notice: Undefined index: #value in D:\WebDev\workspace\drupal-6\sites\all\modules\subscriptions\subscriptions_content.module on line 550.

on the admin/build/views page (using D6 from cvs).

Other than that, it works nicely, but I've only briefly played with it so far.

The reason for the nid being in a VARCHAR column is that value must be able to hold a content type just as easily as a nid or a tid. Your solution with the SQL view seems to work just fine, if you have the necessary grants.

The current approach of using an sql view in order to implement the views integration for subscriptions has the shortcoming that the sql statement for the view needs to be customized depending on which kinds of subscriptions it should take into account. Currently it only takes node subscriptions and taxonomy term subscriptions into account. It is not clear how to extend the current approach to be compatible with the modular nature of the subscriptions framework that allows contributed modules to provide additional subscription types.

I think a better approach would be to use the fact that the subscriptions module already maintains a subscriptions queue. This queue could be used for the purpose of the views integration provided entries would not get deleted from it when they are mailed out. They should only get marked as having been mailed out.

@gustav: Please explain — how would that help? {subscriptions_queue} still has (module, field, value) to deal with, just like {subscriptions}.

@salvis: ah, sorry, my post was based on the apparently wrong assumption that the subscriptions_queue would contain the list of nodes for which notifications need to be sent. What does actually get queued? Is there some easy-to-read explanation of how the subscriptions module works somewhere?

In addition to (module, field, value), which depend on whatever Subscriptions module is managing the given type of subscription, {subscriptions_queue} also gets (load_function, load_args), but this can vary even more.

Subscriptions is designed to handle all sorts of information (specifically including private messages, back in the early days), not just nodes, and this is the downside of that flexibility.

This module supported for 6.x-1.0-beta2 realese ?

Is there any copy for 5.x-2.3 ??

I'm not sure how to "package" this at this point, and Views2 hasn't been released yet afaik.

We need better support for handling the missing-permission issue that I stumbled upon before we can call it "supported".

No, there's nothing for D5, and Views for D5 is different, so it would need to be developed from scratch.

*bump*

This would still be very cool, the Views 2.x API is now finalised and it shouldn't be too hard to do it - look in to views handlers - there's fairly decent documentation linked to from the Views project page. At the moment I'm still trying to work out if my current project requires this. If it does, we'll probably build it. If not, someone else should. =)

I would love to see this also

I still don't know how to "package" this and...

... we still need better support for handling the missing-permission issue that I stumbled upon.

I'm not clear on why you needed to create a database view? I was thinking of a view using the Views module. It seems we're not on the same page...? :-/

@greg.harvey:

This issue is about the patch in #5. It requires a database view.

If you would like to discuss something else, then please open a new issue and start by stating what information you'd like to see in your Views view. A mock-up of what that view would look like would be best.

Sorry for hijacking the thread - it was completely accidental! I will start a new issue. =)

Ps - it seems I'm not the only one under the wrong impression - I would suggest a title change to this issue to clarify its purpose, since Drupalers tend to think of the Views module by merlinofchaos when they see "View" in an issue title without specifically saying it is referring to a database view...

Pps - is the view creation SQL PostGreSQL safe (ANSI SQL)? I'm not familiar enough with it to know if the view creation SQL is the same for MySQL and PostGreSQL. Worth noting.

This is about integrating Subscriptions with the Views module and matt_p's solution in #5 above requires a database view.

Please don't argue with me whether and why a database view is needed, unless you come up with an actual solution that gets by without.

The big question is what exactly do we want to see in a "Subscriptions view"... Install matt_p's patch and see whether it does what you're thinking of.

P.S. The pgsql folks are welcome to test and provide patches.

LOL. I'll just butt out then. ;-)

I was delighted to find this thread, but being a newbie to the Views API I can't get matt_p's code to work. Having installed the module as is, I couldn't see any Subscriptions group or field available when I create a view. Then I followed the instructions in the Views API Help and inserted the following code in the module file:

but then Drupal just crashes with the error "Class 'views_handler_filter_boolean_operator' not found"

can anyone guide me?

ok I figured out how to make it work. This is probably obvious to most of you out there but I'm posting in case it helps someone.

I just transferred the views_handler class to its own .inc file and added implemented hook_views_handlers() int the .views.inc file:

Clem, for the true noobs, do you mind explaining/showing exactly what you put in there?

I'm trying to wrap my head around this to make sure I understand it completely. (I've designed for Drupal for a while now and am getting more into the actual module code, now)

yea, i guess i'll try some of the code here but just wanted to subscribe to this post and also put my $0.02 worth in...

for the current site i am working on the client wants their users to be able to see "the content they are interested in".. i figured the subscriptions module was exactly what we needed. But i don't care about users being sent emails.. i just want them to have a "Subscriptions" tab on My Account that is what they are subscribed to. Surprised this isn't core to this module but certainly views support would suffice.

And, as posted above.. i guess this all assumes the db storage for this isn't simply a queue that gets wiped when email is sent.

I am sure the bits are all here for this.. will be interesting to start doing some digging.

ok, getting a bit of an idea what's here.. personally i think matt_p's approach is incorrect (although i admit i am not a sql view expert)

i think the solution is what gustav eludes to in #3 - there needs to be a new table that maps uid/nid and basically integrates with when the current code is filling the sub queue.

i do like the idea above about using existing queue table and simply marking items as mailed rather than flushing from queue; but likely better to do separate module with its own table. Of course this doesn't work for the idea of subscribing to non-node items; but not real clear on where that would be useful.. i guess you might want to subscribe to a user?? and if they mod their account info you get notified.. but there are users as nodes modules that would bridge a gap like that.

i'll take a look at subs api and see what's involved here - as long as it has an invoke all hook when adding nid's to queue the module (and views handler code for it) should be pretty simple.. i'll see if i can whip it up tonight

i am debating if possibly a module which links the subs module with the favorites modules is of value here. favorites is simply a list of user selected nodes whereas the integration of subscriptions to this would simply populate the faves table on node insert/update (hmm, maybe only insert is of interest here) with nodes which match the subscriptions rules as defined in the subscriptions table.

not sure if value of integration though with faves module; i guess they likely have views handlers already and links to remove a fave; which i guess lets a user override adding via a rule like tag (i.e. i want to see all nodes tagged with X except the 4 i have unselected)

also, duhh.. not just the node insert hook but also need to populate faves when a rule is selected or unselected.

The main problem with the view (as far as I can see) is that view creation may not be allowed to the user, and then we're hosed.

i do like the idea above about using existing queue table and simply marking items as mailed rather than flushing from queue; but likely better to do separate module with its own table.

Yes, I agree. We're talking about a (number of users) x (number of nodes) table, which would keep growing indefinitely, and the rows in {subscriptions_queue} are quite bulky.

Of course this doesn't work for the idea of subscribing to non-node items; but not real clear on where that would be useful.. i guess you might want to subscribe to a user?? and if they mod their account info you get notified.. but there are users as nodes modules that would bridge a gap like that.

In the original design by chx, the non-node items were private messages generated by the privatemsg module, which were not nodes. The new maintainer of that module tore it apart and is now using nodes AFAIK, and dropped integration with Subscriptions.

i'll take a look at subs api and see what's involved here - as long as it has an invoke all hook when adding nid's to queue the module (and views handler code for it) should be pretty simple.. i'll see if i can whip it up tonight

There's hook_subscriptions_queue(), which is called for each item that is added to the queue. You have to check yourself, whether the item is a node or not. I'd say it's safe to assume it is, if it has a nid.

I don't know the favorites module, but reuse and integration is generally preferred over rewriting.