Unable to apply condition of not null (<> null) on field with fulltext index

Thanks for taking time to create the patch, however unfortunately it doesn't seem to work for me.

My steps were:

1. Applied patch in comment #5
2. Changed index type from string to fulltext
3. Reindexed search db

The results are the same as my original comment.

Sorry for the delay to respond. The patch required a small modification to make it work.

In your patch you added the following three lines to createFilterCondition():

// If the field is in its own table, we have to check for NULL values in
// a special way (i.e., check for missing entries in that table).
if ($f[1] === NULL && $field['column'] === 'value') {
  $query = $this->connection->select($field['table'], 't')
    ->fields('t', array('item_id'));
+  if (search_api_is_text_type($field['type'])) {
+    $query->condition('t.field_name', $f[0]);
+  }
  $cond->condition('t.item_id', $query, $not_equals ? 'IN' : 'NOT IN');
  continue;
}

I had to modify the enclosing if statement as follows:

if (($f[1] === NULL && $field['column'] === 'value') or ($f[1] === NULL && $field['type'] === 'text')) {

This is what works for me in the end:

// If the field is in its own table, we have to check for NULL values in
// a special way (i.e., check for missing entries in that table).
if (($f[1] === NULL && $field['column'] === 'value') or ($f[1] === NULL && $field['type'] === 'text')) {
 $query = $this->connection->select($field['table'], 't')
   ->fields('t', array('item_id'));
 if (search_api_is_text_type($field['type'])) {
   $query->condition('t.field_name', $f[0]);
 }
 $cond->condition('t.item_id', $query, $not_equals ? 'IN' : 'NOT IN');
 continue;
}

Thanks again for steering me in the right direction, and hope this feedback is helpful.

Ian

I put a print_r($field) inside if (search_api_is_text_type($field['type'])) { and here's what came out:

Array ( [table] => search_api_db_tm_search_multi_index_text [column] => user_field_link_twitter_url [type] => text [boost] => 1.0 )
Array ( [table] => search_api_db_tm_search_multi_index_text [column] => node_field_link_twitter_url [type] => text [boost] => 1.0 )

My query filters are set up as so:

$query_filter_has->condition('user:field_link_twitter:url', null, '<>');
$query_filter_has->condition('node:field_link_twitter:url', null, '<>');

In the configuration under "Select fields to index" I have these fields set as "Fulltext".

The field is a standard Drupal "Link" type - this is what the underlying table looks like:

mysql> describe field_data_field_link_twitter;
+-------------------------------+------------------+------+-----+---------+-------+
| Field                         | Type             | Null | Key | Default | Extra |
+-------------------------------+------------------+------+-----+---------+-------+
| entity_type                   | varchar(128)     | NO   | PRI |         |       |
| bundle                        | varchar(128)     | NO   | MUL |         |       |
| deleted                       | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id                     | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id                   | int(10) unsigned | YES  | MUL | NULL    |       |
| language                      | varchar(32)      | NO   | PRI |         |       |
| delta                         | int(10) unsigned | NO   | PRI | NULL    |       |
| field_link_twitter_url        | varchar(2048)    | YES  |     | NULL    |       |
| field_link_twitter_title      | varchar(255)     | YES  |     | NULL    |       |
| field_link_twitter_attributes | mediumtext       | YES  |     | NULL    |       |
+-------------------------------+------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

Hopefully this additional information helps!

Success! I applied the patch (2795245-5--switch_to_fulltext_type.patch) and followed your instructions (changed the fulltext to string, then back to fulltext).

Note that I also required the patch in this issue to be applied (2745655-5--fulltext_field_null_condition.patch).

My next question is - how best to apply these fixes in production (given I've got a working implementation via the patch in this issue and my own modifications). Should I hold out until a combined patch or new release is made?

Thanks for your attention to this issue - and let me know if I can test anything else for you.