I have an EntityFieldQuery that uses tableSort() including a header for an integer type field. Before any click sorting, the table displays fine, inclduing some nodes which have no value for the integer field. When I click to sort by the integer field, nodes without a value are excluded in the new results.
This does NOT occur for text fields when there is no string.
Here's an example of my code. The integer field is 'field_year_implemented':
$header = array(
'title' => array('field' => 'title', 'type' => 'property', 'data' => 'Program Name', 'sort' => 'asc', 'specifier' => 'title'),
'field_year_implemented' => array('field' => 'field_year_implemented', 'type' => 'field', 'data' => 'Year', 'specifier' => array('field' => 'field_year_implemented', 'column' => 'value')),
);
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
->entityCondition('bundle', $nodetype)
->fieldCondition('field_nodereference', 'nid', $id)
->tableSort($header)
;
$result = $query->execute();
| Comment | File | Size | Author |
|---|---|---|---|
| #13 | 1662942-EFQ-doesnt-allow-left-join.patch | 3.2 KB | matt2000 |
| #8 | field_order_join-1057110-8.patch | 926 bytes | leksat |
Comments
Comment #1
matt2000 commentedThe $header/$headers typo above is not relevant. I just pasted the important parts in here. In the real code, the headers are passed to the $query with the correct name.edit: Typo fixed.Comment #2
matt2000 commentedCorrection:
This problem CAN be replicated with a text field instead of an integer. I thought it was the integer field because I use the same EFQ building function for other tables with only text fields, and don't experience this issue.
I'll keep digging, but any help would be appreciated....
Comment #3
matt2000 commentedMore findings:
It works when there is a row for the nodes in field table in the database, even if there's nothing in the value column.
But if the node does not have a row in field table for the field being sorted, it will not be included in the EFQ results.
(Feeds module will apparently create rows with no value for empty fields being imported, so I have a number of them.)
Comment #4
bojanz commentedThis is normal behavior since EFQ uses an INNER JOIN, if there are no matching rows in the field table, no row produced.
(a sort adds the join if it's not there)
Not sure if a LEFT JOIN could be used instead (what the consequences would be). chx?
Comment #5
chx commentedWait, how can be there no row in the field table?
Comment #6
bojanz commentedI just created a node and didn't fill any fields, got no rows created in the field tables.
Comment #7
leksat commentedIf I sort a query by a text field, only nodes with non empty field are present in result.
Is there some workaround?
Comment #8
leksat commentedI didn't find a workaround.
Maybe leftJoin is appropriate solution?
(field_sql_storage_field_storage_query())
Comment #9
leksat commentedCan someone explain why innerJoin is the default join method?
Comment #10
matt2000 commentedRan into this issue again today, because the following code does not behave as expected:
My hope was that this would allow me to retrieve records where 'some_field' is not set. I've not yet figured out any other way to do this, (but I have tried very hard yet) but I believe switching to a LEFT JOIN would allow it. So I'd like to renew Leksat's request: Is there a reason for not using LEFT JOIN as the default?
Comment #11
matt2000 commentedRe-titling to draw attention to the real problem.
Comment #12
chx commented#1611438: fieldOrderBy filters out results with empty field values
Comment #13
matt2000 commentedReally? By design a developer should NEVER be permitted to designate a condition that should be LEFT JOIN'd rather than using the default inner JOIN? What if it's been determined that a single LEFT JOIN'd query is faster than the two separate queries that would be required otherwise?
Would it really be so heinous to add to EntityFieldQuery a property for specifying which tables should be LEFT JOIN'd (or abstractly, which fields should be included with NULL values) which can be read by hook_field_storage_query()?
Demonstration of concept patch attached.
Comment #14
matt2000 commentedComment #15
chx commentedDid you read #1611438-14: fieldOrderBy filters out results with empty field values ? A LEFT JOIN is too slow.
Comment #16
chx commentedClosing in favor of #1662950: Document that EntityFieldQuery::fieldOrderBy will exclude Entities with empty field values..
Comment #17
matt2000 commentedThat issue is about Ordering specifically. This issue is about allowing **optional** LEFT JOINS for other purposes regardless of ordering, and as per #1611438-19: fieldOrderBy filters out results with empty field values the performance issues is not tied primarily to what kind of join you use, but rather what table comes first.
As for benchmarking INNER vs. LEFT, I tested several different fields myself, and the worst penalty I saw for LEFT JOIN rather than INNER JOIN was .03 sec for ~130k nodes. Most of my tests had a difference of .01 sec. One table with very few rows was faster as a LEFT JOIN. So it seems that performance is tied to the specific data, which is why the option to use a LEFT JOIN for specific tables should be provided to the developer.
This is a DX feature request, because using a LEFT JOIN is already possible via hook_query_tag_alter(), but it makes for over-complicated code that is difficult to read and maintain.
So I'd like to request that my 2-line code patch (most of it is doc) in #13 actually be reviewed on it's merits before this issue is closed without consideration. I would expect there might be a more elegant way to implement the idea, but I'm not yet convinced the idea is a bad one.
Or if an alternate solution can be proposed for allowing developers to include entities with NULL values in a single dynamic query, I will happily withdraw my proposed solution. Of course, that solution should be just as easy to implement, and have reproducible performance tests if it claims to perform better.
Comment #18
chx commentedLet's put it to CNR then.
Comment #19
j0rd commentedI'm particularly interested in this feature for "ORing" over multiple fields in my entity. This would require a left join. If there's another way to do this, please let me know.
Example use case:
I've read phase2's post regarding "ORs" with EntityFieldQuery, but it's hackish and would become difficult to maintain over time. They do demonstrate how it could be done though:
http://www.phase2technology.com/blog/or-queries-with-entityfieldquery/
As for performance. Depends on your storage engine. I assume this would be very fast with MongoDB as there would be no joins involved. At least I assume, never looked at MongoDB storage engine.
Personally I'm interested in having this functionality for code maintenance and not SQL performance, as I could simply do this with a normal gross SQL query which grows and grows and grows with every field I need to check against. Which is why entityfieldquery is here to help us isn't it?
Comment #20
amateescu commentedComment #20.0
amateescu commentedfixed typo
Comment #36
smustgrave commentedThank you for sharing your idea for improving Drupal.
We are working to decide if this proposal meets the Criteria for evaluating proposed changes. There hasn't been any discussion here for over 8 years which suggests that this has either been implemented or there is no community support. Your thoughts on this will allow a decision to be made.
Since we need more information to move forward with this issue, the status is now Postponed (maintainer needs more info). If we don't receive additional information to help with the issue, it may be closed after three months.
Thanks!
Comment #37
smustgrave commentedWanted to bump 1 more time before closing.
Comment #39
smustgrave commentedSince there's been no follow up and was a feature request going to close out. Can always be re-opened though! Am assigning credit for the work 14 years ago.
Thanks