I have a view that is supposed to list a load of nodes, but only those whose source domain is active. All of these nodes are assigned to All Affiliates, to a specific domain, and have the domain source set to the specific domain - so they are primarily nodes from the sub-domain (because of domain_source), but also available to all affiliates.

I have added a relationship in my view to the source domain, and then added a 'Domains: Valid' filter set to true, and set it to use the source domain relationship.

I would expect this to only list nodes that are assigned to an active source domain - However, the view lists all domains, including inactive ones.

If I change the 'Domains: Valid' filter to False, then only those inactive domain are listed - so clearly it knows which are inactive. It's just a problem when filtering on Domains: Valid = TRUE.

CommentFileSizeAuthor
#9 view.txt5.39 KBagentrickard

Comments

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

In my testing, I was able to duplicate this, but only because the {domain_source} table was empty. If the nodes you want are not recorded in the {domain_source} table, this relationship will fail.

In most cases, you don't need the relationship anyway.

mrfelton’s picture

mrfelton commented

In my case, domain_source is not empty for the nodes in question.

mrfelton’s picture

mrfelton commented

Also, I have tried without the relationship - but it still doesn't work.

mrfelton’s picture

mrfelton commented

Could this be related to #925268: It is not possible to set the domain source to an inactive domain - if you remember, this patch allows a node to have an inactive domain set as domain_source in the first place. I can't see why that would make any difference, as views should only be looking at the raw table data, I would have thought.

mrfelton’s picture

mrfelton commented

I have stripped my view down so it's about as basic as I can get it. Looking at the SQL that is generates shows something strange.

This is the SQL that is generated when Domains : valid = False:

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title,
domain_source.domain_id AS domain_source_domain_id,
node_domain_source__domain.valid AS node_domain_source__domain_valid
FROM node node
LEFT JOIN domain_source domain_source ON node.nid = domain_source.nid
INNER JOIN node node_domain_source ON domain_source.nid = node_domain_source.nid
LEFT JOIN domain_access node_domain_source__domain_access ON node_domain_source.nid = node_domain_source__domain_access.nid
LEFT JOIN domain_access node_domain_source_node_domain_source__domain_access ON node_domain_source.nid = node_domain_source_node_domain_source__domain_access.nid
LEFT JOIN domain node_domain_source__domain ON node_domain_source__domain_access.gid = node_domain_source__domain.domain_id
WHERE (node.type in ('pts_small_hotel', 'pts_small_nonhotel'))
AND (node_domain_source__domain.valid = 0)
GROUP BY nid ORDER BY node_title ASC;

And an excerpt from the view result:

+-------+----------------------------+-------------------------+----------------------------------+
| nid   | node_title                 | domain_source_domain_id | node_domain_source__domain_valid |
+-------+----------------------------+-------------------------+----------------------------------+
| 10337 | Compass Point Beach Resort |                      24 | 0                                | 
| 10336 | El Greco Hotel             |                      25 | 0                                | 
| 10269 | Hope Town Harbor Lodge     |                      43 | 0                                | 
| 10324 | Long Island Breeze Resort  |                      26 | 0                                | 
| 10310 | Orange Hill Beach Inn      |                      28 | 0                                | 
| 10256 | Pete & Gay Guest House     |                      40 | 0                                | 
+-------+----------------------------+-------------------------+----------------------------------+

And this is the SQL that is generated when Domains : valid = True:

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title,
domain_source.domain_id AS domain_source_domain_id,
node_domain_source__domain.valid AS node_domain_source__domain_valid
FROM node node
LEFT JOIN domain_source domain_source ON node.nid = domain_source.nid
INNER JOIN node node_domain_source ON domain_source.nid = node_domain_source.nid
LEFT JOIN domain_access node_domain_source__domain_access ON node_domain_source.nid = node_domain_source__domain_access.nid
LEFT JOIN domain_access node_domain_source_node_domain_source__domain_access ON node_domain_source.nid = node_domain_source_node_domain_source__domain_access.nid
LEFT JOIN domain node_domain_source__domain ON node_domain_source__domain_access.gid = node_domain_source__domain.domain_id
WHERE (node.type in ('pts_small_hotel', 'pts_small_nonhotel'))
AND (node_domain_source__domain.valid <> 0)
GROUP BY nid ORDER BY node_title ASC;

As you would expect, they are identical, expect for the condition node_domain_source__domain.valid =

And an excerpt from the view result:

+-------+-------------------------------+-------------------------+----------------------------------+
| nid   | node_title                    | domain_source_domain_id | node_domain_source__domain_valid |
+-------+-------------------------------+-------------------------+----------------------------------+
| 10201 | A Stone's Throw Away          |                      33 | 1                                | 
| 10424 | Beneby's Bayside Motel        |                       5 | 1                                | 
| 10265 | Bishop's Bonefishing Resort   |                      41 | 1                                | 
| 10337 | Compass Point Beach Resort    |                      24 | 1                                |

Notice how nid 10337 is now showing node_domain_source__domain_valid as 1, where before it was 0!

If I remove the condition altogether, the results look correct

+-------+-------------------------------+-------------------------+----------------------------------+
| nid   | node_title                    | domain_source_domain_id | node_domain_source__domain_valid |
+-------+-------------------------------+-------------------------+----------------------------------+
| 10201 | A Stone's Throw Away          |                      33 | 1                                | 
| 10424 | Beneby's Bayside Motel        |                       5 | 1                                | 
| 10265 | Bishop's Bonefishing Resort   |                      41 | 1                                | 
| 10337 | Compass Point Beach Resort    |                      24 | 0                                | 
| 10306 | Conch Sound Resort Inn        |                       9 | 1                                |

So how does the presence of that condition actually change the value in the column?? I really don't get it.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

I suspect it's the gid in the join; it really needs an extra parameter to ensure the join is against domain_id.

mrfelton’s picture

mrfelton commented

Hmm. definitely something up. Any ideas on how to get that extra parameter in?

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

The join syntax for hook_views_data supports an "extra" element.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
StatusFileSize
new view.txt5.39 KB

I looked at this again, and almost set up a duplicate View (attached). What I can't figure out is why your query has an INNER JOIN in it.

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title,
domain_source.domain_id AS domain_source_domain_id,
node_domain_source__domain.valid AS node_domain_source__domain_valid
 FROM node node 
 LEFT JOIN domain_source domain_source ON node.nid = domain_source.nid
 LEFT JOIN node node_domain_source ON domain_source.nid = node_domain_source.nid
 LEFT JOIN domain_access domain_access ON node.nid = domain_access.nid
 LEFT JOIN domain domain ON domain_access.gid = domain.domain_id
 LEFT JOIN domain_access node_domain_source__domain_access ON node_domain_source.nid = node_domain_source__domain_access.nid
 LEFT JOIN domain_access node_domain_source_node_domain_source__domain_access ON node_domain_source.nid = node_domain_source_node_domain_source__domain_access.nid
 LEFT JOIN domain node_domain_source__domain ON node_domain_source__domain_access.gid = node_domain_source__domain.domain_id
 WHERE (node.type in ('page', 'story')) AND (domain.valid <> 0)
 GROUP BY nid
  ORDER BY node_title ASC

Note: I'm using Views 3.

I also think you may not need the relationships at all.

dqd’s picture

dqd
Location London | N.Y.C | Paris | Hamburg | Berlin
commented
Issue summary: View changes
Status: Active » Closed (outdated)

I'll close this issue due to inactivity for 14 years and even if I would move it to the next nearest core version which is 7, there is upcoming EOL of Drupal 7 in January 2025. While the project's version for Drupal 7 will surely keep providing support for security issues and website breaking bug fixes for Drupal 7, we should try to minimize the open issues for Drupal 7 in the queue as much as possible. A big thanks around for the report and all the efforts in here. Feel free to re-open if you can provide a fix or found an additional related problem which increases the issue priority.