Add subquery with count, having, and orderby I just started working with Drupal 7 and I'm stuck at something I believe should be really easy (I could easily write it using plain SQL).

I have a query, rather complex (but that's not relevant) which I wanted to extend with hook_views_query_alter. What I need to achieve:

Based on base table users I need to:


Join a table node based on users.uid (many-to-one)
Then join a table node_param on node.id (one-to-one)
select count(node_param.id) WHERE node_param.value is greater than X
Order everything based on that count field


I've done research on Drupal website and stackoverflow, but couldn't find anything that would actually work, everything I've tried ended up with no results at all and I did build the query with SQL that returns results - therefore I believe my altered query gone wrong.

I will really appreciate your help here! :)

P.S. I can only modify this query using this hook!
So, from reading your question, it seems you're missing a fundamental step to data modeling with Drupal. The first thing you're going to want to do is to build out content types and fields for your data. This is done through the content types user interface. Once you have all of your data in node content types and fields, it becomes much easier to access this data in views. 

When it comes to creating joins in views, you'll want to use "relationships" under "advanced" on the right hand side of the views UI. Since you're new to Drupal, but not to SQL, you might find it helpful to turn on "Show the SQL Query" here: /admin/structure/views/settings

So, the big realization here is that you don't create custom tables in Drupal databases manually. There are use cases where using a node doesn't make sense. In that case, you'll want to create a custom content entity. This is not done manually in Drupal. While creating entities can be done through code, you're better off using this module to generate your tables:

https://www.drupal.org/project/eck

Finally, most Drupal developers prefer nodes for data storage because Nodes are special entities that have many wonderful features that almost all data in Drupal needs.