MyBB Community Forums

Hi

What is the best way to limit for each id in one query?

$query = $db->query("
            SELECT *
            FROM ".TABLE_PREFIX."posts sc 
            INNER JOIN ".TABLE_PREFIX."users u ON (p.uid=u.uid)
            WHERE 1=1 AND p.tid IN ('".$tids."')
            ORDER BY p.dateline ASC");

I'm not exactly sure I understand what it is you're trying to achieve? It may help to show some sample result data that you'd like to retrieve.

Lets say we have five threads, five tids
1 2 3 4 5

I need to get five posts for each thread

Five which posts? First posts of each thread?

Lets say last

Take the max datetime from post table and join back to post table itself.

I am giving you the raw SQL query for what you need:

SELECT p.message FROM mybb_posts p INNER JOIN (SELECT tid, MAX(dateline) as latest from mybb_posts WHERE tid IN (1,2,3,4,5) GROUP by tid) gpost ON p.tid = gpost.tid AND p.dateline = gpost.latest

sorry, but i don't understand your query,i don't see where is limit number, where is inner join

more what i need

What is the best way to limit for each id in one query?
I have five threads, fromthere i get five ids ($tids) there are not the same,
one time there are 1,2,3,4,5 next 2,3,4,5,6, there are changing, i get latest threads
i need latest posts for threads
the $tids in array
i need to get 5 or oder number (limit) posts to each thread

exemple, lests say for exemple threads have that ids 1,2,3,4,5

> row one = have thread id 1 and gets posts 1,2,,3,4,5
> row two = have thread id 1 and gets posts 6,7,8,9,10
> row three = have thread id 1 and gets posts 11,12,13,14,15
> row four = have thread id 1 and gets posts 16,17,18,19,20
> row five = have thread id 1 and gets posts 21,22,23,24,25

 

  $tids = join("','",array_keys($threads));

    $query = $db->query("
            SELECT *
            FROM ".TABLE_PREFIX."posts sc 
            INNER JOIN ".TABLE_PREFIX."users u ON (p.uid=u.uid)
            WHERE 1=1 AND p.tid IN ('".$tids."')
            ORDER BY p.dateline ASC");

i need something like this in onme query

 

  $tids = array_keys($threads);

  foreach($tids as $k => $v)
  {  
    $query = $db->query("
            SELECT *
            FROM ".TABLE_PREFIX."posts sc 
            INNER JOIN ".TABLE_PREFIX."users u ON (p.uid=u.uid)
            WHERE 1=1 AND p.tid = ".$v."
            ORDER BY p.dateline ASC
			LIMIT 5");
	}

If you see above you said you have 5 thread ids (tid) and you need the latest post of each ID.
My Query will return you last post of every thread as per defined tids (5 as you said). Now if you want 5 post from the same thread you can use 'LIMIT' alongwith 'OFFSET'. Same as we use in case of pagination in MyBB...

SELECT pid, message FROM mybb_posts WHERE tid=1 ORDER BY dateline ASC LIMIT 5 OFFSET 10

Will give you 11th to 15th posts from thread id 1.
Is that what you want?

JOIN is required if you want to fetch user details along with.

I am still not sure I get you correctly.

I know that my translation (translation  what I mean) is wrong, sorry

lets say i have 5 or 6 or other numer (limit will be in setting) threads, i have them id like this in query

WHERE 1=1 AND p.tid IN ('".$tids."')

I would like to get 5 or 6 or other numer (limit will be in setting) last posts for ich thread is on query

something like that but in one query

foreach($tids as $k => $v)
  {  
    $query = $db->query("
            SELECT *
            FROM ".TABLE_PREFIX."posts sc 
            INNER JOIN ".TABLE_PREFIX."users u ON (p.uid=u.uid)
            WHERE 1=1 AND p.tid = ".$v."
            ORDER BY p.dateline ASC
			LIMIT 5");
	}

something like

LIMIT 5 PER EACH TID

I create something for everyone, this will by free and public plugin but I am an amateur and not everything i will skip

Look at this code:

$tids = "2,4,7"; // Comma separated target thread ids
$post_count = 5; // Number of posts to fetch from each thread

$query = $db->query("
SELECT p.tid, p.pid, p.uid, u.username, p.message, p.dateline 
FROM ".TABLE_PREFIX."posts p 
INNER JOIN ".TABLE_PREFIX."users u ON (p.uid=u.uid) 
WHERE p.dateline > IFNULL((SELECT dateline FROM ".TABLE_PREFIX."posts AS datecut WHERE datecut.tid = p.tid ORDER BY dateline DESC LIMIT 1 OFFSET ".$post_count."), 0) 
AND p.tid IN (".$tids.") ORDER BY p.tid, p.dateline ASC");

This will give you $post_count number of last posts from every thread id included in $tids.