How to programmatically create a View on a node that displays SQL Query content?

Question

How can I programmtically create a View on node(s) that displays the content from a specific query?

For example I would like to display the following:

$args = array(':nid' => $node->nid);
$results = db_query('SELECT USER_ID FROM USER INNER JOIN node ON USER.OFFICE_ID =
  node.title AND node.nid = :nid', $args);

Essentially, I would like to grab the 'nid' from the current node and I would like the View to display all 'users' associated with the current node, as the query would return.

I have been able to achieve this through a custom module. But I have been requested to use Views instead.

EDIT:

I've done what Johnathan Elmore suggested and got the View to display the correct information if I manually enter the 'nid' in the 'Preview with contextual filters:' space. However, if I go to the node itself, it will not display the query content.

For example:

I have nid == 123 with url alias 'office-123', if I enter 123 in 'Preview with contextual filters:', it will display the correct query information. However, if I go to 'mysite/office-123' nothing is displayed.

How can I get this View to display when I go to the node url?

Answer 1

You can create a View block to show on the nodes, and use a contextual filter (Content: Nid) in the View to figure out which node to reference.

Then you can display the related user (user reference field?) for that node in the block.

In case you are using pathauto to auto alias your node URL's, you can setup the Contextual Filter field (Content: Nid) to determine the Nid from the URL: