Support from Acquia helps fund testing for Drupal Acquia logo

Comments

salvis’s picture

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

matt_p’s picture

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

gustav’s picture

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.

salvis’s picture

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...

matt_p’s picture

FileSize
1.86 KB

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.

Osfer’s picture

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!

salvis’s picture

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

CREATE VIEW command denied to user 'db_user'@'localhost' for table 'subscriptions_nid' 
query: CREATE VIEW subscriptions_nid AS ...

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.

gustav’s picture

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.

salvis’s picture

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

gustav’s picture

@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?

salvis’s picture

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.

skullJ’s picture

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

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

salvis’s picture

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.

greg.harvey’s picture

*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. =)

break9’s picture

I would love to see this also

salvis’s picture

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.

greg.harvey’s picture

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...? :-/

salvis’s picture

@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.

greg.harvey’s picture

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.

salvis’s picture

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.

greg.harvey’s picture

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

clems’s picture

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:

function subscriptions_views_views_api() {
  return array('api' => 2.0);
}

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

can anyone guide me?

clems’s picture

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:

function my_module_views_handlers() {
  return array(
    'info' => array('path' => drupal_get_path('module', 'my_module'),),
    'handlers' => array(
		// Filter handlers
		'my_views_handler' => array(
			'parent' => 'my_parent_views_handler_filter',
		),
    ),
  );
}
visualnotion’s picture

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)

liquidcms’s picture

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.

liquidcms’s picture

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

liquidcms’s picture

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.

salvis’s picture

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.

vgulla’s picture

Can someone help me here.
I extracted this tar file and the view is created in the DB, but I see no filter.

Also, the module file seems corrupt. All it has is this:

<?php

Please help

vgulla’s picture

bump

vgulla’s picture

Please help

vgulla’s picture

Please help. I am not seeing any subscriptions filter in my views even after installing this module, ran update.php and enabled the module.

salvis’s picture

I'm sorry, this is unsupported.

I'm keeping it open because some people may get it to work for themselves, and maybe someone can even develop it to the point where it can work out of the box, but this hasn't happened so far.

mototribe’s picture

views support for subscriptions would be useful for my user case

salvis’s picture

Version: 6.x-1.x-dev » 7.x-1.x-dev

D7 first, IAC.

KristineC’s picture

Has there been a solution for this in D7? Creating a view of content with only what the user has subscribed in the terms?

salvis’s picture

This is about exposing the subscriptions, not the subscribed content. And we don't even have that yet...

MauPalantir’s picture

Status: Active » Needs review
FileSize
8.38 KB

Hello,

For one of our projects we had to implement a base Views integration for Subscriptions.
This module in the patch attached exposes the subscription ID and other properties to views, and provides a relationship to the subscribing user, the subscribed author (if there is one), the subscribed content and-or taxonomy term.

Please test and review it.
Thanks,
Mau

(development was sponsored by 5 Rings)

rooby’s picture

Status: Needs review » Needs work

Great thanks.

It does seem like there is some code missing from that patch though.

There is a line:
+files[] = subscriptions_views_handler_node_relationship.inc

But that file is noot in the patch.

For patches with new files you can do git add for all the files (new & modified) and then use git diff --staged to roll the patch.

Also, am I right in saying this doesn't support node type subscriptions?

rooby’s picture

Title: Subscriptions View » Views integration
MauPalantir’s picture

That did nothing at all, the files[] entry remained in it by mistake. And yes, this patch doesn‘t support node types yet.

rooby’s picture

Here is an updated version.

Some changes are:
* Removed some redundant comments.
* Cleaned up info file.
* Added the value column as a field of its own for field/sort/filter
* Added relationship on node type.
* Stripped back the support for the fields on the value database column so that they are relationship only. Because the column has a bunch of different data, you get unexpected results using these as fields or filters etc. If you want to use them, you just add the relationship and then add the field/filter/argument on the joined table.
* Added relationships back from the other base tables, for users taxonomy terms & nodes.

I have run out of time tonight.
Things that definitely need doing still are:
* Add argument support for sid, module, field, value etc.
* Relationships from other base tables on the value column are broken because of the "'extra' => "subscriptions.field = 'tid'" parts. This is because the subscriptions table never exists because it has a different alias. I don't think there is any way to substitute in the table alias when using extra, so this will likely have to be a custom relationship handler.
* Much more testing. I have not yet tested all use cases.

rooby’s picture

Status: Needs work » Needs review

It needs work but may as well test it.

proweb.ua’s picture

#42
in RELATIONSHIPS add Content: Subscription or Content: Node type subscription

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'subscriptions.field' in 'on clause'

Subscriptions 7.x-1.1+14-dev

salvis’s picture

Status: Needs review » Needs work

Thanks for taking a look, proweb.ua!

Unfortunately, we have no real tests, so the green light really just means that the patch applies (which is necessary but far from sufficient).

ShaneOnABike’s picture

Status: Needs work » Needs review

I have made some progress on repairing the node section and will try and get the other parts repaired hopefully by the end of today! Then resubmit for community testing

EDIT: Although if there is a views master out there I would totally *appreciate* a second look.

ShaneOnABike’s picture

Hey there

This is my first crack at this so now subscribed content is working properly.

Each time I try to add the contextual filter it's not happy as the relationship with the recipient_uid seems to not link properly. Other than that it seems a bit better.

I used http://alvinalexander.com/drupal-code-examples/drupal-7-votingapi-projec... as a better reference on how to build the relationships. It seems to be working more smoothly despite the recipient_uid issue.

Any views experts out there that can push this along .. it's sooo close but missing just a bit. And there's just a lack of documentation out there to really aid this (well for me at least).

For reference: Hardcoding the actual table can't be done b/c views uses aliases which was the reason this wasn't working properly.

ShaneOnABike’s picture

Actually I discovered that the issue I reported above only happens if the relationship isn't established first ... but otherwise it should work ok (I think). But let me know

ShaneOnABike’s picture

Alright so now the patch should properly support

* Contextual filters (subscribing author/user)
* Displaying subscribed content
** from subscribed content types
** from subscribed content
** from subscribed authors (which unless #2061685: Subscribe to all author content is changed this means subscribed authors posting content type X)

Test away!

salvis’s picture

Any reviewers out there?

rooby’s picture

Based on a diff of the patch in #42 & #47 I think there will still be problems as I found in #42.

With #42 you cannot create a view that shows all subscriptions (content types, content, terms, author al in one). To do so I think it will need a custom relationship plugin and without this there is a large gap in useful functionality.

Also, relationships from other base tables to the subscriptions table through the subscription value field needed work.

The relationships are quite complex and have a bit of work to go.

@ShaneOnABike, Am I right to assume these things are still issues in the latest version?

eugene.ilyin’s picture

jcam88’s picture

A simple work around for showing customized list of subscriptions in a view is to use Flag and Rules module. A flag to provide the subscribe/unsubscribe link. The rule triggers the subscribe/unsubscribe subscription module function.

You can then use views to create customized lists of flagged content, users subscriptions, a customized subscriptions dashboard, etc. by simply using the flag relationship.

pscl’s picture

Hi joecam41788,

Very interesting approach!
I am new to the flag and rules modules, so got stuck.

Could you please elaborate on the following sentence?

The rule triggers the subscribe/unsubscribe subscription module function.

I am setting up the rule. I set up the event fine, but when trying to set up the action, I cannot find any reference to "subscribe/unsubscribe" in the list of available actions.

Do I need to install any further modules or write some custom code in order to have subscribe and unsubscribe available in the actions menu?

Your help would be most appreciated!

pscl’s picture

Hi all,

It looks like what joecam41788 meant was that "The rule triggers the subscribe/unsubscribe action".

In order to have these actions available in the Rules UI, you need to install the Subscriptions Rules module.

jcam88’s picture

Ah, sorry about that! Forgot to mention Subscription_rules module. I had Oliver add a few new rule actions to the module. Should be available to download. The flexibility is nice.

Working on anonymous subscribing too! (Available soon)
Using flags, subscription_rules, session_api...
Anonymous users will click anonymous "subscribe or unsubscribe" flags. After the user creates an account or logs in: custom rule syncs flags to trigger subscription.

arpitr’s picture

Issue summary: View changes

patch #52 works for me, thanks @eugene.ilyin

jollysolutions’s picture

Status: Needs review » Reviewed & tested by the community

Tested and works fine for me. Can we get this into dev please?

Rodeo.be’s picture

When can we add #52 in the dev release?

Anybody’s picture

ASAP, I'd suggest?

vuil’s picture

Assigned: Unassigned » salvis
Status: Reviewed & tested by the community » Patch (to be ported)

The #52 patch can be committed to 7.x-1.x branch.

salvis’s picture

Assigned: salvis » Unassigned
Status: Patch (to be ported) » Needs work

The dependency on Subscription_Rules needs to go into the .info file, and since we don't know whether that module will have a D8 version, we can't really expect to port it to D8 as such. This means it has to go into the contrib directory.