Missing query parameter for modifier %table.

[jtojnar]

Hi, I am trying to upgrade a project from ORM 4.0 to 5.0 but I get stuck on the following error:

Nextras\Dbal\Exception\InvalidArgumentException
Missing query parameter for modifier %table.

According to Tracy:

1. ItemReservationMapper::getStats() calls

   Nextras\Dbal\Connection::queryArgs('SELECT name, COUNT(*) AS cnt FROM %table AS [item_reservation] GROUP BY name')

2. which in turn calls Nextras\Dbal\SqlProcessor::process().

3. That calls preg_replace_callback

4. And the callback throws the exception.

I did not see anything relevant in the migration guide.

I noticed that all the Mapper examples use $this->toCollection but I am not sure how I could use that when the result of the query does not map to an entity. Do I need to create one?

Code of the upgraded model classes

/**
 * @extends \Nextras\Orm\Mapper\Dbal\DbalMapper<ItemReservation>
 */
final class ItemReservationMapper extends \Nextras\Orm\Mapper\Dbal\DbalMapper {
	public function getStats(): \Nextras\Dbal\Result\Result {
		$query = $this->builder()->select('name')->addSelect('COUNT(*) AS cnt')->groupBy('name')->getQuerySql();

		return $this->connection->queryArgs($query);
	}

	public function getTableName(): string {
		return 'item_reservation';
	}
}


/**
 * @extends \Nextras\Orm\Repository\Repository<ItemReservation>
 *
 * @property ItemReservationMapper $mapper
 */
final class ItemReservationRepository extends \Nextras\Orm\Repository\Repository {
	public static function getEntityClassNames(): array {
		return [ItemReservation::class];
	}

	/**
	 * @return array<string, int>
	 */
	public function getStats(): array {
		/** @var array<string, int> */
		$result = $this->mapper->getStats()->fetchPairs('name', 'cnt');

		return $result;
	}
}


/**
 * Item reservation for enforcing limits.
 *
 * @property int $id {primary}
 * @property string $name
 * @property ?Team $team {m:1 Team::$itemReservations}
 * @property ?Person $person {m:1 Person::$itemReservations}
 */
final class ItemReservation extends \Nextras\Orm\Entity\Entity {
	public function __construct(string $name) {
		parent::__construct();

		$this->name = $name;
	}
}

[jtojnar]

Looks like the following appears to work:

--- a/app/Model/Orm/ItemReservation/ItemReservationMapper.php
+++ b/app/Model/Orm/ItemReservation/ItemReservationMapper.php
@@ -14,6 +14,6 @@ final class ItemReservationMapper extends BaseMapper {
 	public function getStats(): Result {
 		$query = $this->builder()->select('name')->addSelect('COUNT(*) AS cnt')->groupBy('name')->getQuerySql();
 
-		return $this->connection->queryArgs($query);
+		return $this->connection->queryArgs([$query, $this->getTableName()]);
 	}
 }

Not sure if this is expected usage but I found that in https://github.com/nextras/dbal/blob/d772929e8189ac60470ec5054b8f49cb23846523/tests/cases/unit/ConnectionTest.phpt#L35.

[hrach]

Please could you post dump of $query = $this->builder()->select('name')->addSelect('COUNT(*) AS cnt')->groupBy('name'); i.e. the builder state, before conversion to SQL. I don't see the "bug" yet. The proposed workaround is not correct.

[jtojnar]

Here is the output of var_dump($query):

object(Nextras\Dbal\QueryBuilder\QueryBuilder)#318 (13) {
  ["platform":protected]=>
  object(Nextras\Dbal\Platforms\MySqlPlatform)#67 (2) {
    ["driver":"Nextras\Dbal\Platforms\MySqlPlatform":private]=>
    object(Nextras\Dbal\Drivers\Mysqli\MysqliDriver)#65 (5) {
      ["connection":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
      NULL
      ["connectionTz":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
      NULL
      ["logger":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
      NULL
      ["timeTaken":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
      float(0)
      ["resultNormalizerFactory":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
      NULL
    }
    ["connection":"Nextras\Dbal\Platforms\MySqlPlatform":private]=>
    object(Nextras\Dbal\Connection)#64 (8) {
      ["driver":"Nextras\Dbal\Connection":private]=>
      object(Nextras\Dbal\Drivers\Mysqli\MysqliDriver)#65 (5) {
        ["connection":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
        NULL
        ["connectionTz":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
        NULL
        ["logger":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
        NULL
        ["timeTaken":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
        float(0)
        ["resultNormalizerFactory":"Nextras\Dbal\Drivers\Mysqli\MysqliDriver":private]=>
        NULL
      }
      ["platform":"Nextras\Dbal\Connection":private]=>
      *RECURSION*
      ["sqlPreprocessor":"Nextras\Dbal\Connection":private]=>
      object(Nextras\Dbal\SqlProcessor)#66 (5) {
        ["modifiers":protected]=>
        array(23) {
          ["s"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(6) "string"
          }
          ["json"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(20) "pretty much anything"
          }
          ["i"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(3) "int"
          }
          ["f"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(14) "(finite) float"
          }
          ["b"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(4) "bool"
          }
          ["dt"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(17) "DateTimeInterface"
          }
          ["dts"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(17) "DateTimeInterface"
          }
          ["ldt"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(17) "DateTimeInterface"
          }
          ["di"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(12) "DateInterval"
          }
          ["blob"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(true)
            [2]=>
            string(11) "blob string"
          }
          ["_like"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(false)
            [2]=>
            string(6) "string"
          }
          ["like_"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(false)
            [2]=>
            string(6) "string"
          }
          ["_like_"]=>
          array(3) {
            [0]=>
            bool(true)
            [1]=>
            bool(false)
            [2]=>
            string(6) "string"
          }
          ["any"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(20) "pretty much anything"
          }
          ["and"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(5) "array"
          }
          ["or"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(5) "array"
          }
          ["multiOr"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(5) "array"
          }
          ["table"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(true)
            [2]=>
            string(12) "string|array"
          }
          ["column"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(true)
            [2]=>
            string(6) "string"
          }
          ["values"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(true)
            [2]=>
            string(5) "array"
          }
          ["set"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(5) "array"
          }
          ["raw"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(6) "string"
          }
          ["ex"]=>
          array(3) {
            [0]=>
            bool(false)
            [1]=>
            bool(false)
            [2]=>
            string(5) "array"
          }
        }
        ["customModifiers":protected]=>
        array(0) {
        }
        ["modifierResolvers":protected]=>
        object(SplObjectStorage)#68 (1) {
          ["storage":"SplObjectStorage":private]=>
          array(0) {
          }
        }
        ["identifiers":"Nextras\Dbal\SqlProcessor":private]=>
        NULL
        ["platform":"Nextras\Dbal\SqlProcessor":private]=>
        *RECURSION*
      }
      ["connected":"Nextras\Dbal\Connection":private]=>
      bool(false)
      ["nestedTransactionIndex":"Nextras\Dbal\Connection":private]=>
      int(0)
      ["nestedTransactionsWithSavepoint":"Nextras\Dbal\Connection":private]=>
      bool(true)
      ["logger":"Nextras\Dbal\Connection":private]=>
      object(Nextras\Dbal\Utils\MultiLogger)#69 (1) {
        ["loggers"]=>
        array(1) {
          ["00000000000000460000000000000000"]=>
          object(Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel)#70 (6) {
            ["count":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            int(0)
            ["totalTime":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            float(0)
            ["queries":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            array(0) {
            }
            ["connection":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            *RECURSION*
            ["doExplain":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            bool(true)
            ["maxQueries":"Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel":private]=>
            int(100)
          }
        }
      }
      ["config":"Nextras\Dbal\Connection":private]=>
      array(7) {
        ["driver"]=>
        string(6) "mysqli"
        ["host"]=>
        string(9) "localhost"
        ["database"]=>
        string(7) "entries"
        ["username"]=>
        string(4) "root"
        ["password"]=>
        NULL
        ["connectionTz"]=>
        string(6) "+01:00"
        ["options"]=>
        array(1) {
          ["charset"]=>
          string(7) "utf8mb4"
        }
      }
    }
  }
  ["args":protected]=>
  array(8) {
    ["select"]=>
    array(0) {
    }
    ["from"]=>
    array(1) {
      [0]=>
      string(16) "item_reservation"
    }
    ["indexHints"]=>
    NULL
    ["join"]=>
    NULL
    ["where"]=>
    NULL
    ["group"]=>
    array(0) {
    }
    ["having"]=>
    NULL
    ["order"]=>
    NULL
  }
  ["select":protected]=>
  array(2) {
    [0]=>
    string(4) "name"
    [1]=>
    string(15) "COUNT(*) AS cnt"
  }
  ["distinct":protected]=>
  bool(false)
  ["from":protected]=>
  array(2) {
    [0]=>
    string(6) "%table"
    [1]=>
    string(16) "item_reservation"
  }
  ["indexHints":protected]=>
  NULL
  ["join":protected]=>
  NULL
  ["where":protected]=>
  NULL
  ["group":protected]=>
  array(1) {
    [0]=>
    string(4) "name"
  }
  ["having":protected]=>
  NULL
  ["order":protected]=>
  NULL
  ["limit":protected]=>
  NULL
  ["generatedSql":protected]=>
  NULL
}

[hrach]

Oh, I haven't checked your code properly; this is not an Orm bug.

I noticed that all the Mapper examples use $this->toCollection but I am not sure how I could use that when the result of the query does not map to an entity. Do I need to create one?

Yes, if data does not map to en entity, toCollection() is useless.

  	$query = $this->builder()->select('name')->addSelect('COUNT(*) AS cnt')->groupBy('name')->getQuerySql();
  	return $this->connection->queryArgs($query);

The bug is here. If you're building a query using a builder, you should "execute" it differently:

The easiest correct way is:

$result = $this->connection->queryByQueryBuilder($builder);

If you want to use the queryArgs directly, you have to pass the builder's parameters explicitly:

return $this->queryArgs(
	$queryBuilder->getQuerySql(),
	$queryBuilder->getQueryParameters(),
);

whis is what queryByQueryBuilder() does.

So replace your code:

  	$builder = $this->builder()->select('name')->addSelect('COUNT(*) AS cnt')->groupBy('name');
  	return $this->connection->queryByQueryBuilder($builder);

[jtojnar]

Thanks, this makes sense.