如何在zend db中实现sql查询

Question

I tried myself for another queries but this one is more complex for me as i am new to zend. Please help me i tried different ways but not worked.

Tour Id fetching from another query

$tourId = $row2 ['test_public_id'];

$query = select count(ms.test_public_id) as total_views, ms1.recent_views from test_stats 
ms join (select count(test_stats.test_public_id) as recent_views 
from test_stats  where test_stats.test_public_id = '$tourId' 
and test_stats.updated_on > DATE_SUB(CURDATE(), INTERVAL 7 DAY)) ms1 
where ms.test_public_id ='$tourId'" ;

Answer 1

Something like that should work:

$subselect = $dbAdapther->select()->from(
  array('test_stats' => 'test_stats'),
  array(
    '(COUNT(test_public_id)) AS recent_views'
  )
)->where(
  $dbAdapther->quoteInto('test_stats.test_public_id = ?', $tourId)
)->where(
  'test_stats.updated_on > DATE_SUB(CURDATE(), INTERVAL 7 DAY)'
);

$select = $dbAdapther->select()->from(
  array('ms' => 'test_stats'),
  array(
    '(COUNT(ms.test_public_id)) AS total_views' // COUNT should be in brackets to preevent Zend from interpreting it as a field name
  )
)->join(
  array('ms1' => $subselect),
  '',
  array(
    'ms1.recent_views'
  )
)->where(
  $dbAdapther->quoteInto('ms.test_public_id = ?', $tourId)'
);

Although I'd have your query broken into two separate ones or, more precisely, write a universal "get number of views" query with a date as its parameter, and then I'd be calling it twice, with or without the date.

But if you still need to get those two figures in one go in a single row (i.e. you can't use UNION instead of your unnecessary JOIN), I'd recommend you to use the following code instead:

$select = $dbAdapther->select()->from(
  array('ms' => 'test_stats'),
  array(
    '(COUNT(ms.test_public_id)) AS total_views',
    '(
      COUNT(
        CASE
          WHEN ms.updated_on > DATE_SUB(CURDATE(), INTERVAL 7 DAY)) THEN ms.test_public_id
          ELSE NULL
        END
      )
    ) AS recent_views'
  )
)->where(
  $dbAdapther->quoteInto('ms.test_public_id = ?', $tourId)
);