mysql - UNIRSE IZQUIERDO en ZF2 usando TableGateway

frameworks zend-framework2

Question

Tengo una mesa

*CREATE TABLE IF NOT EXISTS `blogs_settings` (
  `blog_id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `meta_keywords` text NOT NULL,
  `theme` varchar(25) NOT NULL DEFAULT ''default'',
  `is_active` tinyint(1) NOT NULL DEFAULT ''1'',
  `date_created` int(11) NOT NULL,

  PRIMARY KEY (`blog_id`),
  KEY `owner_id` (`owner_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;*

Y la segunda mesa:

*CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(128) NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `birthday` date NOT NULL,
  `avatar_id` int(11) DEFAULT NULL,
  `user_level` tinyint(1) NOT NULL DEFAULT ''1'',
  `date_registered` int(11) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT ''0'',
  `is_banned` tinyint(1) NOT NULL DEFAULT ''0'',

  PRIMARY KEY (`user_id`),
  KEY `is_active` (`is_active`),
  KEY `user_level` (`user_level`),
  KEY `is_banned` (`is_banned`),
  KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;*

¿Cómo puedo seleccionar todos los campos de la tabla blogs_settings y unirme solo al campo ''nombre de usuario'' de la tabla de usuarios utilizando TableGateway en ZF2, en blogs_settings.owner_id = users.user_id . Gracias por adelantado. Su ayuda es muy apreciada.

EDITAR:

namespace Object/Model;

use Zend/Db/TableGateway/TableGateway;
use Zend/Db/Sql/Select;

class BlogsSettingsTable {

protected $tableGateway;
protected $select;

public function __construct(TableGateway $tableGateway) {
    $this->tableGateway = $tableGateway;
    $this->select = new Select();
}

public function getBlogs($field = '''', $value = '''') {
    $resultSet = $this->tableGateway->select(function(Select $select) {
                $select->join(''users'', ''blogs_settings.owner_id = users.user_id'', array(''username''));
            });

    return $resultSet;
}

public function getBlog($blogID) {
    $id = (int) $blogID;

    $rowset = $this->tableGateway->select(array(''blog_id'' => $id));
    $row = $rowset->current();

    if (!$row) {
        throw new Exception(''Could not find row with ID = '' . $id);
    }

    return $row;
}

public function addBlog(BlogsSettings $blog) {
    $data = array(
        ''owner_id'' => $blog->owner_id,
        ''title'' => $blog->title,
        ''meta_description'' => $blog->meta_description,
        ''meta_keywords'' => $blog->meta_keywords,
        ''theme'' => $blog->theme,
        ''is_active'' => $blog->is_active,
        ''date_created'' => $blog->date_created,
    );

    $this->tableGateway->insert($data);
}

public function deleteBlog($blogID) {
    return $this->tableGateway->delete(array(''blog_id'' => $blogID));
}

}

Con esto, ejecuta la siguiente consulta:

SELECCIONA blogs_settings . *, users . username COMO username DE blogs_settings INNER JOIN users EN blogs_settings . owner_id = users . user_id

pero el conjunto de resultados no contiene el campo de nombre de usuario de la tabla de ''usuarios'' unidos. Sin embargo, cuando ejecuto la consulta en phpmyadmin, todo está bien y tengo el campo ''nombre de usuario'' de la tabla ''usuarios'' unido. ¿Cuál es el problema?

EDIT 2 ok, ahora he intentado lo siguiente:

public function getBlogs() {
    $select = $this->tableGateway->getSql()->select();
    $select->columns(array(''blog_id'', ''interest_id'', ''owner_id'', ''title'', ''date_created''));
    $select->join(''users'', ''users.user_id = blogs_settings.owner_id'', array(''username''), ''left'');

    $resultSet = $this->tableGateway->selectWith($select);

    return $resultSet;
}

La consulta ejecutada es:

SELECT `blogs_settings`.`blog_id` AS `blog_id`, `blogs_settings`.`interest_id` AS `interest_id`, `blogs_settings`.`owner_id` AS `owner_id`, `blogs_settings`.`title` AS `title`, `blogs_settings`.`date_created` AS `date_created`, `users`.`username` AS `username` FROM `blogs_settings` LEFT JOIN `users` ON `users`.`user_id` = `blogs_settings`.`owner_id`

Cuando lo ejecuto en phpmyadmin, se une al campo de nombre de usuario de la tabla de usuarios. Cuando en zf2, no lo hace.

Aquí está el volcado de todo el objeto:

Zend/Db/ResultSet/ResultSet Object
(
[allowedReturnTypes:protected] => Array
    (
        [0] => arrayobject
        [1] => array
    )

[arrayObjectPrototype:protected] => Object/Model/BlogsSettings Object
    (
        [blog_id] => 
        [interest_id] => 
        [owner_id] => 
        [title] => 
        [meta_description] => 
        [meta_keywords] => 
        [theme] => 
        [is_active] => 
        [date_created] => 
    )

[returnType:protected] => arrayobject
[buffer:protected] => 
[count:protected] => 1
[dataSource:protected] => Zend/Db/Adapter/Driver/Pdo/Result Object
    (
        [statementMode:protected] => forward
        [resource:protected] => PDOStatement Object
            (
                [queryString] => SELECT `blogs_settings`.`blog_id` AS `blog_id`, `blogs_settings`.`interest_id` AS `interest_id`, `blogs_settings`.`owner_id` AS `owner_id`, `blogs_settings`.`title` AS `title`, `blogs_settings`.`date_created` AS `date_created`, `users`.`username` AS `username` FROM `blogs_settings` LEFT JOIN `users` ON `users`.`user_id` = `blogs_settings`.`owner_id`
            )

        [options:protected] => 
        [currentComplete:protected] => 
        [currentData:protected] => 
        [position:protected] => -1
        [generatedValue:protected] => 0
        [rowCount:protected] => 1
    )

[fieldCount:protected] => 6
[position:protected] => 
)

Arriba ... alguna idea?

Answer 1

Agregando a la respuesta de @samsonasik y abordando los problemas en sus comentarios. No podrá obtener los valores unidos de lo que se devuelve de esa declaración. Esa declaración devuelve el objeto modelo que no tendrá las filas unidas. Deberá ejecutarlo como SQL a un nivel que lo preparará como SQL sin formato y le devolverá cada fila resultante como una matriz en lugar de un objeto:

$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array(''column_name_yourtable''));
$sqlSelect->join(''othertable'', ''othertable.id = yourtable.id'', array(''column_name_othertable''), ''left'');

$statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
$resultSet = $statement->execute();
return $resultSet;

//then in your controller or view:

foreach($resultSet as $row){
    print_r($row[''column_name_yourtable'']);
    print_r($row[''column_name_othertable'']);
}

Answer 2

Como el OP no ha aceptado ninguna respuesta, intentaré dar la solución. Me enfrento a la misma solución que los estados OP y la única forma de solucionarlo es agregar esta línea a la clase del modelo (en este caso, podría ser ''Blogsetttings.php'').

$this->username= (!empty($data[''username''])) ? $data[''username''] : null;

debe agregar la línea anterior al método exchangeArray (). Espero eso ayude

Answer 3

Darle una oportunidad:

namespace Object/Model;

use Zend/Db/TableGateway/AbstractTableGateway;
use Zend/Db/Sql/Select;

class BlogsSettingsTbl extends AbstractTableGateway {
    public function __construct($adapter) {
        $this->table = ''blogs_settings'';
        $this->adapter = $adapter;
        $this->initialize();
    }

    public function fetchAll() {
        $where = array(); // If have any criteria
        $result = $this->select(function (Select $select) use ($where) {
                    $select->join(''users'', ''blogs_settings.owner_id = users.user_id'', array(''username''));
                    //echo $select->getSqlString(); // see the sql query
                });
        return $result;
    }
}

Agregue a ''getServiceConfig ()'' en Module.php:

''Object/Model/BlogsSettingsTbl'' => function($sm) {
    $dbAdapter = $sm->get(''Zend/Db/Adapter/Adapter'');
    $table = new BlogsSettingsTbl($dbAdapter); // <-- also add this to ''USE'' at top
    return $table;
},

Answer 4

Debe incluir el campo de nombre de usuario en el modelo de configuración de Blogs que se usa como modelo de BlogsSettingTable (The TableGateway)

class BlogsSetting {
    public $blog_id;
    public $interest_id;
    public $owner_id;
    public $title;
    public $meta_description;
    public $meta_keywords;
    public $theme;
    public $is_active;
    public $date_created;
    public $username;

    public function exchangeArray($data)
    {
        // Create exchangeArray
    }
}

Espero que esto ayude

Answer 5

En su clase heredada de AbstractTableGateway, puede usar Seleccionar con cierre de esta manera:

use Zend/Db/Sql/Select;
...
public function getAllBlockSettings()
{
    $resultSet = $this->select(function(Select $select) {
        $select->join(''users'', ''blogs_settings.owner_id = users.user_id'', array(''username''));
    });

    return $resultSet;
}

Answer 6

Esta es la necesidad exacta de las cláusulas Join y Where con tableGateway .

public function getEmployeefunctionDetails($empFunctionId) {
    $empFunctionId = ( int ) $empFunctionId;
    //echo ''<pre>''; print_r($this->tableGateway->getTable()); exit;
    $where = new Where();
    $where->equalTo(''FUNCTION_ID'', $empFunctionId);

    $sqlSelect = $this->tableGateway->getSql()->select()->where($where);    

    $sqlSelect->columns(array(''FUNCTION_ID''));
    $sqlSelect->join(''DEPARTMENTS'', ''DEPARTMENTS.DEPARTMENT_ID = EMPLOYEE_FUNCTIONS.DEPARTMENT_ID'', array(''DEPARTMENT_ID'',''DEPARTMENT_NAME''), ''inner'');
    $sqlSelect->join(''ROLES'', ''ROLES.ROLE_ID = EMPLOYEE_FUNCTIONS.ROLE_ID'', array(''ROLE_ID'',''ROLE_NAME''), ''inner'');

    //echo $sqlSelect->getSqlString(); exit;
    $resultSet = $this->tableGateway->selectWith($sqlSelect);

    if (! $resultSet) {
        throw new /Exception ( "Could not find row $empFunctionId" );
    }
    return $resultSet->toArray();
}

Answer 7

Si está utilizando TableGateway, puede seleccionar unirse de esta manera

$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array(''column_name''));
$sqlSelect->join(''othertable'', ''othertable.id = yourtable.id'', array(), ''left'');

$resultSet = $this->tableGateway->selectWith($sqlSelect);
return $resultSet;