write custom query with zend framework 2

Question

I want to perform a custom query in zf2. Now I have a Album controller and AlbumTable. Inside AlbumTable, I want to perform an join operation. But I am unable to do this.Please give me some suggation.

below my code:

namespace WebApp\Table;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Sql;

new Zend\Db\Adapter\Adapter;

class UserTable
{

   protected $tableGateway;

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

   public function searchUser($search)
   {

    $search    = "mehedi";
    $adapter   = new Adapter();
    $sql       = new Sql($adapter);
    $select    = $sql->select();
    $select->from('foo');
    $select->join('profiles', 'user.user_id = profiles.ownerId', array('name'));
    $select->where(array('id' => 2));
    $statement = $sql->prepareStatementForSqlObject($select);
    $results   = $statement->execute();
    return $results;
   }

}

Andrew · Accepted Answer · 2013-03-11 09:05:49Z

1

The issue is you are trying to instantiate your Adapter with no parameters, when it requires at least a driver :

 $adapter   = new Adapter(); // Bad
 $adapter   = new Adapter($driver); // ..

You should use the ServiceManager to get your Adapter, did you start with the Skeleton Application?

It should have already been injected into the TableGateway for you..

$adapter = $this->getAdapter();

An example of instantiating an Adapter:

$config = $serviceLocator->get('Config');
$adapter = new Adapter($config['db']);

where you specify your setup inside your config, local.php will do:

return array(
    /**
     * Database Config
     */
    'db' => array(
        'driver'    => 'pdo',
        'dsn'       => 'mysql:dbname=dbname;host=localhost',
        'username'  => 'root',
        'password'  => 'password',
    ),

edited Mar 11, 2013 at 9:05

answered Mar 6, 2013 at 9:51

Andrew

12.8k1 gold badge37 silver badges48 bronze badges

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

2 Comments

nopaws Over a year ago

in line $adapter = new Adapter($driver); how can i get $driver. Please can you give a brief.

nopaws Over a year ago

Thans Andrew for your nice ans.

nopaws · Accepted Answer · 2013-03-11 10:03:40Z

1

I got some solution of this problem with helping all of here.

    $adapter = $this->tableGateway->getAdapter();

   /* $adapter variable is used to fetch Adapter 
   configuration from serivce manager. */

    $sql       = new Sql($adapter);
    $select    = $sql->select();
    $select->from('foo');
    $select->join('profile', 'foo.skillId = profile.id', array('name'));

    $statement = $sql->prepareStatementForSqlObject($select);
    $results   = $statement->execute();

   /* if you want you can see your
   desired output in here. */

    foreach ($results as $person) {
        echo "<pre>";
        print_r($person);
    }

    return $results;

answered Mar 11, 2013 at 10:03

nopaws

2451 gold badge3 silver badges9 bronze badges

Comments

deadtoy · Accepted Answer · 2013-03-05 11:17:50Z

0

It's because the join method expects an array for the join table. Also I personally would prefix the tables in the query, something like this:-

 $search    = "mehedi";
 $adapter   = new Adapter();
 $sql       = new Sql($adapter);
 $select    = $sql->select();
 // PREFIXED THE foo table f
 $select->from(array('f' =>'foo'));
 // PREFIXED THE profiles table p 
 $select->join(array('p' => 'profiles'), 'user.user_id = profiles.ownerId', array('name'));
 $select->where(array('id' => 2));
 $statement = $sql->prepareStatementForSqlObject($select);
 $results   = $statement->execute();
 return $results;

answered Mar 5, 2013 at 11:17

deadtoy

434 bronze badges

1 Comment

nopaws Over a year ago

Actually this issue is not completely related with Alias. You could do the same operation without alias. The main issue was unable to create an instance of Adapter. As all entity(table entity) must use tablegateway so $adapter = $this->tableGateway->getAdapter(); is the best approach to initialize an adapter instance.

Collectives™ on Stack Overflow

write custom query with zend framework 2

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related