Using EntityFieldQuery's FieldCondition on file fields (or complex fields)

Question

I see plenty of examples of using FieldCondition on basic fields but in my case I would like to query if the file field holds a specific file name.

The structure in the entity object (with language undefined) is:

$entity->field_file['und'][0]['filename']

This is what I have tried:

$query = new EntityFieldQuery();

$query->entityCondition('entity_type', $type)
  ->propertyCondition('status', 1)
  ->fieldCondition('field_file', 'filename', $tokens[1], '=')
  ->addMetaData('account', user_load(1)); // Run the query as user 1.

$result = $query->execute();

Perhaps it is having trouble because of the ['und'][0] not sure? Thanks.

Here is the detailed error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_file0.field_file_filename' in 'where clause': SELECT field_data_field_file0.entity_type AS entity_type, field_data_field_file0.entity_id AS entity_id, field_data_field_file0.revision_id AS revision_id, field_data_field_file0.bundle AS bundle FROM {field_data_field_file} field_data_field_file0 INNER JOIN {eck_downloadable} eck_downloadable ON eck_downloadable.id = field_data_field_file0.entity_id WHERE (field_data_field_file0.field_file_filename = :db_condition_placeholder_0) AND (field_data_field_file0.deleted = :db_condition_placeholder_1) AND (eck_downloadable.status = :db_condition_placeholder_2) AND (field_data_field_file0.entity_type = :db_condition_placeholder_3) ; Array ( [:db_condition_placeholder_0] => rnd13_poster_pack.pdf [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => downloadable ) in field_sql_storage_field_storage_query() (line 582 of C:\www\sr14\modules\field\modules\field_sql_storage\field_sql_storage.module).

Answer 1

A file field only contains the fid (file ID) relating to the file. The extra data you see (filename, uri, etc.) are loaded by the file module, but are actually contained in the file_managed table, which is not a field table.

Since EntityFieldQuery doesn't support JOINs you're out of luck, unless you alter the query a bit later on, e.g.

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node');
$query->addTag('customtag');

...

function MYMODULE_query_customtag_alter(QueryAlterableInterface $query) {
  $query->innerJoin('file_managed', 'file_managed', 'file_managed.fid = field_data_field_name.fid');
  $query->condition('file_managed.filename', $something);
}

That will obviously need some tweaking for your specific situation.