2

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'
Sean Kimball
  • 4,506
  • 9
  • 42
  • 73

2 Answers2

3

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.

okyanet
  • 3,106
  • 1
  • 22
  • 16
  • While this is one way to utilise `xPDOQuery::where`, this will not work as it will fail when validating the query for valid operators. – Ian Brindley Feb 12 '15 at 09:36
  • Including the condition as a string bypasses the validation; it will work but you obviously need to be more careful with it. – okyanet Feb 13 '15 at 02:35
  • I've added an example to show this does work as expected. Please don't claim an answer won't work without testing it first... – okyanet Feb 13 '15 at 02:43
  • I retract what I said about this not working however, passing a string does not bypass the validation. `xPDOQuery` contains an array of valid operators so this `$query->where("ST_Contains(GeomFromText('POLYGON(({$polygon}))'), Incident.location)");` would not work. – Ian Brindley Feb 13 '15 at 11:42
  • Apologies for stating that your method would not work, but I do maintain that you are incorrect about bypassing validation. – Ian Brindley Feb 13 '15 at 11:42
  • 1
    Ok, I should have said "operator parsing" instead of "validation" - string conditions are simply validated to contain one of the conditional operators. Your example would not work, but interestingly if you added any valid conditional operator to the condition it would: `$query->where("1 = 1 AND ST_Contains(GeomFromText('POLYGON(({$polygon}))'), Incident.location)");` https://github.com/modxcms/xpdo/blob/master/xpdo/om/xpdoquery.class.php#L762 – okyanet Feb 15 '15 at 05:54
  • That's a nice way passing validation! I was going to add some of the new mysql spatial functions to the list of valid operators. I'll use that trick until I find the time. Thanks :) – Ian Brindley Feb 15 '15 at 16:42
-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.

Sean Kimball
  • 4,506
  • 9
  • 42
  • 73
  • I'm not sure why you've been downvoted but this is really the only way (other than using `xPDO::query`) as `LENGTH` is not a supported operator and will fail when using `xPDOQuery::where`. – Ian Brindley Feb 12 '15 at 09:38
  • `xPDOQuery::where` works fine when passed this condition as a string. Amended my answer above to provide a working example. http://stackoverflow.com/a/26707405/1063532 – okyanet Feb 13 '15 at 02:44