Mysql – Only LIMIT rows with specific number

MySQL

Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title.

So I have this query to fetch all the comments of a specific topic.
Now for my paging I want to limit it down to 20 rows, but ONLY for posts with pid (parent_id) zero.

SELECT *, c.id AS cid, u.id AS userid 
FROM comments c 
INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id ORDER BY c.posted ASC LIMIT 20

How do I go about limiting the posts by 20 WHERE pid = 0 without removing every row with pid = 1?

If I add:

 WHERE c.tid = :id AND c.pid = 0

I'll just get all comments without a pid.

So, for example, it shows 100 comments, but only 20 that has pid = 0 in the database. That's what I want.

Best Answer

Maybe you could try with:

WHERE c.tid = :id AND c.pid IN(0,1)

WHERE c.tid = :id AND c.pid <=1

but I am not sure what you want to get. Maybe an UNION is more appropriate.

-- only 20 with c.pid = 0
(SELECT *, c.id AS cid, u.id AS userid 
FROM comments c INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id AND c.pid = 0 
LIMIT 20)
UNION
(SELECT *, c.id AS cid, u.id AS userid 
FROM comments c INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id AND c.pid = 1)
ORDER BY c.posted ASC

Related Solutions

Mysql – Rows with a variable number of columns

Edited after a couple of comments by the question asker!:

Better idea for your design: new sample schema

(If you're not familar with these diagrams, that's seven tables, with foreign key links as pictured [the three _Link tables all have two foreign keys referencing the data tables]. It probably looks like I'm overcomplicating things, but trust me, "tokenise once and store in the database" is so much more efficient than "tokenise data each time you retrieve it".)

Sample data, using a simple tweet (ignoring Users for now, it's the same concept though): "test tweet! #howdoesthiswork #newbie"

      Tweet
TweetID   WordCount
   1         2

Tweet_Word_Link
TweetID   WordID
   1         1
   1         2

      Word
WordID    Word
   1      test
   2      tweet

Tweet_Hashtag_Link
TweetID  HashtagID
   1         1
   1         2

      Hashtag
HashtagID  Hashtag
    1      #howdoesthiswork
    2      #newbie

This allows you to easily get all the users linked in a tweet:

SELECT U.UserName
FROM  User U
        INNER JOIN
      Tweet_User_Link TUL ON U.UserID = TUL.UserID
        INNER JOIN
      Tweet T ON TUL.TweetID = T.TweetID
WHERE T.TweetID = <your ID here>

(change table names as appropriate for Hashtags and Words)

Or, slightly more complicated, get the top 10 words for all tweets mentioning a specific hashtag:

SELECT TOP 10 W.Word, COUNT(*) AS WordCount
FROM  Word W
        INNER JOIN
      Tweet_Word_Link TWL ON TWL.WordID = W.WordID
        INNER JOIN
      Tweet T ON TWL.TweetID = T.TweetID
        INNER JOIN
      Tweet_Hashtag_Link THL ON THL.TweetID = T.TweetID
        INNER JOIN
      Hashtag H ON H.HashtagID = THL.HashtagID
WHERE H.Hashtag = <your hashtag here>
GROUP By W.Word

(I've used ANSI SQL syntax, I'm not 100% sure if MySQL supports all of ANSI SQL - but it should be close enough.)

Mysql – How to COUNT number of rows with LIMIT

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Best Answer

Related Solutions

Mysql – Rows with a variable number of columns

Mysql – How to COUNT number of rows with LIMIT

Related Question