CODEWITHSUNDEEP
mysqljoinzend-framework
Ashish Ranjan
Ashish Ranjan

Reputation: 12970

How to move a column of the "FIRST" table to the last in the result after a join query?

I am using zend, but the answer for the question can purely be given in mysql.

protected function _getView() {
    $gatewayTable = new Emailer_Model_DbTable_Gateway();
    $select = $this->_getDb()->select()
        ->from(
            array(
                'gatewayTable'  =>  $gatewayTable->getTableName()
            ),
            array(
                'id',
                'name',
                'requireAuth',
                'smtphost',
                'smtpPort',
                'sendLimit',
                'inError',
                'lastError',
                'errorCount'
            )
        );
    return $select;
}



$authTable = new Emailer_Model_DbTable_Auth();
    $select = $this->_getView();
    $select
        ->join(
            array(
                'authTable' =>  $authTable->getTableName()
            ),
            'gatewayTable.id = authTable.idEmailerGateway',
            array(
                "authId"    =>  "id",
                'username',
                'fromAddress',
                'fromName',
                "password"  =>  "encryptedPwd",
                'sentCount',
                'lastUsed'
            )
        );

When I execute a fetch on the resultant $select, I want the errorCount column of the first table to come at the last in $res.

$res = $this->_getDb()->query($select)->fetch();

Upvotes: 0

Views: 42

Answers (1)

Philipp
Philipp

Reputation: 2796

You mean you want to change the order of the columns in your result?

In SQL you can simply select the columns in the order you need. Like

SELECT t1.x, t1.y, t2.a, t2.b, t1.z 
FROM t1
JOIN t2

For Zend_Db there is the columns() method.

https://framework.zend.com/manual/1.10/en/zend.db.select.html

Paragraph Adding columns to an existing FROM or JOIN table

There may be cases where you wish to add columns to an existing FROM or JOIN table after those methods have been called. The columns() method allows you to add specific columns at any point before the query is executed. You can supply the columns as either a string or Zend_Db_Expr or as an array of these elements. The second argument to this method can be omitted, implying that the columns are to be added to the FROM table, otherwise an existing correlation name must be used.

With Example #11 Examples of adding columns with the columns() method

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'product_id')
             ->columns('product_name');

// Build the same query, specifying correlation names:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'p.product_id')
             ->columns('product_name', 'p');
             // Alternatively use columns('p.product_name')

Upvotes: 1

Related Questions