CODEWITHSUNDEEP
phppostgresqlzend-framework2
matt
matt

Reputation: 2352

On ZF2 how can use an expression instead of a quoted table name?

How can I generate a query like this on PHP's ZF2?

SELECT timestamp FROM generate_series(0,20,5) AS timestamp

I have tried:

$select = $this->select()
    ->columns(array("timestamp"))
    ->from("timestamp" => array("generate_series(0,20,5)"))

Which generates: 

SELECT "timestamp" FROM "generate_series(0,20,5)" AS "timestamp"

But then it's looking for a table named generate_series(0,20,5) which doesn't exists

And also:

$select = $this->select()
    ->columns(array("timestamp"))
    ->from(array("timestamp" => new \Zend\Db\Sql\Expression('generate_series(0,20,5)')))

But it also generates an error:

SELECT "timestamp"."timestamp" AS "timestamp" FROM "Object" AS "timestamp"

Any ideas on how to turn the table name into an expression instead of a quoted table name?

Upvotes: 1

Views: 218

Answers (1)

BenRoob
BenRoob

Reputation: 1722

FYI, be aware there is another Zend\Sql\Predicate\Expression class in ZF2. If you look at the Select::from(), i think you can not pass any Expression object as table name.

So a quick solution would be to use a sub select. But it is some kind of ugly ;)

$prefixColumns = false;
$select2 = new Select();
$select2->columns(array('*'), $prefixColumns);
$select2->from('generate_series(0,20,5)');

$select = new Select();
$select->columns(array('timestamp')); // or disable prefixed columns like in $select 2
$select->from(array('tblAlias' => $select2));

This should provide the following query:

var_dump($select->getSqlString());

SELECT
  tblAlias.timestamp AS timestamp 
FROM (
  SELECT * FROM generate_series(0,20,5)
) AS tblAlias

Upvotes: 1

Related Questions