Custom Table: xPDO::newQuery adds " `fieldname` = 0

[jaredfhealy]

Things I’ve checked

1. Different field names in the where query have the same result. Querying for non-existent fields on modUser outputs a correct query, so don’t think field name matters.
2. Deleting the generate schema php files, dropping the tables and running build again to re-generate everything.
3. Clearing the cache

Any Ideas why this could be?

Schema

I have a model with 3 custom table (objects) defined by this schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="grv" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" phpdoc-package="grv" phpdoc-subpackage="model" version="1.1">
  <object class="grvPackage" table="grv_packages" extends="xPDOSimpleObject">
    <field key="display" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <index alias="display" name="display" primary="false" unique="false" type="BTREE">
      <column key="display" length="" collation="A" null="false"/>
    </index>
    <field key="package_key" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <index alias="package_key" name="package_key" primary="false" unique="false" type="BTREE">
      <column key="package_key" length="" collation="A" null="false"/>
    </index>
    <field key="base_class" dbtype="varchar" precision="100" phptype="string" null="false" default="xPDOObject"/>
    <field key="platform" dbtype="varchar" precision="100" phptype="string" null="true" default="mysql"/>
    <field key="default_engine" dbtype="varchar" precision="100" phptype="string" null="false" default="InnoDB"/>
    <field key="phpdoc_package" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <field key="phpdoc_subpackage" dbtype="varchar" precision="100" phptype="string" null="false" default="model"/>
    <field key="version" dbtype="varchar" precision="100" phptype="string" null="false" default="1.1"/>
    <field key="sortorder" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
    <composite alias="Objects" class="grvObject" local="id" foreign="package" cardinality="many" owner="local"/>
  </object>
  <object class="grvObject" table="grv_objects" extends="xPDOSimpleObject">
    <field key="class" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <index alias="class" name="class" primary="false" unique="false" type="BTREE">
      <column key="class" length="" collation="A" null="false"/>
    </index>
    <field key="table_name" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <index alias="table_name" name="table_name" primary="false" unique="false" type="BTREE">
      <column key="table_name" length="" collation="A" null="false"/>
    </index>
    <field key="extends" dbtype="varchar" precision="100" phptype="string" null="false" default="xPDOSimpleObject"/>
    <field key="package" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default=""/>
    <index alias="package" name="package" primary="false" unique="false" type="BTREE">
      <column key="package" length="" collation="A" null="false"/>
    </index>
    <field key="sortorder" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
    <field key="raw_xml" dbtype="text" phptype="string" null="true" default=""/>
    <aggregate alias="Package" class="grvPackage" local="package" foreign="id" cardinality="one" owner="foreign"/>
    <composite alias="Fields" class="grvField" local="id" foreign="object" cardinality="many" owner="local"/>
  </object>
  <object class="grvField" table="grv_fields" extends="xPDOSimpleObject">
    <field key="column_name" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <index alias="column_name" name="column_name" primary="false" unique="false" type="BTREE">
      <column key="column_name" length="" collation="A" null="false"/>
    </index>
    <field key="dbtype" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <field key="precision" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default=""/>
    <field key="phptype" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <field key="allownull" dbtype="varchar" precision="100" phptype="string" null="false" default=""/>
    <field key="default" dbtype="varchar" precision="100" phptype="string" null="true" default=""/>
    <field key="sortorder" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
    <field key="object" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default=""/>
    <aggregate alias="Object" class="grvObject" local="object" foreign="id" cardinality="one" owner="foreign"/>
  </object>
</model>

Issue

1. I am able to create, update and delete records in the custom table.
2. When trying to query the table with parameters using “where” I noticed that I wasn’t getting results. I checked the SQL output, and the where is not built correctly.
   a. I ran the below as a snippet on a test page to compare
   b. I validated that setting the where to an invalid field on modUser has no effect and it outputs the expected SQL query.

Test page HTML

<style>
    pre {
        white-space: pre-wrap;       /* css-3 */
        white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
        white-space: -pre-wrap;      /* Opera 4-6 */
        white-space: -o-pre-wrap;    /* Opera 7 */
        word-wrap: break-word;       /* Internet Explorer 5.5+ */
    }
</style>

<pre>[[!TestGrv]]</pre>

Snippet contents

<?php
// Add the package
$output = "";
if (!$modx->addPackage('grv', MODX_CORE_PATH.'components/grv/model/')) {
    $output .= '<p><strong>There was a problem adding your package.</strong></p>';
    return $output;
}

$c = $modx->newQuery('grvObject');
$c->where(['package' => 'test']);
$c->prepare();
$output .= 'Custom Class: 
---------------
'.$c->toSQL();

$c = $modx->newQuery('modUser');
$c->where(['nofield' => 'test']);
$c->prepare();
$output .= '

modUser Class:
---------------
'.$c->toSQL();

// Return the output
return $output;

Results

[halftrainedharry]

The field package of the class grvObject is of type integer.

<object class="grvObject" table="grv_objects" extends="xPDOSimpleObject">
	...
	<field key="package" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default=""/>
	...
</object>

Why would you try to query it with a string value?

$c->where(['package' => 'test']);

[jaredfhealy]

Looks like troubleshooting sequence that had a gap in it. This originally came up using the REST API and trying to use “search=1” with the searchFields array set to “array(‘package’)”. I think this must be coming through as a string rather than an integer. Then when I tested it in a Snippet, I wasn’t thinking about types…

Thank you!