Executing manually built SQL queries - Is there an easier way?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Executing manually built SQL queries - Is there an easier way?

Stephen Rees-Carter
Hi all,

Someone please correct me if I've missed the easy way to do this!

Currently to build a manual SQL statement and execute it, it takes a couple
of extra steps which I don't understand or see the need for. For example,
let's run this query: SELECT count(id) AS `count` FROM `affected_version`

        $sql    = $table->getSql();
        $select = $sql->select()->columns(Array('count' => new
Expr('count(id)')));

        $statement = $sql->prepareStatementForSqlObject($select);
        $results   = $statement->execute();

        foreach ($results as $value) {
            \Zend\Debug\Debug::dump($value);
        }

The first two lines make sense.

The third and forth lines (prepare*() & execute()) seem needlessly verbose.
Can't it be simplified down into a single "execute() function on the Select
object?

Finally, I get back some weird Result object which I need to do yet another
operation on before I can even access the query data in a useful way.
Again, it seems needlessly verbose and not very useful. ZF1 made this
pretty easy from memory.

I personally would love to write code like this:

        $sql    = $table->getSql();
        $select = $sql->select()->columns(Array('count' => new
Expr('count(id)')));
        $results = $select->execute();

        \Zend\Debug\Debug::dump($results);

I'm sure there is a very good reason for this... But I don't have a clue
what it is.

Can anyone help me out here?

Thanks,
~Stephen

--
Stephen Rees-Carter ~ Valorin
http://stephen.rees-carter.net/
Reply | Threaded
Open this post in threaded view
|

Re: Executing manually built SQL queries - Is there an easier way?

samsonasik
You can use like this :

use Zend\Db\Sql\Select; <~ import this


    $result  = $this->select(function (Select $select) use ($id){
        $select->columns(array('count' => new Expr('count(id)')));
    });
 
    return $result;
Reply | Threaded
Open this post in threaded view
|

Re: Executing manually built SQL queries - Is there an easier way?

Stephen Rees-Carter
This post has NOT been accepted by the mailing list yet.
samsonasik wrote
You can use like this :

use Zend\Db\Sql\Select; <~ import this

    $result  = $this->select(function (Select $select) use ($id){
        $select->columns(array('count' => new Expr('count(id)')));
    });
 
    return $result;
I tried a variant of that originally, but it thows this lovely Exception:

Zend\Db\RowGateway\Exception\RuntimeException: While processing primary key data, a known key id was not found in the data array

I'm assuming that the TableGateway only likes queries where the primary field is returned - probably something to do with the RowGateway feature I've configured. This is the reason I was doing it as an independent SQL query...

Thanks though, I appreciated the quick reply :)

Thanks,
~Stephen
Reply | Threaded
Open this post in threaded view
|

Re: Executing manually built SQL queries - Is there an easier way?

cmple
Hey Stephen,
I had the same issue, so in order to simplify my code I had to create my own class for this:

<?php

namespace Application\Service\Db;

use Zend\Db\Adapter\Adapter,
    Zend\Db\ResultSet\ResultSet,
    Zend\Db\ResultSet\Row,
    Zend\Db\Sql\Insert,
    Zend\Db\Sql\Update,
    Zend\Db\Sql\Delete,
    Zend\Db\Sql\Select;

class SqlGateway {
       
        protected $dbAdapter;
        protected $lastInsertId;

        public function __construct(Adapter $dbAdapter) {
                $this->dbAdapter = $dbAdapter;
        }
       
        public function getAdapter() {
                return $this->dbAdapter;
        }
       
        public function select($tableName, array $columns = null){
                $select = new Select($tableName);
                if ( true === is_array($columns) ) {
                        $select->columns($columns);
                }
                return $select;
        }
       
        public function insert($tableName, array $values){
                $insert = new Insert($tableName);
                $insert->values($values);
                return $insert;
        }
       
        public function update($tableName, array $values){
                $update = new Update($tableName);
                $update->set($values);
                return $update;
        }
       
        public function delete($tableName, array $where = null){
                $delete = new Delete($tableName);
                if ( true === is_array($where) ) {
                        $delete->where($where);
                }
                return $delete;
        }
       
        public function fetchAll($sql) {
                $statment = $this->dbAdapter->createStatement();
            $sql->prepareStatement($this->dbAdapter, $statment);
            $resultSet = $statment->execute();
            if (  0 >= $resultSet->count() ) {
            return null;
            }
            $result = new \StdClass;
           
        foreach ( $resultSet as $key => $value ) {
            $result->{$key} = (object)$value;
        }
        unset($resultSet);
        return $result;
        }
       
        public function fetchRow($sql) {
        $statment = $this->dbAdapter->createStatement();
        $sql->prepareStatement($this->dbAdapter, $statment);
        $result = $statment->execute()->current();
        return ( false === empty($result) ? (object)$result : null );
        }
       
        public function execute($sql) {
                $statment = $this->dbAdapter->createStatement();
            $sql->prepareStatement($this->dbAdapter, $statment);
            $result = $statment->execute();
            if ( $sql instanceof Insert ) {      
               $this->lastInsertId = $this->dbAdapter->getDriver()->getConnection()->getLastGeneratedValue();
            }
            return $result;
        }
       
        public function getLastInsertId() {
           return $this->lastInsertId;
        }
       
}

Usage:

$result = $this->sqlGateway->fetchRow($this->sqlGateway->select('affected_version', array('count' => new
Expr('count(id)')))->where("something='true'"));
Reply | Threaded
Open this post in threaded view
|

Re: Executing manually built SQL queries - Is there an easier way?

ralphschindler
In reply to this post by Stephen Rees-Carter

> The third and forth lines (prepare*() & execute()) seem needlessly verbose.
> Can't it be simplified down into a single "execute() function on the Select
> object?

On the select object, probably not.  The Select object by itself does
not do SQL abstraction, only when prepared with a Sql object will you
get SQL abstraction.  The Select by itself will produce
ANSI-compatible-ish queries, specific to the adapter and the platform.

I think there is a feature request in there though.  Perhaps the Sql
object could have an execute() which would be similar in nature to
Zend\Db\Adapter\Adapter::query().  The downside is that you are throwing
away the statement produced in favor of just the result.  While that may
seem like what you're really after in most cases, this also means you
cannot change a parameter in the ParameterContainer and then execute
(again) and already prepared statement.


> Finally, I get back some weird Result object which I need to do yet another
> operation on before I can even access the query data in a useful way.
> Again, it seems needlessly verbose and not very useful. ZF1 made this
> pretty easy from memory.
>
> I personally would love to write code like this:
>
>          $sql    = $table->getSql();
>          $select = $sql->select()->columns(Array('count' => new
> Expr('count(id)')));
>          $results = $select->execute();
>
>          \Zend\Debug\Debug::dump($results);

I will see about this.  It would look more like this though (just
stabbing in the dark):

$select = $table->getSql()->select();
$select->columns(['count' => new Expr('count(id)')]);
$results = $table->getSql()->execute($select);

var_dump($results->toArray());

-ralph

--
List: [hidden email]
Info: http://framework.zend.com/archives
Unsubscribe: [hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Executing manually built SQL queries - Is there an easier way?

Stephen Rees-Carter
> I think there is a feature request in there though.  Perhaps the Sql
object could have an execute() which would be similar in nature to
Zend\Db\Adapter\Adapter::query().  The downside is that you are throwing
away the statement produced in favor of just the result.  While that may
seem like what you're really after in most cases, this also means you
cannot change a parameter in the ParameterContainer and then execute
(again) and already prepared statement.
>
> I will see about this.  It would look more like this though (just
stabbing in the dark):
>
> $select = $table->getSql()->select();
> $select->columns(['count' => new Expr('count(id)')]);
> $results = $table->getSql()->execute($select);
>
> var_dump($results->toArray());

Something like that would be perfect. It's nice and simple to understand
and means that if you just want to get the database result you can bypass
the extra steps.

Thanks,
~Stephen


On 10 November 2012 01:48, Ralph Schindler <[hidden email]> wrote:

>
>  The third and forth lines (prepare*() & execute()) seem needlessly
>> verbose.
>> Can't it be simplified down into a single "execute() function on the
>> Select
>> object?
>>
>
> On the select object, probably not.  The Select object by itself does not
> do SQL abstraction, only when prepared with a Sql object will you get SQL
> abstraction.  The Select by itself will produce ANSI-compatible-ish
> queries, specific to the adapter and the platform.
>
> I think there is a feature request in there though.  Perhaps the Sql
> object could have an execute() which would be similar in nature to
> Zend\Db\Adapter\Adapter::**query().  The downside is that you are
> throwing away the statement produced in favor of just the result.  While
> that may seem like what you're really after in most cases, this also means
> you cannot change a parameter in the ParameterContainer and then execute
> (again) and already prepared statement.
>
>
>  Finally, I get back some weird Result object which I need to do yet
>> another
>> operation on before I can even access the query data in a useful way.
>> Again, it seems needlessly verbose and not very useful. ZF1 made this
>> pretty easy from memory.
>>
>> I personally would love to write code like this:
>>
>>          $sql    = $table->getSql();
>>          $select = $sql->select()->columns(Array(**'count' => new
>> Expr('count(id)')));
>>          $results = $select->execute();
>>
>>          \Zend\Debug\Debug::dump($**results);
>>
>
> I will see about this.  It would look more like this though (just stabbing
> in the dark):
>
> $select = $table->getSql()->select();
> $select->columns(['count' => new Expr('count(id)')]);
> $results = $table->getSql()->execute($**select);
>
> var_dump($results->toArray());
>
> -ralph
>
> --
> List: [hidden email]
> Info: http://framework.zend.com/**archives<http://framework.zend.com/archives>
> Unsubscribe: fw-general-unsubscribe@lists.**zend.com<[hidden email]>
>
>
>


--
Stephen Rees-Carter ~ Valorin
http://stephen.rees-carter.net/