Writing SQL is hard. Especially in Drupal, where so much of the data structure is abstracted away. Because of its ability to generate SQL automatically, many developers love to use views to compose functionality, but most don't know some of the more advanced concepts such as context filters and relationships. Even fewer know how to properly express their SQL data structures in views to add to the awesome flexibility that views provides.

In this session I'll discuss the 3 most commonly missed SQL techniques in code, and how to compose them in Drupal code, but, we'll cover some basics first:

1) the different joins (did you know there are 3?)
2) why each join is important
3) how to tease data out of each kind of join

Following that, we all know how powerful Views is, and what it is capable of. But its surprising how many modules don't provide views support and leverage views for creating displays, or provide the tools for other to do the same.

The second half of this session covers how to use the hook_view_data() hook to declare the table structure of your module to views in meaningful ways, and show how to leverage some of the lesser known handlers in views - such as relationship handlers and join handlers to make really cool things happen.

Things that will be covered:
1) declaring product joins for awesome reports
2) declaring joins on composite keys
3) registering new relationships

This deals with the views 3.x api, and is directly applicable to both Drupal 7 and 8. I'll even cover some of the differences between the two.

By the end of this, you should be able to declare your module's table structure to views, understand how to express the relationships for views to properly connect the tables together, and create reporting displays that provide some advanced but meaningful results.