Expanding mySQL query to include more content

Question

I have a MySQL query that selects content created by friends of the current user and lists that content in reverse chronological order.

    SELECT node.nid AS nid,
   node.created AS node_created
 FROM node node 
 LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
 LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
 WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
   ORDER BY node_created DESC

How do I "expand" this query so it lists comments in addition to nodes?

here's the structure of the comment table:

• cid (comment ID)
• uid (ID of user who posted comment)
• timestamp (uses same UNIX date format as the "node created" column from the node table up above)

So right now the query grabs all the "nodes" written by people who are buddies with the current user. I want it to also grab all the comments written by people who are buddies with the current user. Then I want to list these nodes and comments in DESC order based on the date they were created.

It appears I need to add more stuff to my select statement, but I'm not sure what.

Answer 1

You can use a UNION ALL to combine the results from the two queries:

SELECT nodeid, commentid, created FROM (
    SELECT node.nid AS nodeid, NULL as commentid, node.created AS created
    FROM node node 
    LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
    LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
    WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
    UNION ALL
    SELECT NULL as nodeid, comment.cid AS commentid, comment.timestamp AS created
    FROM node node 
    LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
    LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
    WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
)
ORDER BY created DESC

Answer 2

So right now the query grabs all the "nodes" written by people who are buddies with the current user. I want it to also grab all the comments written by people who are buddies with the current user. Then I want to list these nodes and comments in DESC order based on the date they were created.

Use:

   SELECT n.nid,
          COALESCE(c.timestamp, n.created) AS coalesced_timestamp,
          c.comment
     FROM NODE n
LEFT JOIN USER_RELATIONSHIPS ur ON ur.requestee_id = n.uid
LEFT JOIN USERS u ON u.uid = ur.requestee_id
LEFT JOIN COMMENT c ON c.uid = u.uid
                   AND c.nid = n.nid
    WHERE n.status <> 0 
      AND n.type in ('drigg', 'blog_post') 
      AND ur.requester_id = 1
 ORDER BY coalesced_timestamp DESC

That's based on the ERD listed here.

It doesn't make much sense to have table aliases that are the same, or longer than the table name itself.