File Usage: Content relationship broken

the fix produces the following corrected query:

SELECT file_managed.filename AS file_managed_filename, file_managed.fid AS fid, file_managed.filesize AS file_managed_filesize, file_managed.uri AS file_managed_uri, node_file_usage.nid AS node_file_usage_nid, node_file_usage.title AS node_file_usage_title
FROM 
{file_managed} file_managed
LEFT JOIN {file_usage} file_usage ON file_managed.fid = file_usage.fid
LEFT JOIN {node} node_file_usage ON file_usage.id = node_file_usage.nid AND file_usage.type = 'node'
WHERE (( (node_file_usage.nid IS NOT NULL ) ))
LIMIT 10 OFFSET 0

actually, no. That then breaks my download_count module views. Rather this patch seems to work in all cases.

the current views 7.x-3.x-dev remains as originally reported (with 'extra' => array(array('field' => 'type', 'value' => 'node')),.

And your patch is identical to the one I originally provided-- the question remains as described in #3. Would be nice to get some more eyes on this.

Thanks for following up thinkyhead.

The query I get with the patch original patch is:

SELECT download_count.dcid AS dcid, download_count.id AS download_count_id, download_count.type AS download_count_type, file_managed_download_count.fid AS file_managed_download_count_fid, file_managed_download_count.filename AS file_managed_download_count_filename, uri AS uri
FROM 
{download_count} download_count
LEFT JOIN {file_managed} file_managed_download_count ON download_count.fid = file_managed_download_count.fid
LEFT JOIN {file_usage} file_managed_download_count__file_usage ON file_managed_download_count.fid = file_managed_download_count__file_usage.fid
LEFT JOIN {node} node_file_usage ON file_managed_download_count__file_usage.id = node_file_usage.nid AND file_usage.type = 'node'

Which also results in an error:

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

And my relationship definition is:

  // fid
  $data['download_count']['fid'] = array(
    'title' => t('File'),
    'help' => t('The file information of the downloaded file.'),
    'relationship' => array(
      'base' => 'file_managed',
      'base field' => 'fid',
      'handler' => 'views_handler_relationship',
      'label' => t('File'),
    ),
  );

I add that relationship, and then chain the 'file usage: content' relationship on top of that to get at the node title.

I've been banging my head against the wall trying to get this resolved for weeks, and no matter what I do it seems the only thing that works is #2.

I still get blank node titles for the patch in #10 against a fresh checkout of d7 dev. No error though.

Attached is a simple views export for the view I use to test this.

awesome-- thanks so much for taking time to explain the details andrew. I feel like I'm stabbing in the dark when I try to trouble shoot views issues like this and that type of explanation is really instructive.

And thanks for rolling the patch rlmumford!

I'm testing it now, and it seems to be fixing the issues.

I still have one odd issue, though fixable with a filter, that doesn't make sense to me.

I get the following query:

SELECT file_managed_download_count.uri AS file_managed_download_count_uri, download_count.id AS download_count_id, download_count.type AS download_count_type, node_file_usage.title AS node_file_usage_title, node_file_usage.nid AS node_file_usage_nid
FROM 
{download_count} download_count
LEFT JOIN {file_managed} file_managed_download_count ON download_count.fid = file_managed_download_count.fid
LEFT JOIN {file_usage} file_managed_download_count__file_usage ON file_managed_download_count.fid = file_managed_download_count__file_usage.fid
LEFT JOIN {node} node_file_usage ON file_managed_download_count__file_usage.id = node_file_usage.nid AND file_managed_download_count__file_usage.type = 'node'
LIMIT 10 OFFSET 0

However, the result set is still including files attached to user entities in spite of the LEFT JOIN {node} node_file_usage ON file_managed_download_count__file_usage.id = node_file_usage.nid AND file_managed_download_count__file_usage.type = 'node'.

That's probably specific to the download_count module, though so I'm not sure it should hold up this patch. Hopefully some others will test it out and we can rtbc this puppy.

Adding a (File) File Usage: Entity type (= node) filter produces the following query:

SELECT file_managed_download_count.uri AS file_managed_download_count_uri, download_count.id AS download_count_id, download_count.type AS download_count_type, node_file_usage.title AS node_file_usage_title, node_file_usage.nid AS node_file_usage_nid
FROM 
{download_count} download_count
LEFT JOIN {file_managed} file_managed_download_count ON download_count.fid = file_managed_download_count.fid
LEFT JOIN {file_usage} file_managed_download_count__file_usage ON file_managed_download_count.fid = file_managed_download_count__file_usage.fid
LEFT JOIN {node} node_file_usage ON file_managed_download_count__file_usage.id = node_file_usage.nid AND file_managed_download_count__file_usage.type = 'node'
WHERE (( (file_managed_download_count__file_usage.type LIKE 'node' ESCAPE '\\') ))
LIMIT 10 OFFSET 0

Which correctly returns only nodes. Still don't know why though.

rlmumford ftw-- I didn't realize that flag was tied to switching to an inner join. brilliant, lol. testing with some more complex views, but so far this seems to be the fix.

i've test this quite a bit with all sorts of complex views, and it seems to be the fix. It would be good if someone else on this thread also tested it so we can rtbc it.

bot tests pass, patch applies cleanly, retested again with some complex views and everything is working. New comments look good also.

looks gtg.

views still work and definitely easier read. looks good.

awesome... thanks everyone!