Welcome to Abdul Malik Ikhsan's Blog

Zend Framework 2 : Zend\Db\Sql – Join Your Tables

Posted in Tutorial PHP, Zend Framework 2 by samsonasik on August 14, 2012

Zend Framework 2 has a component named Zend\Db that can simplify a way application interact with database. If you just has one table, you don’t need Sql statement, but if you have many, you need this. With Sql object, your paradigm of Query will be changed to Object Oriented Paradigm.


For example, i have an AlbumTable :

namespace Test\Model;

use Zend\Db\TableGateway\AbstractTableGateway,
    Zend\Db\Adapter\Adapter;

class AlbumTable extends AbstractTableGateway
{
    protected $table ='album';

    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->initialize();
    }
}

Then, i have a TrackTable which a child of AlbumTable :

namespace Test\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;

class TrackTable extends AbstractTableGateway
{
    protected $table ='tracks';

    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->initialize();
    }
}

We will join 2 tables, that the function join will be placed in TrackTable class, so we add the following :

use Zend\Db\Sql\Sql;

For conditional statement, you need :

use Zend\Db\Sql\Where;

and then, we create a function to getTrackByAlbumId :

public function getTrackByAlbumId($id=9)
{
    $sql = new Sql($this->adapter);
    $select = $sql->select();
    $select->from($this->table)
           ->join('album', 'tracks.album_id = album.id');

    $where = new  Where();
    $where->equalTo('album_id', $id) ;
    $select->where($where);

    //you can check your query by echo-ing :
   // echo $select->getSqlString();
    $statement = $sql->prepareStatementForSqlObject($select);
    $result = $statement->execute();

    return $result;
}

Done !, the complete source of TrackTable class will be like :

namespace Test\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;

use Zend\Db\Sql\Sql,
    Zend\Db\Sql\Where;

class TrackTable extends AbstractTableGateway
{
    protected $table ='tracks';

    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->initialize();
    }

   public function getTrackByAlbumId($id=9)
   {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from($this->table)
              ->join('album', 'tracks.album_id = album.id');

        $where = new  Where();
        $where->equalTo('album_id', $id) ;
        $select->where($where);

        //you can check your query by echo-ing :
        // echo $select->getSqlString();
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();

        return $result;
    }
}

How about Classes relation ?
Until Zend Framework version 2.0.0rc3, and as i know, $_dependentTables and $_referenceMap as ZF1’s way to relate more than one Table Class is not yet supported. So why i have to create 2 classes? it’s because i want to show you how to discipline your technique at building application.
Conclusions

  • You can use Zend\Db\Sql\Sql for Query or joining tables and change your Query paradigm to Object Oriented Paradigm
  • Zend\Db\Sql can join your tables, but can’t “create a relation” like ZF1’s way yet

[UPDATE]

You can use Simple usage like the following closure:

use Zend\Db\Sql\Select; <~ import this 
...........
public function getTrackByAlbumId($id=9)
{
    $result  = $this->select(function (Select $select) use ($id){
        $select->where(array('album_id'=>$id));
        $select->join('album', 'tracks.album_id = album.id');
     });
  
     return $result;
}

References :
1. https://github.com/ralphschindler/Zend_Db-Examples/blob/master/example-19.php
2. https://github.com/ralphschindler/Zend_Db-Examples/blob/master/example-16.php

Advertisements

91 Responses

Subscribe to comments with RSS.

  1. […] mal hier: Zend Framework 2 : ZendDbSql – Join Your Tables Gruß, […]

  2. […] würde ich direkt im Model (oder eigentlich im Model-Mapper) definieren. Ein gutes Beispiel dazu: Zend Framework 2 : ZendDbSql – Join Your Tables Tabledata Gateway Pattern allgemein: Zend Framework Es gab im alten ZF2 wenn ich mich recht […]

  3. sds said, on November 5, 2012 at 8:17 pm

    I am using SQL SERVER 2008,

    i try to use limit and offset in zend framework 2.

    But i got error, since in sql server the statement will be like this
    “select TOP 10 id from users”

    and not like
    “select id from users limit 10”

    how can i use

    ->limit(’10’);

    like this in SQL SERVER

    Please suggest me.

  4. samsonasik said, on November 6, 2012 at 2:36 am

    1. sqlserver doesn’t has limit function. add limit and offset to the select object, so sql will add rowcount() func between limit and/or offset.
    2. have you update your lib to the latest version, latest dev is in https://github.com/zendframework/zf2 , clone/pull from that repo
    3. from my unit test, it’s correct, please check : vendor/zendframework/zendframework/tests/ZendTest/Db/Sql/Platform/SqlServer/SelectDecoratorTest.php

    • sds said, on November 6, 2012 at 12:47 pm

      Thank you very much Mr Abdul Malik. Yes i have updated to latest version. Let me try it and get back to you if.

  5. Ori said, on November 10, 2012 at 7:01 pm

    Can I do joins using Zend\Db\TableGateway\TableGateway? Could you give me a few hints?

    Thanks

  6. samsonasik said, on November 11, 2012 at 12:03 am

    try :

    public function getJoin()
    {
          $select = new \Zend\Db\Sql\Select ;
          $select->from('album');
          $select->join('tracks','tracks.album_id = album.id');
      
         $resultSet = $this->tableGateway->selectWith($select);
            
         return $resultSet;
    }
    
    • Ori said, on November 11, 2012 at 5:50 am

      Worked, Thank you.

    • Jekin said, on December 4, 2012 at 8:11 pm

      Hello Samsonasik,
      thanks for giving this example.
      its too helpful.
      i need inner join and outer join and all joins.
      please give simple example like this.
      Thank you..,

      • samsonasik said, on December 4, 2012 at 8:33 pm

        for others join, try something like the following :

        $userprofile = $this->select(function (Sql\Select $select) {
            $select->join("zf2_countries","zf2_countries.country_id = zf2_users.country_id",
                                           array('country_name'), 'left');
            $select->join("zf2_states","zf2_states.state_id = zf2_users.state_id", 
                                            array('state_name'), 'left');
        });
        
      • Jekin said, on December 4, 2012 at 8:40 pm

        Thank you,
        but i’m using TableGateway..
        here i use your above example code and it works fine but it does not return value of tracks table.it only return user’s table value.

        $select = new \Zend\Db\Sql\Select ;
        $select->from(‘user’);
        $select->columns(array(’email’));
        $select->join(‘tracks’,’tracks.user_id = user.user_id’,array(‘tracks’));
        $result = $this->tableGateway->selectWith($select);
        return $result;

        and sql query result:
        SELECT “user”.*, “tracks”.”tracks” AS “tracks” FROM “user” INNER JOIN “tracks” ON “tracks”.”user_id” = “user”.”user_id”

        i want tracks column from tracks table.
        Thank you.

      • samsonasik said, on December 4, 2012 at 10:50 pm

        try :

        $select = new \Zend\Db\Sql\Select ;
        $select->from(‘user’);
        $select->columns(array(’email’));
        $select->join(‘tracks’, "tracks.user_id = user.user_id", array(‘tracks’), ‘left’);

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

        // return $resultSet;

      • Jekin said, on December 5, 2012 at 11:18 am

        Thank you samsonasik,
        it’s work perfectly.

      • samsonasik said, on December 5, 2012 at 12:14 pm

        you’re welcome

      • Jekin said, on December 5, 2012 at 5:00 pm

        Hello,
        I’m using zend framework 1.12 and i need to configure my own library which is extend zend library.
        my controller is like:
        class IndexController extends My_Controller_BackendAbstract {}

        Library is located at myproject/library/My

        and my question is that,how to configure application.ini file and index.php file?
        currently i write a line in application.ini
        autoloadernamespaces.msingi[] = “My_”

        but it does not work.it gives me server error.
        Please help me to solve this problem.
        Thank you.

      • samsonasik said, on December 5, 2012 at 5:07 pm

        maybe take a look my post about zf1 : https://samsonasik.wordpress.com/2009/07/24/membuat-library-sendiri-di-zend-1-8-4/ ( indonesian language 😀 )

      • Jekin said, on December 5, 2012 at 5:11 pm

        Thank you,
        still not working.
        it’s gives me server error…

      • samsonasik said, on December 5, 2012 at 5:33 pm

        I have no idea in zf 1.12.

      • Jekin said, on December 7, 2012 at 1:02 pm

        Hello,
        i’m using zend 1.12
        how can i registering plugin resources?
        which files need to change?
        please help me to solve this bug:
        Uncaught exception ‘Zend_Application_Bootstrap_Exception’ with message ‘Resource matching “Cache” not found’
        Thank you.

      • samsonasik said, on December 7, 2012 at 9:38 pm

        I have no idea in ZF 1.12 :), sorry.

    • jcropp said, on February 28, 2014 at 6:15 am

      Thanks for this information, samsonasik. You’ve presented these concepts in way that’s easy to follow. I’m new to ZF2, and my next challenge is implementing the join in a module.

      I’ve followed the tutorial application in the ZF2 User Guide. In my module I’ve simplified the indexAction() in my controller because I only want to see the single record in my index view:

      public function indexAction()
      {
      $id = (int) $this->params()->fromRoute(‘id’, 0);
      $album = $this->getAlbumTable()->getAlbum($id);
      return array(
      ‘albumID’ => $album->id,
      ‘albumArtist’ => $album->artist,
      ‘albumTitle’ => $album->title,
      );
      }

      Next, I want to use more complex sql statements to join tables and filter the data in the manner that you have laid out; so I’ve added your getJoin() function to my AlbumTable class.

      In order to call the data that results from the getJoin() function, I’ve changed the indexAction function from:

      $album = $this->getAlbumTable()->getAlbum($id);

      to:

      $album = $this->getAlbumTable()->getJoin($id);

      The next step is where I need help. The tutorial prescribes using ServiceManager to configure the table gateway and inject into the AlbumTable by adding the following function to the Module class:

      public function getServiceConfig()
      {
      return array(
      ‘factories’ => array(
      ‘Album\Model\AlbumTable’ => function($sm) {
      $tableGateway = $sm->get(‘AlbumTableGateway’);
      $table = new AlbumTable($tableGateway);
      return $table;
      },
      ‘AlbumTableGateway’ => function ($sm) {
      $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
      $resultSetPrototype = new ResultSet();
      $resultSetPrototype->setArrayObjectPrototype(new Album());
      return new TableGateway(‘album’, $dbAdapter, null, $resultSetPrototype);
      },
      ),
      );
      }

      I know I can’t use this. I just don’t know what to replace it with. Can you offer any advice?

  7. Najla said, on December 30, 2012 at 11:07 am

    thanks for your articles.. very helpful.. 🙂

  8. Nicky said, on January 3, 2013 at 4:05 pm

    Hi samsonasik!

    I try the follow code;

    $select = new \Zend\Db\Sql\Select;
    $select->from(‘websites’);
    $select->join(‘websites_statistics’,’websites_statistics.website_id = websites.id’);
    $select->where(array(‘websites.website’ => ‘google.com’));

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

    echo $select->getSqlString();
    var_dump($resultSet);
    exit();

    What I just get is an empty set. When I execute the sqlString in my MySqlWorkBench it returns me a record.
    What do I do wrong? (unfortunately, I get already an empty result set when I comment out the join).

  9. Aamir said, on February 5, 2013 at 6:18 pm

    How make query like this
    “select column_name from table_name where id=”some id”” using table gateway..please help me

    • samsonasik said, on February 5, 2013 at 9:51 pm
      $sqlSelect = $this->tableGateway->getSql()->select();
      $sqlSelect->columns(array('column_name'));
      
      $select = $this->tableGateway->selectWith(sqlSelect);
      return $select;
      
  10. Aamir said, on February 6, 2013 at 1:38 pm

    Thank you so much…….

  11. Aamir said, on February 6, 2013 at 7:21 pm

    😉

  12. Vrushal said, on February 6, 2013 at 9:48 pm

    See I have Following table and trying for join, it is working fine, But I don’t know what to pass to $this->getMock function for phpUnittesting.

    class AlbumTable {

    protected $tableGateway;

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

    public function fetchAll() {
    $sql = new Sql($this->tableGateway->getAdapter());
    $select = $sql->select();
    $select->from(‘AlbumTable’)->columns(array(‘account_id’, ‘account_name’, ‘account_type_id’, ‘currency_id’, ‘country_id’, ‘external_id’, ‘notes’, ‘managing_account_id’, ‘status’))->join(array(‘AT’ => ‘account_type’), ‘account.account_type_id = AT.account_type_id’);
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
    }

    I want to write a phpUnit test for the same :

    public function testFetchAll()
    {
    $resultSet = new ResultSet();
    $mockTableGateway = $this->getMock(‘Zend\Db\TableGateway\TableGateway’,
    array(‘select’), array(), ”, false);
    $mockTableGateway->expects($this->once())
    ->method(‘select’)
    ->with()
    ->will($this->returnValue($resultSet));

    $albumTable = new AlbumTable($mockTableGateway);

    $this->assertSame($resultSet, $albumTable->fetchAll());
    }

    Let me know the parameter I have to pass to $this->getMock() function.

  13. Vrushal said, on February 15, 2013 at 1:41 am

    Any update on this

  14. vian said, on April 2, 2013 at 10:28 am

    samsonasik bagaimana untuk select “NOT IN”, karena yg ada hanya “IN”. sy sudah coba pakai literal tapi masih error dalam penentuan parameternya. thx

    • samsonasik said, on April 2, 2013 at 5:42 pm

      coba update ZF 2 nya ke yg terbaru. skrg udah support 😉

  15. Rocco said, on May 7, 2013 at 5:57 pm

    Hello and congratulations for the blog.

    I followed the example based on the official site for the construction of the album.
    Then I integrated caching with Hydrator and changed the adapter used instead of Tablegateway.

    Now my problem is two or more mapper table.
    Which file edit and add to integrate it in the best way possible?

    The official documentation does not mention it.

    It useful to use an adapter dynamically?
    There are examples to work with?

  16. Rocco said, on May 10, 2013 at 12:53 am

    When i “join” two tables and i rename one or more fields is obligatory map the renamed field in the exchangeArray() function ?

    Thanks

    • samsonasik said, on May 11, 2013 at 12:32 pm

      setArrayObjectPrototype(new ClassModel()); is optional, you can use HidratingResultSet if you’re working with join functionality.

  17. Vimal raj said, on July 7, 2013 at 11:53 pm

    In ZF2 how to execuute OR condition in WHERE clause

    Hi i am really confused with this , please help me ,

    In my model

    $rowset = $this->tableGateway->select(array(‘username’ => $identifier));
    $row = $rowset->current();
    return $row;

    It executes the following query

    SELECT * FROM member WHERE username='<>’;

    but i want execute the following query

    SELECT * FROM member WHERE username='<>’ OR id_customer='<>’;

    What are the changes i have to make in model file.

    and please suggest usefull blog regarding this, i cant find answer for this ZF2 Documentation

    • samsonasik said, on July 8, 2013 at 3:56 pm
      $sql    = $this->tableGateway->getSql();
      $select = $sql->select();
      
      $select->where
                      ->NEST->
                             equalTo('username', $username)
                                  ->OR->
                             equalTo('id_customer', $id_customer)
                      ->UNNEST;
      
      return $this->tableGateway->selectWith($select);
      
  18. slimBa said, on July 30, 2013 at 4:49 pm

    Really good blog , very helpful .

    i have two table
    Table sale :

    id_sale int
    id_projet int
    price float
    date date

    Table sale_ligne:

    id_sale_ligne int
    id_sale int FK_SALE
    id_projet int
    price float
    date date

    i m asking how can i insert a record on sale_line with same id_sale when i make a new record on sale . I know it’s a large question but i m really stuck here and i can find this exemple on official ZF documentation , so any one can help me with suggestion or a reference wirh full exemple that explain my case

    Great Thanks

  19. slimBa said, on July 30, 2013 at 5:26 pm

    yes sam i use it but how can’i make the relation between the too table and with just one submit on the sale Form i can isert on sale_line ???
    is there any function on the controller that make this ??

    • samsonasik said, on July 30, 2013 at 5:34 pm

      I propose to create trigger on db for something like this rather than code on app.

  20. slimBa said, on July 30, 2013 at 5:44 pm

    Yes i thike about that , just i ant to be sure that there’s any good solution that i must learn it with zf2 , i m a begenner and i m curious to know tha advantage to use zf2

  21. webcodedesenvolvimento said, on August 1, 2013 at 6:13 am

    When using $ this-> tableGateway-> getAdapter (), appears the following error
    Call to a member function canCallMagicGet() on a non-object

    • samsonasik said, on August 1, 2013 at 6:17 pm

      what approach do you using ? if you use ZF2 docs approach, it should be work.

  22. ahmednet said, on August 1, 2013 at 8:11 am

    hi im asking how to show result value !

  23. Oubaydi said, on August 5, 2013 at 2:19 pm

    Hi sam , thx for the blog

    i have 2 Table

    **Table sale :**

    id_sale int
    id_projet int
    price float
    date date

    **Table sale_ligne:**

    id_sale_ligne int
    id_sale int FK_SALE
    id_projet int
    price float
    date date

    i was able to insert query on table line_sale at the same time when i insert on Sale table with

    $this->dbAdapter->query(‘INSERT INTO sale (price, date) VALUES (?, ?)’, array(‘price’, ‘date’));
    $salesId = $this->dbAdapter->getDriver()->getLastGeneratedValue();
    $this->dbAdapter->query(‘INSERT INTO sale_ligne (price, date, id_sale) VALUES (?,?,?)’,array(‘price’, ‘date’, $salesId));

    , i want to (delete , update ) the line_sale row with relation with sale table with one click , i mean when i delete the sale recorde with (id=2 “exemple”) the line_sale with (id_sale=2) will be deleted automaticly , same thinck for update when i update (price , date , id_sale ) for record with (id=2 “exemple”) they will be updated automaticly on line_sale with (id_sale=2)
    i m using adapter for sql request and i use this function to get the Line_sale’s for a specific Sale

    public function getLigneVenteByVente($id)
    {
    $result = $this->select(function (Select $select) use ($id){
    $select->where(array(‘id_sale’=>$id));
    $select->join(‘sale’, ‘ ligne_sale.id_sale=sale.id ‘);
    });
    return $result;

    }

    Thx

  24. webcodedesenvolvimento said, on August 6, 2013 at 1:55 am

    how to insert into two tables using a form?

    • samsonasik said, on August 6, 2013 at 1:31 pm

      I’m not fan of inserting/deleting row on another table using app side, use trigger!

      • slimBa said, on August 6, 2013 at 3:00 pm

        thx sam but i m really a begener , can you help me with an exemple of trigger just i can apply it on my application , i m using Mysql
        Great thx

      • webcodedesenvolvimento said, on August 6, 2013 at 9:19 pm

        Thank you,
        But I need to write the information in the form of different database tables
        there are examples using fieldset, but can not use

  25. Lerry said, on November 6, 2013 at 5:40 am

    Hi I would ask you if you can tell me how I can write a sql UNION with tablegateway. I have a very simple query but I don’t know how to execute with \Zend\Db\Sql\Select
    (SELECT from AS luogo FROM rates WHERE group=’$group’ AND type=’$tipo’)
    UNION
    (SELECT to AS luogo FROM rates WHERE group=’$group’ AND type=’$tipo’)
    ORDER BY luogo ASC
    thx for your help

    • samsonasik said, on November 6, 2013 at 10:45 am

      use combine function, read the code at \Zend\Db\Sql\Select.php at combine function.

      • Lerry said, on November 10, 2013 at 4:34 am

        Great thx for your help !!! but I found two problems. First the ORDER BY doesn’t work with a combine because is automatically added on the first select, I found the solution in one post but could be useful also for your blog that is the best for zf2. I’ve changed the Zend\Db\Sql\Select::$specifications order:
        from:
        protected $specifications = array(
        ‘statementStart’ => ‘%1$s’,
        self::SELECT => array(…),
        self::JOINS => array(…),
        self::WHERE => ‘WHERE %1$s’,
        self::GROUP => array(…),
        self::HAVING => ‘HAVING %1$s’,
        self::ORDER => array(…),
        self::LIMIT => ‘LIMIT %1$s’,
        self::OFFSET => ‘OFFSET %1$s’,
        ‘statementEnd’ => ‘%1$s’,
        self::COMBINE => ‘%1$s ( %2$s )’,
        );
        to:
        protected $specifications = array(
        ‘statementStart’ => ‘%1$s’,
        self::SELECT => array(…),
        self::JOINS => array(…),
        self::WHERE => ‘WHERE %1$s’,
        self::GROUP => array(…),
        self::HAVING => ‘HAVING %1$s’,
        ‘statementEnd’ => ‘%1$s’,
        self::COMBINE => ‘%1$s ( %2$s )’,
        self::ORDER => array(…),
        self::LIMIT => ‘LIMIT %1$s’,
        self::OFFSET => ‘OFFSET %1$s’,
        );
        The second is the select columns(array(‘foo’ => ‘bar’, ‘baz’ => ‘bax’)); I can’t find a way to show the recordSet->foo because is not defined how can I use it.
        Thank you again

      • samsonasik said, on November 11, 2013 at 11:21 pm

        I think you should submit an issue here https://github.com/zendframework/zf2/issues it would be great if you provide a patch 🙂

  26. sunil.hk said, on November 7, 2013 at 5:08 pm

    how can i use the database function in zendfremework2

  27. tejas said, on March 6, 2014 at 7:06 pm

    i want to insert data in more then one table from one form..for that i make differnt model but i can’t get success.. what i have to do?

  28. Sabine said, on March 26, 2014 at 11:54 pm

    Dear Samsonasik, thanks a lot for the helpful example! I am new to Zf2 and your blog is a great help for me to not give up.
    Right now I am trying to programm an example where I have a function which shall give back which of my uploads I shared with some friends and with who I shared them. I need to do two joins in the function, but the second one does not work. Do you have any idea on what I am doing wrong?

    public function getSharedUploadsForUserId($userId)
    {
    $userId = (int) $userId;
    $rowset = $this->tableGateway->select(function (Select $select) use ($userId){
    $select->where(array(‘user_id’ => $userId));
    $select->join(‘upload_sharing’, ‘upload.id = upload_sharing.upload_id’)
    ->join(‘user’, ‘upload_sharing.shared_user_id = user.id’);
    });
    Debug::dump( $rowset);
    return $rowset;

    }
    The error I get is that the property $name which is in the table ‘user’ is not a property of the result I get back.

    • Sabine said, on April 1, 2014 at 7:53 pm

      Ok, the issue is resolved. It was not because of the ‘joins’ that I had problems, but because I was trying to connect the tables the wrong way round..

  29. Najjar Samar said, on June 28, 2014 at 8:38 am

    hello,
    thks for this tuto, it was very helpful, but i have this problem with it “Zend\View\HelperPluginManager::get was unable to fetch or create an instance for select” what can i do for this?

    • samsonasik said, on July 1, 2014 at 11:57 pm

      you call a view helper that not registered at HelperPluginmanager, see if there is typo in there.

  30. Matteo said, on July 11, 2014 at 8:22 pm

    Hi,
    i’ve an error with this code:
    $select = new \Zend\Db\Sql\Select;
    $select->from(array(‘c’=>’cliente’));
    $select->columns(array(Select::SQL_STAR));
    $select->join(array(‘p’=>’privato’), “p.idCliente = c.idCliente”, Select::SQL_STAR, Select::JOIN_LEFT);
    $select->join(array(‘a’=>’azienda’), “a.idCliente = c.idCliente”, Select::SQL_STAR, Select::JOIN_LEFT);

    Statement could not be executed (42S21 – 1060 – Duplicate column name ‘idCliente’)

    How can I fix this query? I’ve a lot of field with same name between cliente, privato and azienda, and I don’t want to rename them, because it is useful to be joined together in the view

    Thank you in advance

    • Matteo said, on July 11, 2014 at 9:25 pm

      I’ve also this code for the pagination:
      $paginatorAdapter = new DbSelect (
      // our configured select object
      $select,
      // the adapter to run it against
      $this->tableGateway->getAdapter (),
      // the result set to hydrate
      $resultSetPrototype );

      I think that the error was here

    • samsonasik said, on July 12, 2014 at 4:51 am

      add tablename or table alias as prefix, for example : “a.productname”, “p.productcategory”, “c.idCliente” , etc

      • Matteo said, on July 14, 2014 at 2:15 pm

        sorry, but I’m not sure I understand the answer.
        In the code I posted I have already used the alias. Did you mean this?
        If I change this:

        $select->join ( ‘privato’, “cliente.idCliente = privato.idCliente”, array (‘privato.idCliente’), ‘left’ );

        I get this error:

        Statement could not be executed (42S22 – 1054 – Unknown column ‘privato.privato.idCliente’ in ‘field list’)

      • samsonasik said, on July 14, 2014 at 9:35 pm

        you can define something like this

        use Zend\Db\Sql;
        //////
        
        $select->columns(array(
            new Sql\Expression('client.id as clientId'),
            new Sql\Expression('product.id  as product_Id'),
            new Sql\Expression('user_group_id  as group_id'),
        ));
        
  31. Matteo said, on July 14, 2014 at 10:59 pm

    is not a good solution for me. As I said in the first message, I have several fields with the same name in different tables, and it is desirable that then united in the view

  32. Michael said, on July 15, 2014 at 3:35 am

    Thanks for the great Tutorial! Maybe you also have an idea for my problem. I want to join a table and include a WHERE in the ON part of the join, so that only datasets with a defined “type” are joined.

    In good old SQL this would look like that:

    SELECT id AS id
    FROM cms_article
    LEFT JOIN search_time
    ON id = search_time.article_id AND cms_article.type=”necessarytype”
    WHERE bla bla…

    With tablegateway I´m unable to get this query done, cause it looks like the “AND” part is always masked which leads to an exception.

    $select->join(
    ‘search_time’,
    ‘id = search_time.article_id AND cms_article.type=”necessarytype”‘,
    array(
    ‘id’,
    ),
    $select::JOIN_LEFT);

    Has anybody an idea, how I can achieve this?

    Thanks in advance & best,
    Michael

    • samsonasik said, on September 8, 2014 at 7:21 am

      you can do :

      $select->join(
          "search_time",
          new \Zend\Db\Sql\Expression("id = search_time.article_id AND cms_article.type='necessarytype' "),
          array(
          "id",
          ),
          $select::JOIN_LEFT
      );
      
  33. piyush said, on September 5, 2014 at 2:51 pm

    please let me know i have face this type of error….
    Argument 1 passed to Sparepart\Model\SparepartTable::__construct() must be an instance of Zend\Db\Adapter\Adapter, instance of Zend\Db\TableGateway\TableGateway given, called in C:\wamp\www\amityadav\module\Sparepart\Module.php on line 35 and defined in C:\wamp\www\amityadav\module\Sparepart\src\Sparepart\Model\SparepartTable.php on line 12

    • samsonasik said, on September 8, 2014 at 7:22 am

      read the error properly, you pass tablegateway intance which actually the class require to pass zend\db\adapter\adapter service to __construct.

  34. Zarif said, on May 14, 2015 at 6:47 pm

    your examples are very effective and helpful……Thanks

  35. black Hack said, on January 21, 2016 at 4:54 am

    hi i read your post i try a little query with zf2 but don’t work maybe you can help me please
    this is my query
    $where = new Where();
    $where->like(‘album.title’, ‘%’ . $title . ‘%’)
    ->like(‘album.artist’, ‘%’ . $artist . ‘%’);
    $select = new Select();
    $select->from(‘album’)
    ->columns(array (‘id’, ‘title’,’artist’))
    ->join(‘pais’, ‘pais.id_album = album.id’, [‘nombre_pais’])
    ->join(‘biografia’, ‘biografia.id_album = album.id’, [‘genero’, ‘ocupacion’, ‘sitio_web’ ])
    ->where($where);

    only return (id, artist,title) but don’t the other columns how i can return the other columns thanks for you help

    • samsonasik said, on January 21, 2016 at 12:48 pm

      I believe you’re the one that asked same question in fb, answered in fb.

  36. black Hack said, on January 21, 2016 at 11:54 pm

    thanlks for all i get run my code and get the query with this thanks
    $selectString = $sql->getSqlStringForSqlObject($select);


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: