1

enter image description here

Hi, I need a zf2 join query that fetches only the latest row(by id DESC) from the second table. I have written an sql query and it works.

SELECT st1.customer_id,
    st1.id
  FROM status st1
  inner JOIN 
  (
    SELECT max(id) MaxId, customer_id
    FROM status
    GROUP BY customer_id
  ) st2
    ON st1.customer_id = st2.customer_id
    AND st1.id = st2.MaxId

But I need this query at zend framework 2 table gateway format. Please help.

akond
  • 15,865
  • 4
  • 35
  • 55
murad
  • 175
  • 2
  • 14

1 Answers1

1
    use Zend\Db\Sql\Select;
    use Zend\Db\Sql\Expression;

    $sql = new Select ();
    $sql->columns(["customer_id", new Expression ("max(id) AS MaxId")])
        ->from ('status')
        ->group('customer_id');

    $outer = new Select ();
    $outer->columns (['customer_id', 'id'])
        ->from (['st1' => 'status'])
        ->join (['st2' => $sql], 
             'st1.customer_id = st2.customer_id AND st1.id = st2.MaxId', []);
akond
  • 15,865
  • 4
  • 35
  • 55
  • Thank you @akond it's working except new Expression ("max(id) AS MaxId")] this one. I think we should use 'MaxId'=>new Expression("max(id)") instead of it. – murad Jan 10 '16 at 09:33