lundi 27 juin 2016

MySQL Query for Posts and Comments


I am building a social network wall feature and need help with a query to fetch all feeds and feed replies for a specific user. My database structure is as follows:

feeds (id, user_id, poster_id, comment, created_at, updated_at)
feed_replies (id, feed_id, user_id, comment, created_at, updated_at)

I want to fetch all the feeds and for each feed all of the comments for that specific feed along with joining to the users table to get the user first name and last name. I tried joining and union queries but nothing has given me what I want unless I'm just not doing it right. Here is what I have as of now.

SELECT 
    'feed' AS type, 
    f.id AS feed_id, 
    f.comment, 
    f.poster_id AS poster_id, 
    f.updated_at AS updated_at, 
    CONCAT(u.first_name, ' ', u.last_name) AS poster_name, 
    u.thumb AS poster_thumb 
FROM feeds f 
    INNER JOIN users u ON f.poster_id = u.id 
WHERE f.user_id = $user_id 
ORDER BY updated_at DESC 
LIMIT $offset, $limit

I stripped out what I had tried and left in what works right now. Any help is greatly appreciated.

UPDATE: I fixed the table fields to match what I have now. The current query returns the feeds with the user that posted them. I want to be able to also retrieve all feed_replies for each feed so that I can display them on a social media wall. I started working on a union query but that didn't work so I'm not sure where to go from here. Using joins I was able to fetch the replies but it also returned the feeds over and over again.


Aucun commentaire:

Enregistrer un commentaire