While working on #412518: Convert taxonomy_node_* related code to use field API + upgrade path I found a critical limitation in field_attach_query().

1. It's impossible to do a join on another table - whether a field table or some other table, because we can't make assumptions about storage - that's somewhat by design I guess.

2. There's no support for order by - that means you can't do an ordered list of entities with a certain field value, even if the ordering criteria is stored in the same field.

So... I think we should add an $options parameter to field_attach_query() - which would allow us to pass in $options['order_by'] = array($column, $direction);

Having the options parameter will also make further operations possible (like joining on other field tables) without having to change the function signature.

field_sql_storage.module can implement order by in SQL, other storage engines might or might not allow for that.

Comments

bjaspan’s picture

bjaspan commented

And the slow march of turning field_attach_query() into a complete query builder continues... :-)

Seriously, we have to be very careful about restricting functionality we add to f_a_query() to things that can be implemented on all likely field storage engines. In particular, joining to other fields is probably *not* possible in general b/c we do not know where they are stored, so that has to be done by the caller. Even joining to other fields in the same storage mechanism is a problem if the storage mechanism is not a relational database.

catch’s picture

catch
he/him
Primary language English
commented

Well this is true. But we also have to be careful about requiring every possible query on field tables to be built with Views, or having modules special casing everything to field_sql_storage.module themselves.

yched’s picture

yched commented

"Order by" sounds like a reasonable expectation across storage engines.

It's always been clear that queries on multiple fields is out of scope, though.

catch’s picture

catch
he/him
Primary language English
commented
Title: field_attach_query() needs to support order by » Impossible to join or order by on field tables without using Views

Since orderby almost only makes sense when having a condition somewhere else (like n.status), retitling this issue for the more general problem of not being able to do anything apart from get a list of object ids from field storage without installing Views or making hard-coded assumptions to something which can disappear from under your feet.

yched’s picture

yched commented

There should be a way to ensure a given field is stored in the local SQL. Then, we could possibly let local storage engines expose table and column names, and let client code build their own queries. field_attach_query() is still useful for remote storage...

Not sure of the best way to do that, it probably relates to #443422: 'per field' storage engine.

catch’s picture

catch
he/him
Primary language English
commented

So local SQL storage engines exposing table names would be similar to the D6 CCK api functions which allowed Views to switch between per-content type and shared/multiple storage transparently right? Seems like that's necessary for Views to be possible in Drupal 7 at all.

yched’s picture

yched commented

"Seems like that's necessary for Views to be possible in Drupal 7 at all."
No, Views don't need this. Views would receive table names in data exposed by storage engines. There's no need to formalize a public API for this.

yched’s picture

yched commented
Status: Active » Closed (duplicate)

I guess that's a duplicate of #569224: Expose field storage details through field attach.