How to convert SQL where LENGTH() condition to xPDO?

Question

How do I express the where clause in this:

select * from TABLE where LENGTH(COLUMN)  > 0

in xPDO?

$criteria->where(array('LENGTH(customer_po_num):>' => '0'));

does not work, it results in something like this:

`InventoryData`.`LENGTH(customer_po_num)` > '0' 

Answer 1

I wound up doing it like this:

$criteria->where(
array('`InventoryData`.`id` NOT IN (SELECT id FROM modx_gssi_inventory_data where LENGTH(customer_po_num) > 0)'));

Not sure is that is the nicest way of doing it, but it works.

Answer 2

For unsupported SQL operators, you can usually force your condition into the query by including it as a string rather than an array:

$criteria->where('LENGTH(customer_po_num) > 0');

Edit: working example provided below

$c = $modx->newQuery('modResource');
$c->where('LENGTH(pagetitle) > 0');
$c->select('pagetitle');
$c->prepare();
print_r($c->toSql());

Returns the following (working) SQL:

SELECT `pagetitle` 
FROM `ovo_site_content` 
AS `modResource` 
WHERE LENGTH(pagetitle) > 0

It works.