phpzend-framework2zend-db-select

ZF2 complex SQL where closes


How can I create complex WHERE closes in Zend\Db\Sql? Those with "COLLATE utf_general_ci" or "REGEXP". Also what is the way to feed it the query as a string I want?

Thank you!


Solution

  • An example of a full complex where clause would help but you can use Expression or Literal

    Initiate Adapter

    $adapter = new Zend\Db\Adapter\Adapter(array(
        'driver' => 'Mysqli',
        'database' => 'zend_db_example',
        'username' => 'developer',
        'password' => 'developer-password'
     ));
    
    $sql = new Zend\Db\Sql\Sql($adapter);
    
    $qi = function($name) use ($adapter) { return $adapter->platform->quoteIdentifier( $name ); };
    
    $qv = function($name) use ($adapter) { return $adapter->platform->quoteValue( $name ); };
    

    Literal

    $regexSpecification = '(\\"key\\":\\"value\\")';
    $sql->select()
            ->from('dbTable')
            ->where->literal( $qi('column1') . " REGEXP " . $qv($regexSpecification) )
            ->or->equalTo('column2', 'value');
    
    //SELECT `dbTable`.* FROM `dbTable` WHERE `column1` REGEXP '(\"key\":\"value\")' OR `column2` = 'value'
    

    Expression

        $expression = new Zend\Db\Sql\Predicate\Expression( "SHA2(".$qi('column2').", 512)" );
        $sql->select()
            ->from('dbTable')
            ->columns( array( 'hashed' => $expression ) )
            ->where( array( $qi('column1') . " COLLATE latin1_general_cs = " . $qv('value'))); 
    
    //SELECT SHA2(`column2`, 512) AS `hashed` FROM `dbTable` WHERE `column1` COLLATE latin1_general_cs = 'value'
    

    Adapter Executed Query

    $adapter->query( 'ALTER TABLE ADD INDEX('. $qi('foo') .') ON ( '. $qi('foo_column') .' )', Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE );
    

    Referneces:

    https://packages.zendframework.com/docs/latest/manual/en/modules/zend.db.adapter.html

    https://packages.zendframework.com/docs/latest/manual/en/modules/zend.db.sql.html