0

I am building a simple social network where I have the following schema( I did not include all the fields in the tables, just the ones that are relevant):

tbl_users:
id
username

tbl_friends:
user_id FK tbl_users.id
friend_id FK tbl_users.id

tbl_questions:
id
user_id
title
date_created

tbl_answers:
id
question_id
body

What I want is to retrieve all the questions belonging to a user's friends with each question having a limit of 3 answers. The result will have a limit to be efficient, lets say the last 5 added questions of my friends. I want a scalable solution if anyone can help with this. I managed to do this having 2 queries: 1st query finds all my friends SELECT id FROM tbl_friends WHERE user_id=:id OR friend_id=:id

After that I used the result for a WHERE IN clause to only get the questions. I could not get the answers, I made a SELECT for each answer. My solution is inefficient and not scalable and I want to find a better solution for my problem.

I also have to ask this...would noSQL be better in this situation?

Mythriel
  • 1,360
  • 5
  • 24
  • 45
  • Very similar problem to http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group/2129703. The most popular answer links to [this very good tutorial](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/). – RandomSeed Mar 20 '13 at 17:27

1 Answers1

0

You can try some like:

SELECT <there JOIN between user, friends and questions>
  LEFT JOIN 
    (SELECT question_id, body 
      FROM tbl_answers 
      WHERE question_id = tbl_questions.id 
      LIMIT 3
    ) AS Answers3
    ON Answers3.question_id = tbl_questions.id
Ilya Gruzinov
  • 31
  • 2