Conflict between Domain Access and Subscriptions: Column not found: 1054 Unknown column 'sq.nid'

Let's try to make it readable...

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sq.nid' in 'on clause':
SELECT c.cid AS cid, sq.sqid AS _subscriptions_sqid, sq.is_new AS _subscriptions_is_new
  FROM {comment} c
  INNER JOIN {subscriptions_queue} sq ON c.cid = sq.load_args
  LEFT OUTER JOIN {domain_access} da ON da.nid = sq.nid
  WHERE (c.nid = :db_condition_placeholder_0)
    AND (sq.uid = :db_condition_placeholder_1)
    AND (sq.load_function = :db_condition_placeholder_2)
    AND (c.status = :db_condition_placeholder_3)
    AND (((da.realm = 'domain_id' AND da.gid = :domain_id) OR (da.realm = 'domain_site' AND da.gid = 0)))
    AND ( EXISTS (
      SELECT na.nid AS nid FROM {node_access} na
        WHERE (( (na.gid = :db_condition_placeholder_4)
            AND (na.realm = :db_condition_placeholder_5) ))
          AND (na.grant_view >= :db_condition_placeholder_6)
          AND (c.nid = na.nid)
    ))
  ORDER BY c.cid ASC;
Array (
  [:db_condition_placeholder_0] => 1963
  [:db_condition_placeholder_1] => 1518
  [:db_condition_placeholder_2] => subscriptions_content_load_comment
  [:db_condition_placeholder_3] => 1
  [:domain_id] => 0
  [:db_condition_placeholder_4] => 0
  [:db_condition_placeholder_5] => all
  [:db_condition_placeholder_6] => 1 
)
in _subscriptions_content_load() (line 61 of modules/subscriptions/subscriptions_content.notify.inc).

Please try this.

Please try this...

Hmm, weird, some kind of caching problem...

I think the issue here is that this query has had the 'node_access' tag added to it but hasn't had the node table joined into the query. Domain Access (which runs using the node access system) is failing to find the node table and is therefore joining on the base table as a last resort.

The following patch (against 7.x-1.x HEAD) resolves the issue with Domain Access for me.

This works without the patch in comments 2/3 which adds the base_table meta data, though that patch doesn't do any harm either.

Thank you for your patch!

The following patch (against 7.x-1.x HEAD) resolves the issue with Domain Access for me.

... and #3 does not?

... and #3 does not?

Nope, it does not.

@petednz: Please review the patch in #4.

Ok, thanks.

I think the issue here is that this query has had the 'node_access' tag added to it but hasn't had the node table joined into the query. Domain Access (which runs using the node access system) is failing to find the node table and is therefore joining on the base table as a last resort.

I'm not trying to be difficult, but I'd like to learn something here...

The base table {comment} does have a 'nid' column. Falling back to the {comment} table and joining to it was exactly the intention of #3. Apparently, you and I would expect this to work. What message do you get with #3? Does domain_access still try to join with the {subscriptions_queue} table?

I don't think it should be necessary to join with the {node} table, but I may be wrong...

Hi Salvis,

I'm afraid I can't explain why node access attempts to join on the subscriptions_queue table and not the comments table despite you having made clear which table is which, except that to guarantee node access compatibility it is idiomatic to provide an inner join on the node table for node access to subsequently do its joins.

The best example of searching comments in a node access friendly way is provided by comment module itself here: http://api.drupal.org/api/drupal/modules!comment!comment.module/function...

HTH a bit.

Thanks for the pointer to comment_get_recent(). We could also argue that it joins {node} in order to check n.status. This may help node access, but

What is the purpose of the 'base_table' tag if it's not working?

I'm afraid I can't explain why node access attempts to join on the subscriptions_queue table

This is not "node access" — the join with the {domain_access} table must be done by the domain_access module in a hook_query_alter() implementation. What version of DA are you using?

Let's ask the DA maintainers:

It seems that DA implements hook_query_node_access_alter() and joins to the wrong table (see #1):

LEFT OUTER JOIN {domain_access} da ON da.nid = sq.nid

Should you not use the 'base_table' metadata (#3) to tell which table to join with?

node_query_node_access_alter seems to indicate that that'd be the way to go...

This JOIN is a special case that only applies to users who are not affected by node access. Disabling the "Enforce access control on administrators" setting should remove the error.

Note that the JOIN only fires if domain_admin_filter() returns TRUE, which it should not unless that setting is enabled.

Also note that domain_alter_node_query() does _not_ JOIN to the {domain_access} table. It replicates the logic from node_query_node_access_alter(), so that LEFT OUTER JOIN is coming from somewhere else.

So I'd look for implementations of hook_query_node_access_alter() in your codebase.

petednz / torrance123:
What version of DA are you using? I was hoping you'd chime in and set the correct version...

Note that Subs currently doesn't specify the base_table, which is an open Subs bug. However, torrance123 confirmed in #6 that adding it is not sufficient to fix the issue.

(Subs does not do any query altering.)

What version of DA are you using? I was hoping you'd chime in and set the correct version...

We were using domain version 7.x-2.19, however I've just upgraded that to 7.x-3.4 (which was quite a bumpy upgrade!).

I have removed the patch to subscription module in comment 4 and retest the cron. I am getting the original error, irrespective of domain version.

I'm really happy to help push further with this. Please let me know where to look.

Solved after much debugging. It turns out the join wasn't being introduced by domain_alter_node_query() (it wasn't even being called). So I searched for all other modules that were responding to either the node_access or comment_filter query tags and narrowed the culprit down to domain_adv module — which was previously used to resolve issues with domain and organic groups.

It's a bit embarrassing since I wrote a successor module for domain_adv (https://github.com/torrance/domain_og_bridge), but it looks like this particular site had never had domain_adv removed.

Sorry for the noise!

That was my assumption, too, but I wasn't aware that Domain Advanced had been ported to D7.

No worries.

I've pushed #3 to Subs.

Thanks!