Using RAW sql for zftable

[Deadringer87]

Hi, first of all, great product you have made here.

I was wondering if it is possible in the model to use some raw sql. I have used your examples as a basis for my application, but i have a few complicated queries which would be better suited to some raw sql.

    public function fetchMethodName($id) { 

        $sql = new Sql($this->adapter);
        $select = $sql->select();

         $select->from('tableName')
                ->columns(array('*'))
                ;
            return $select;

        }  

Can this be converted into some raw sql

select * from tableName where xxx create tableName if exists etc

[emiliolodigiani]

The select object can be converted into an sql string. You can read the details here: http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html

For example in zf2 you can do:

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);

$selectString = $sql->getSqlStringForSqlObject($select);

[Deadringer87]

Thanks for the reply!

I kinda understand this, but putting it into a working example is proving to be a little tricky for me. I would like to run the raw sql in my model, and still use zftable's functionalities.

The example below is basically a standard controller function which zftable uses. In my controller I have:

   public function ajaxindexAction()
{
    $id = (int) $this->params()->fromRoute('id', 0); //gets id from route to pass into method
    $table = new TableExample\Base();
    $table->setAdapter($this->getDbAdapter())
            ->setSource($this->getMethodName($id))
            ->setParamAdapter($this->getRequest()->getPost())
    ;
    return $this->htmlResponse($table->render());
}  

  private function getMethodName($id)
{    
    return $this->getModuleTable()->fetchMethod($id);
}

In my model I have the function which is passed the $id. This works fine!, in my Base.php file I will ofcourse call the fields I wish to display.

public function fetchMethod($id) { 
           $sql = new Sql($this->adapter);

            $select = $sql->select();
            $select->from('system')
                ->columns(array('*')) 
        }

My problem is, how can I write RAW sql here, and is it possible to do this integrated with zftable?

[Deadringer87]

An example of how I thought it might work is this:

            $dbAdapterConfig = array(
            'driver'   => 'Mysqli',
            'database' => 'dbName',
            'username' => 'root',
            'password' => 'password'
        );
            $dbAdapter = new Adapter($dbAdapterConfig);

            $sql = "SELECT * FROM tableName";

            $select = $this->adapter->query($sql, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

            return $select;

Ideally this could render with zftable, although i receive the error message

         This type of source is undefined

[dudapiotr]

Hi, Currently you have only one type of source (look below)

/**
 * 
 * @param \Zend\Db\Sql\Select |  $source
 * @return \ZfTable\AbstractTable
 * @throws \LogicException
 */
public function setSource($source)
{

    if ($source instanceof \Zend\Db\Sql\Select) {
        $source = new \ZfTable\Source\SqlSelect($source);
    } else {
        throw new \LogicException('This type of source is undefined');
    }

    $source->setTable($this);
    $this->source = $source;
    return $this;
}

Currently, You cant use raw SQL (becouse it's use \Zend\Db\Sql\Select object), but of course it's possible. You have to write own implementaion of source (extends AbstractSource).

On the second hand, I think each raw SQL can be write with Object SQL. If you want please send me your RAW sql and I'll convert it to Zend\Db\Sql\Select.

[Deadringer87]

Hi Dudapiotr, sadly that was my thought also, but it was worth a try seeing if it is possible.

The problem is my query is quite long, and contains a lot of statements which together form 1 large select statement.

An example here, if you are able to advise on a solution to create an object query like this then I should be able to do it myself. This is some of the complicated syntax.

$a = mysql_query("DROP table if exists tmp_table_a;");

$b = mysql_query("CREATE temporary tmp_table_a as
SELECT 
id as systemId
ifnull(MIN(system.startdate), '2222-02-02') as System_Start_Date,
MAX(ifnull(system.enddate, '2222-02-02')) as System_End_Date
from system join tableB ...

I do not have is knowledge of creating temporary and dropping tables to form such a complicated query with db\select(); I have also not been successful in finding a tutorial which explains this.

Thanks again for your time.

[Deadringer87]

Hi again Dudapiotr, just to confirm I found a solution to the issue.

I can use

  $this->adapter->query(' raw sql here to create/drop tables ');

I can then use the select object model to select from these temporary tables. I am not sure if this is the best method but it works forsure :)

Thanks for your support and great product :)