Zend Framework: How Do I 'auto' generate a SQL query correctly

Question

I want to generate the following SQL:

SELECT `rc`.*, `c`.`name` FROM `RunConfigurations` AS `rc` INNER JOIN `Clients` AS `c` ON rc.client_id = c.id WHERE (rc.client_id = ?) ORDER BY `rc`.`config_name` ASC

However I am getting:

SELECT `rc`.*, `c`.* FROM `RunConfigurations` AS `rc` INNER JOIN `Clients` AS `c` ON rc.client_id = c.id WHERE (rc.client_id = ?) ORDER BY `rc`.`config_name` ASC

The difference is I want c.name, not c.*
Using the following ZF PHP code:

public function fetchConfigurations($clientId = null, $order = 'rc.config_name ASC')
    {
        $db = $this->getDb();
        $stmt = $db->select()
                ->from(array('rc' => 'RunConfigurations','c.name'))
                ->join(array('c' => 'Clients'),'rc.client_id = c.id')
                ->order($order);

        if(is_numeric($clientId))
        {
            $stmt->where('rc.client_id = ?')
                ->bind(array($clientId));
        }

        $results = $db->fetchAll($stmt);
        if(sizeof($results) > 0)
        {
            $configs = array();
            foreach($results as $row)
            {
                $configs[] = $this->createRunConfigurationFromRow($row);
            }
            return $configs;
        }
        else
        {
            die($stmt->__toString());
            return null;
        }
    }

This is aggravating and I feel like I am missing something at either:

->from(array('rc' => 'RunConfigurations','c.name'))

or

->join(array('c' => 'Clients'),'rc.client_id = c.id')

and the ZF examples are not shedding any light on this.

Piro · Accepted Answer · 2010-08-05 22:15:50Z

1

You are so close! join() actually has a 3rd parameter in which you can supply the column names just like the 2nd parameter from from().

This would mean that ->join(array('c' => 'Clients'),'rc.client_id = c.id',array('name')) should generate the SQL you are looking for.

-- Quote from the Zend Framework manual:

The third argument to join() is an array of column names, like that used in the from() method. It defaults to "*", supports correlation names, expressions, and Zend_Db_Expr in the same way as the array of column names in the from() method.

answered Aug 5, 2010 at 22:15

Piro

2,4763 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Zend Framework: How Do I 'auto' generate a SQL query correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related