Use of relationships (joins) in Views

Question

I use Drupal 7. I have read dozens of questions that revolve around the need to incorporate relationships among tables in Views (even many where the person asking the question phrased it differently, and the person answering the question said "just use Relations in Views"). I have also read various books that nearly always use single tables in example of pulling data from databases. I rarely see any understandable explanation of the use of "relations" in views. What's the point of using a relational database if it is so difficult to use joins? Can anyone shed any light on this? I've read the explanation in Pro Drupal 7 Development, which is arcane at best.

Bruce

Answer 1

Views is an awesome query builder and formatter - all through the UI. In my past coding days I did that all manually and recently I started to appreciate the power of views.

Just go into views settings / basic and turn on "Show the SQL query". Then create a basic view of nodes. Now add fields and filters to see how the SQL changes. Add a relationship and you'll see how views adds the "SQL Joins" automatically.

When you add some "fields" it will automatically add some joins without you having to add relationships. For example add "last comment author" field to a node and views will automatically add:

INNER JOIN {node_comment_statistics} ...
LEFT JOIN {users} ....

When you want to add some fields that you don't see in the selection, for example "author of the node", you add the relationship "Content: Author - Relate content to the user who created it." Views adds the SQL:

INNER JOIN {users} ....

Now you'll have the user related fields available, for example "(author) User: Picture (Picture)".

So in summary: most views will pull data from multiple tables but most of that detail is hidden from the developers because views does it all for you. If you understand tables and SQL turn on the SQL output and see what views does. Sometimes all those "joins" are not optimal from an optimization standpoint ;-)

Answer 2

For future reference, quoting from https://www.acquia.com/blog/drupal-views-sql-analogy-easier-way-explain-views

The author explains:

• SELECT {fields list } is in the Fields section. FROM {tables list} -
• [This is a bit complex in Views, the base table is determined when we
• create a view - whether its node, users, terms, node revisions, files
• etc] INNER JOIN - is in the Advance Options > Relationships section
• in Views WHERE - is defined in the Filters & Contextual Filters
• sections ORDER By - is determined by Sort Criteria

Answer 3

You can modify views query by using hook_pre_execute, i.e.

function mymodule_views_pre_execute(view &$view) {
  if ($view->name == 'view_to_modify') {
    $query = $view->build_info['query'];
    $count_query = $view->build_info['count_query'];
    $query->condition(...);
    $query->addJoin(...);
    $count_query->condition(...);
  }
}

for the default view plugin, $query is an instance of SelectQuery class, which allows easy modifications