33246

ZF2 TableGateway join

Question:

I am in the process of trying to learn OO/Zend Framework over standard PHP.. I want to scream and write a mysql query instead of using the TableGateway method.

I have been following tutorials and have successfully printed out a table and some fields, although with the way I have gone about doing this, I am totally lost in how I should make this a join with another table and print out some fields there.

For example.

Table Fields customer Idx, Company contact Idx, First_Name

This is my customersController where I assume the work is carried out

namespace Customers\Controller; use Zend\Mvc\Controller\AbstractActionController; use Zend\View\Model\ViewModel; use Zend\DB\TableGateway\TableGateway; class CustomersController extends AbstractActionController { protected $customersTable; public function indexAction() { return new ViewModel(array('customer' => $this->getCustomersTable()->select())); //return new ViewModel(array('customers' => $this->fetchJoin()->select())); } public function addAction() { } public function editAction() { } public function deleteAction() { } public function getCustomersTable() { if (!$this->customersTable) { $this->customersTable = new TableGateway ( 'customer', //table name $this->getServiceLocator()->get('Zend\DB\Adapter\Adapter') ); } return $this->customersTable; } }

Am I on the right track here?

Answer1:

If you need to make joins read about Zend\Db\Sql and Zend\Db\Select which you can read about here

<blockquote>

<a href="http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html" rel="nofollow">http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html</a>

</blockquote>

An example would be:

In your model(that extends the TableGateway or the AbstractTableGateway) <br /> in Some function you can have something like(this is from a project) :

$sql = new \Zend\Db\Sql\Sql($this->getAdapter()); $select = $sql->select() ->from('event_related_events') ->columns(array()) ->join('event_invitees', 'event_invitees.event_id = event_related_events.related_event_id') ->where(array('event_related_events.event_id' => $eventId)); $selectString = $sql->getSqlStringForSqlObject($select); $results = $this->getAdapter()->query($selectString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

Then you can loop over the results and do what you need to.

Taking a look at more powerful ORM like Doctrine or Propel may also help, but may be an overkill for a small/hobby project.

<hr />

EDIT: Answer for what was asked in comments

For Using expression(if, case etc) directly you can use something like :

$sql->select() ->from('table') ->columns(array( 'sorter' => new Expression('(IF ( table.`something` >= 'otherthing', 1, 0))'), 'some_count' => new Expression('(count(*))'), ) )

Explaining the last line in SQL terms, it would be: count(*) AS some_count

Answer2:

So this is my controller, basically from the Album example but now it will display customers from the customer table.

<?php namespace Customers\Controller; use Zend\Mvc\Controller\AbstractActionController; use Zend\View\Model\ViewModel; use Customers\Model\Customers; use Customers\Form\CustomersForm; class CustomersController extends AbstractActionController { protected $customersTable; public function indexAction() { return new ViewModel(array( 'customer' => $this->getCustomersTable()->fetchAll(), )); } public function addAction() { } public function editAction() { } public function deleteAction() { } public function getCustomersTable() { if (!$this->customersTable) { $sm = $this->getServiceLocator(); $this->customersTable = $sm->get('Customers\Model\CustomersTable'); } return $this->customersTable; } } ?>

The indexAction calls the getCustomersTable method which goes to the model (CustomersTable) and executes the "query" there.

<?php namespace Customers\Model; use Zend\Db\TableGateway\TableGateway; class CustomersTable { protected $tableGateway; public function __construct(TableGateway $tableGateway) { $this->tableGateway = $tableGateway; } public function fetchAll() { $resultSet = $this->tableGateway->select(); return $resultSet; } public function getCustomers($id) { } public function saveCustomers(customers $customers) { } public function deleteCustomers($id) { } } ?>

So from your example, I should be trying to implement this into the fetchAll in the model? Thanks for the help.

$sql = new \Zend\Db\Sql\Sql($this->getAdapter()); $select = $sql->select() ->from('customer') ->columns(array()) ->join('contact', 'contact.Idx = customer.Idx') ->where(array('contact.Idx' => $eventId)); $selectString = $sql->getSqlStringForSqlObject($select); $results = $this->getAdapter()->query($selectString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

Recommend

  • How to get the phone number(callerID) in asterisk
  • Asterisk : originate call doesn't set the CALLERID in the dialplan
  • Redirecting to 404 route in PhalconPHP results in Blank Page
  • Symfony2 custom form field
  • Catch-all route in Symfony 3
  • Neo4j: Filter nodes based on aggregate function
  • Command line installation of Code Signing certificates, .p12 files, and mobileprovisions
  • Magento get URL before current
  • Autofac with Web API 2 - Parameter-less constructor error
  • Laravel: Getting Session ID oddly truncates when using foreach
  • Bypass multiple inheritance in Java
  • Recording logins for password protected directories
  • how to do an event when i swipe from fragment to the other
  • Eraser for UIBezierPath
  • How to limit post in wp_query
  • Upload files with Ajax and Jquery
  • Build own AppleScript numerical error handling
  • Cannot Parse HTML Data Using Android / JSOUP
  • AngularJs get employee from factory
  • Benchmarking RAM performance - UWP and C#
  • JTable with a ScrollPane misbehaving
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • Change div Background jquery
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Bitwise OR returns boolean when one of operands is nil
  • unknown Exception android
  • Is there any way to bind data to data.frame by some index?
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • failed to connect to specific WiFi in android programmatically
  • Net Present Value in Excel for Grouped Recurring CF
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • How can I use threading to 'tick' a timer to be accessed by other threads?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?