1

I have a simple query which I can't for the life of me figure out.

I have a table (simplified) "messages" with columns "thread_id" and "messages"

I also have a list of threads I need 10 results from each.

$list = "1,2,3,4";

I would do $db->select()->from("messages")->where("thread_id IN ('?')",$list)->limit(10);

but that limits only 10 out of total, how would I do 10 out of each thread_id in ZF 1.11 query method?

Nandakumar V
  • 4,317
  • 4
  • 27
  • 47
Darius
  • 1,613
  • 5
  • 29
  • 58

2 Answers2

3

you can get the desired result using this query

 SELECT * FROM 
    (SELECT i1.*
    FROM threadmessage i1
    LEFT JOIN threadmessage i2
      ON (i1.threadid = i2.threadid 
          AND i1.messageid < i2.messageid 
         )
    GROUP BY i1.messageid
    HAVING COUNT(*) < 5
    ORDER BY i1.messageid) A
WHERE A.threadid IN (3,4);

I have created a SQL fiddle

Also you can execute it in aend using the query method. 

$stmt = $db->query($sql);
$result = $stmt->fetchAll();

This is the classic 'N per group' type query. You can get more details from these posts

How do I select multiple items from each group in a mysql query?
How to SELECT the newest four items per category?
Retrieving the last record in each group

Community
  • 1
  • 1
Nandakumar V
  • 4,317
  • 4
  • 27
  • 47
0

is not a good solution, but can solve your issue, create sub-sql and union.

try{
    $db =  Zend_Db_Table::getDefaultAdapter();
    $sql=array();
    $idAry=explode(',',$list);
    for($i=0;$i<count($idAry){
        array_push($sql," SELECT * FROM ".$this->_name." where thread_id = ".$idAry[$i]." limit 10 ");
    }   
    $stmt = $db->query(implode(' union ',$sql););
    $result = $stmt->fetchAll();
    return $result;
}
catch(Exception $ex){
    print_r($ex);
    exit;
}
sheauren
  • 96
  • 2
  • 6
  • Appreciate the help, I'm trying to stay away from Union since there could be more than 8 records in the $list. – Darius Sep 23 '13 at 03:22
  • if data is not realtime information,your can flag(top 10 or sequence) your table by batchjob, Simplify sql syntax. but thread_id seem not , i have no idea don't use union. sorry can't help you. – sheauren Sep 23 '13 at 03:34
  • Appreciate the effort. It's realtime data :\ Thank you – Darius Sep 23 '13 at 03:37