Propel\Runtime\ActiveQuery\Criterion\Exception\InvalidValueException: Could not build SQL for expression: MENU_ITEM.LEFT_MENU_ITEM_ID > NULL

[francescocambi]

Hi!

I'm having troubles with this nested_set hierarchy:

DB:
MENU (ID, ...) MENU_ITEM (ID, ..., MENU_ID (used as scope for nested_set behavior), LEFT_MENU_ITEM_ID (used as left field), RIGHT_MENU_ITEM_ID (used as right field), LEVEL, ...)

FOREIGN KEYS MENU_ID -> MENU(ID) LEFT_MENU_ITEM_ID -> MENU_ITEM(ID) RIGHT_MENU_ITEM_ID -> MENU_ITEM(ID)

My code is: $this->menu = new \Menu(); $this->menu->setId(3); $this->menu->setName("test_null");

    $item1 = new \MenuItem();
    $item1->setId(11);
    $item1->setLabel("item_a");
    $item1->setUrl("url_a");
    $item1->setItemOrder(0);
    $item1->setHidden(false);
    $this->menu->addMenuItem($item1);

    $itema1 = new \MenuItem();
    $itema1->setId(12);
    $itema1->setLabel("item_a1");
    $itema1->setUrl("url_a1");
    $itema1->setItemOrder(0);
    $itema1->setHidden(false);
    $itema1->insertAsLastChildOf($item1);

    $itema2 = new \MenuItem();
    $itema2->setId(13);
    $itema2->setLabel("item_a2");
    $itema2->setUrl("url_a2");
    $itema2->setItemOrder(0);
    $itema2->setHidden(false);
    $itema2->insertAsLastChildOf($item1);

    $itema3 = new \MenuItem();
    $itema3->setId(14);
    $itema3->setLabel("item_a3");
    $itema3->setUrl("url_a3");
    $itema3->setItemOrder(0);
    $itema3->setHidden(false);
    $itema3->insertAsLastChildOf($itema2);

    $item2 = new \MenuItem();
    $item2->setId(15);
    $item2->setLabel("item_b");
    $item2->setUrl("url_b");
    $item2->setItemOrder(0);
    $item2->setHidden(false);
    $this->menu->addMenuItem($item2);

    $this->menu->save();

If I comment out last line, moving along the hiearchy is painless but when I'm trying to save I found this exception: Propel\Runtime\ActiveQuery\Criterion\Exception\InvalidValueException: Could not build SQL for expression: MENU_ITEM.LEFT_MENU_ITEM_ID > NULL

TRACE: /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/Criterion/BasicCriterion.php:100 /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/Criterion/AbstractCriterion.php:275 /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/Criteria.php:1848 /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/Criteria.php:2434 /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/ModelCriteria.php:1813 /var/www/html/vendor/propel/propel/src/Propel/Runtime/ActiveQuery/ModelCriteria.php:976 /var/www/html/generated-classes/Base/MenuItem.php:2931

Please tell me if I made mistakes or there's a bug.

[marcj]

Please post your schema.xml

[francescocambi]

<table name="MENU" idMethod="native" phpName="Menu">
<column name="ID" phpName="Id" type="INTEGER" size="4" primaryKey="true" required="true"/>
<column name="NAME" phpName="Name" type="VARCHAR" size="40" required="true"/>
<column name="DESCRIPTION" phpName="Description" type="VARCHAR" size="250"/>
<vendor type="mysql">
  <parameter name="Engine" value="InnoDB"/>
</vendor>
</table>
<table name="MENU_ITEM" idMethod="native" phpName="MenuItem">
<column name="ID" phpName="Id" type="INTEGER" size="4" primaryKey="true" required="true"/>
<column name="LABEL" phpName="Label" type="VARCHAR" size="30" required="true"/>
<column name="URL" phpName="Url" type="VARCHAR" size="400" required="true"/>
<column name="ITEM_ORDER" phpName="ItemOrder" type="TINYINT" required="true"/>
<column name="HIDDEN" phpName="Hidden" type="INTEGER" size="1" required="true"/>
<column name="MENU_ID" phpName="MenuId" type="INTEGER" size="4" required="true"/>
<column name="LEFT_MENU_ITEM_ID" phpName="LeftMenuItemId" type="INTEGER" size="4"/>
<column name="RIGHT_MENU_ITEM_ID" phpName="RightMenuItemId" type="INTEGER" size="4"/>
<column name="LEVEL" phpName="Level" type="INTEGER" size="4"/>
<column name="PAGE_ID" phpName="PageId" type="INTEGER" size="4"/>
<foreign-key foreignTable="MENU" name="MENU_FK" onUpdate="CASCADE" onDelete="CASCADE">
  <reference local="MENU_ID" foreign="ID"/>
</foreign-key>
<foreign-key foreignTable="PAGE" name="MENU_ITEM_PAGE_FK">
  <reference local="PAGE_ID" foreign="ID"/>
</foreign-key>
<foreign-key foreignTable="MENU_ITEM" name="LEFT_MENU_ITEM_FK" onUpdate="CASCADE" onDelete="CASCADE">
  <reference local="LEFT_MENU_ITEM_ID" foreign="ID"/>
</foreign-key>
<foreign-key foreignTable="MENU_ITEM" name="RIGHT_MENU_ITEM_FK" onUpdate="CASCADE" onDelete="CASCADE">
    <reference local="RIGHT_MENU_ITEM_ID" foreign="ID"/>
</foreign-key>
<index name="MENU_ITEM_MENU_FK">
  <index-column name="MENU_ID"/>
</index>
<index name="MENU_ITEM_PAGE_FK">
  <index-column name="PAGE_ID"/>
</index>
<index name="PARENT_FK">
  <index-column name="MENU_ITEM_ID"/>
</index>
<behavior name="nested_set">
    <parameter name="left_column" value="LEFT_MENU_ITEM_ID" />
    <parameter name="right_column" value="RIGHT_MENU_ITEM_ID" />
    <parameter name="level_column" value="LEVEL" />
    <parameter name="use_scope" value="true" />
    <parameter name="scope_column" value="MENU_ID" />
</behavior>
<vendor type="mysql">
  <parameter name="Engine" value="InnoDB"/>
</vendor>
</table>

[marcj]

You need root nodes to attach normal nodes onto. So either you define $item1 as root via $item1->makeRoot(); or you create a new node as root and attach all sub elements to this node.

[francescocambi]

I tried your solution, but it still doesn't work. Same exception thrown.