How do you build a query to get a series of nodes and their field values filtered by field values? Hopefully someone with Drupal 7 experience can point me in the right direction.

I'm creating a custom module to query some nodes and group and sort them by their field values.

I tried to use Views but it seems that it's not possible to get the results in the format I need so I've decide to query the database directly and build my own output.

What's the easiest way to query nodes in Drupal 7 as I notice there is a table for every field now which would seem to make any query that I have to write extremely complex?

Are there any methods to query node data and filter/group the results or can it only be done using raw SQL.

If the latter can you show me an example query that includes Drupal 7 Field values?
I second Jeremy's suggestion of trying harder with views. However, if you absolutely must write your query directly in code, you'll want to use EntityFieldQuery.

For example, to get a count of all nodes with a text field value of 'foo':

// Get field information
$field = field_info_field('field_name');
$query = new EntityFieldQuery;
$query-&gt;entityCondition('entity_type', 'node')
  -&gt;entityCondition('bundle', 'NODE TYPE')
  -&gt;fieldCondition($field, 'value', 'foo')
  -&gt;count();

$count = $query-&gt;execute();


If you don't want a count, but results instead, you would leave off the count part, and fetch results:

foreach ($query-&gt;execute() as $entity_type =&gt; $entities) {
  foreach ($entities as $entity_id =&gt; $entity) {
  ...
  }
}


The Commerce Guys also have a good blog post detailing a simple use case.