Duplicated column name when using join with Zend_Db_table

zend-db-table

I have two tables. Both of them have a column named 'title'. When I use the following code snippet to join two tables, I can't access one of the title column.

    $select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
    $select->setIntegrityCheck(false);
    $select->join("service","service.id = lecture.service_id");
    return $select;

is there a way to access both columns?

Best Answer

You need to rename one of the columns so it doesn't conflict with the other. If you pass an array as the optional 3rd argument to join(), you can specify which columns to retrieve from the joined table. If one or more of the entries in the array is hashed, then the hash key will be used as the property name. With the code below, you can refer to the title column of the service table as "service_title" in your query results. If you want other columns from the service table, add them to the array, with or without hashes as desired.

$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false);
$select->join("service", "service.id = lecture.service_id",
              array("service_title" => "service.title");
return $select;

Related Solutions

Best way to do an Inner Join using the Zend Framework

Finally figured out how to do it. If you've got a better way, please let me know.

$db = Zend_Db_Table::getDefaultAdapter(); //set in my config file
$select = new Zend_Db_Select($db);
$select->from('groups', array('id', 'title')) //the array specifies which columns I want returned in my result set
    ->joinInner(
        'group_members',
        'groups.id = group_members.group_id',
        array()) //by specifying an empty array, I am saying that I don't care about the columns from this table
    ->where('user_id = ?', $userId);
$resultSet = $db->fetchAll($select);

This will return a table with only the id and title columns. The empty array() was the key to removing the columns I didn't care about. I could then do something with the result set.

foreach ($resultSet as $row) {
    //do something with $row->id or $row->title
}

R – How to go around this Zend_Form_Element_Select db error

Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accounts_status ' in 'field list'

Maybe I'm crazy, but looks to me like there's an extra space on that end of that 'accounts_status ' field name.

Best Answer

Related Solutions

Best way to do an Inner Join using the Zend Framework

R – How to go around this Zend_Form_Element_Select db error

Related Topic