How to order where clause after leftjoin in query?: Fieldtype getMatchQuery for field='' -- how to search for empty/unset values?

[hellomoto]

public function getMatchQuery($query, $table, $subfield, $operator, $value) {
      if (!$subfield) $subfield = 'data';

      // search for empty/not empty:
      if ($subfield == 'data' && empty($value) && $operator == '!=') {
          // doesn't work for '=', same result
          $ft = new DatabaseQuerySelectFulltext($query);
          $ft->match($table, 'sf1', $operator, $value)->match($table, 'sf2', $operator, $value);
          return $query;
      }
      if ($subfield == 'data' && empty($value) && $operator == '=') {
          // return pages with no data?
      }
}

Presently it returns all pages where field in question is populated, whether searching for empty or not empty.

When field value is empty, there is no row representing it in the field table. How can I return those pages when searching `fieldname=''` if the page template has the field?

EDIT: I replied to this myself with details of issues I encountered trying to resolve it but essentially I can't figure out how to have the "where" method added to the query added ultimately AFTER the left join. The left join is aliased (or error that it isn't unique), but the where statement included with/after it always is included in the whole statement in the JOIN preceding the LEFT JOIN where the alias is introduced.

    static $n = 0;
    $_table = $table . '_li' . (++$n);

    $ft = new DatabaseQuerySelectFulltext($query);
    $query->leftjoin("$table AS $_table ON $_table.pages_id=pages.id")->where("$_table.pages_id IS NULL");

    return $query;

Resulting Lister query:

SELECT SQL_CALC_FOUND_ROWS pages.id,pages.parent_id,pages.templates_id  
FROM `pages`  
JOIN field_list AS field_list ON field_list.pages_id=pages.id 
AND (((field_list_li1.pages_id IS NULL ) )) 
LEFT JOIN field_list AS field_list_li1 ON field_list_li1.pages_id=pages.id 
WHERE (pages.templates_id=124) 
AND (pages.status<1024)
GROUP BY pages.id  
ORDER BY pages.created DESC;

so if that would-be WHERE AND clause preceding the LEFT JOIN would just come after it instead, it should work, but how?

[ryan]

@hellomoto It looks like you are building a custom Fieldtype? If so, can you paste in the contents of your getDatabaseSchema() method? Matching an empty value can depend on the schema, though usually is handled by an OR condition that matches an empty string or the non-presence of a row. Matching the non presence of a row is done by performing a LEFT JOIN on the field_* table along with a WHERE condition that matches "IS  NULL field_table_name.pages_id". It can be a little tricky, so luckily PW handles this situation for you internally, and doesn't require your Fieldtype to handle it unless you tell PW that you want it to…

To describe further, ProcessWire matches empty values automatically, and so may not even call your getMatchQuery() for most empty-value matching selectors. But if PW isn't correctly matching it for you, then you may need to implement your own "empty" match logic. PW asks your Fieldtype::isEmptyValue() method (if implemented) if you would like to handle that situation (details). If it sends a Selector object to your isEmptyValue() method and your method returns true, then you are telling PW you want to handle matching the empty value:

public function isEmptyValue(Field $field, $value) {
  if($value instanceof Selector) {
    // tells PW your getMatchQuery will match empty values
    return true; 
  }
  return parent::isEmptyValue($field, $value); 
}

So if you have a method in your Fieldtype like above, then your getMatchQuery() would have to have logic to match an empty value. I don't think your current getMatchQuery() would correctly match an empty value, so I'd need to see the schema to identify what would be needed. 

[hellomoto]

Thank you much. Here is the schema:

	public function getDatabaseSchema(Field $field) {
		$schema = parent::getDatabaseSchema($field); 
		$schema['data'] = "VARCHAR(21) DEFAULT ''"; // empty placeholder, required
		$schema['sf1'] = "CHAR";
		$schema['sf2'] = "INT UNSIGNED";

		return $schema;
	}

I added this now to getMatchQuery:

		if ($subfield == 'data' && empty($value) && $operator == '=') {
			// return pages with no data?
			$query->leftjoin($table)->where("IS NULL {$table}.pages_id");
			return $query;
		}

but in Lister get this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NULL field_list.pages_id ) )) LEFT JOIN field_list WH' at line 3

It looks as though this is the SQL query:

SELECT SQL_CALC_FOUND_ROWS pages.id,pages.parent_id,pages.templates_id  
FROM `pages`  
JOIN field_list AS field_list ON field_list.pages_id=pages.id AND (((IS NULL field_list.pages_id ) )) 
LEFT JOIN field_list 
WHERE ((pages.parent_id!=2 AND pages.parent_id NOT IN (SELECT pages_id FROM pages_parents WHERE parents_id=2 OR pages_id=2))) 
AND (pages.status<8192)
GROUP BY pages.id  
ORDER BY pages.modified DESC
LIMIT 0,25  -- [0.2ms] FAIL SQLSTATE[42000]

Thanks @ryan

[hellomoto]

Also tried (from fields in core):

			static $n = 0;
			$_table = $table . '_li' . (++$n);
			$query->leftjoin("$table AS $_table ON $_table.pages_id=pages.id");
			$query->where("($_table.pages_id IS NULL)");
			return $query;

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_list_li1.pages_id' in 'on clause'

Whole query:

SELECT SQL_CALC_FOUND_ROWS pages.id,pages.parent_id,pages.templates_id  
FROM `pages`  
JOIN field_list AS field_list ON field_list.pages_id=pages.id AND ((((field_list_li1.pages_id IS NULL) ) )) 
LEFT JOIN field_list AS field_list_li1 ON pages.id=field_list_li1.pages_id AND (field_list_li1.pages_id IS NULL) 
WHERE (pages.templates_id=124) 
AND (pages.status<1024)
GROUP BY pages.id  
ORDER BY pages.created DESC
LIMIT 0,25  -- [0.8ms] FAIL SQLSTATE[42S22]

So on line 3 before the AS comes in the AS pops up...

With $_table = $table:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'field_list'

getQuery():

SELECT   
LEFT JOIN field_list AS field_list_li1 ON pages.id=field_list_li1.pages_id 
WHERE (field_list_li1.pages_id IS NULL)

 

[hellomoto]

If I run this in DB it works

SELECT pages.id, pages.parent_id, pages.templates_id
FROM pages
LEFT JOIN field_list ON pages.id=field_list.pages_id 
WHERE /*(pages.templates_id=124) AND */(field_list.pages_id IS NULL)

I can't figure out how to implement it in getMatchQuery.

[hellomoto]

This returns all values (don't know how as I thought I'd tried it already and been getting nothing but errors):

if ($subfield === 'data' && $value === '' && $operator === '=') {
	// return pages with no data?
	static $n = 0;
	$_table = $table . '_li' . (++$n);
	$query->leftjoin("$table AS $_table ON pages.id=$_table.pages_id AND ($_table.pages_id IS NULL)");
	return $query;
}

so no error... but ineffective...

If I insert

			$ft = new DatabaseQuerySelectFulltext($query);
			$ft->match($table, 'pages_id', $operator, 'NULL');

first within the above condition, there are no results, and getQuery returns

SELECT   
LEFT JOIN field_list AS field_list_li1 ON pages.id=field_list_li1.pages_id AND (field_list_li1.pages_id IS NULL) 
WHERE field_list.pages_id=:pf21s0X

so the would-be where gets appended (I tried with value='IS NULL' also?), but what is the code that it outputs? :pf21s0X?

[ryan]

@hellomoto It looks to me like you can drop the use of DatabaseQuerySelectFulltext. I don't see any text columns or fulltext indexed columns in your query, and I don't think it's doing anything at present. That DatabaseQuerySelectFulltext class is primarily for doing partial matches on text columns, most often using MySQL's fulltext indexes. Your schema doesn't have any columns that would typically be used with this class, and I don't see any indexes on the columns. 

Currently I'm wondering if you even need a getMatchQuery() method? Your schema is pretty simple, and it seems like the core Fieldtype::getMatchQuery() should handle it fine. If not, can you give me an example of a selector that is not working? (remove or comment out your getMatchQuery method completely to test).

The only thing I can spot in your schema that confuses me is the CHAR column with no length specified. I've only ever seen CHAR(n), where n is the fixed length of the CHAR column. Would "CHAR" be the same as CHAR(0), which either holds a 0-length string or a null? If so, then this may be a reason to have your own getMatchQuery, as PW consider blank string and NULL to be the same when querying the DB. So a simpler solution might be to just change it to a TINYINT, CHAR(1) or ENUM, so that you can be explicit about the ON or OFF (1 or 0, etc.) value, rather than trying to distinguish between two empty values (blank vs. null). Then it should work with the core getMatchQuery. The sf1 and sf2 columns may need to be indexed for best performance though. 

[hellomoto]

Thank you, I tried removing the method & without DatabaseQuerySelectFulltext, but when searching for field='' no results are returned (nor if searching by subfield). Is there no way to reorder the where to be included after the leftjoin when returning this:

$query->leftjoin("$table AS $_table ON $_table.pages_id=pages.id")->where("$_table.pages_id IS NULL");
return $query;

? The where clause precedes the left join in the join clause before it that includes it?

...
JOIN field_list AS field_list ON field_list.pages_id=pages.id AND ((((field_list_li1.pages_id IS NULL) ) )) 
LEFT JOIN field_list AS field_list_li1 ON pages.id=field_list_li1.pages_id AND (field_list_li1.pages_id IS NULL) 
WHERE ...

 

[hellomoto]

I'm sorry @ryan , thank you for your help; returning false if($value instanceof Selector) in isEmptyValue does seem to be working.