jimmyoneshot Posted October 19, 2011 Share Posted October 19, 2011 I'm creating a sort of forum/wall where users can post examples of their work in either photo or video format but I want a query which will get all of these posts and their attachments however if a video is attached it will only get all of the video details but if a photo is attached it will only get the photo details. Is there any way to do this in the following query or will I simply need to join onto both tables the way I have currently? SELECT posts.id, posts.text, posts.attachment_id, posts.attachment_type, videos.id, videos.name, videos.source, photos.id, photos.name, photos.source FROM posts LEFT JOIN videos ON (posts.attachment_id = videos.id) LEFT JOIN photos ON (posts.attachment_id = photos.id) Is this the only feasible way of doing this? I also have a secondary problem. Users are able to comment on these posts but when the wall is loaded I only want the first 3 comments for each post to be displayed. Is this possible inside the above single query? At the moment my comments table includes only id and comment_text and my join table between comments and posts contains id1 and id2 with id1 being the id of the post the comment is on and id2 being the id of the comment. Any help would be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 19, 2011 Share Posted October 19, 2011 This is a typical anti-pattern. What if you have the same UID in both the video and photo table? A somewhat-better way to to have two columns: video_attachment_uid and photo_attachment_uid. Of course, that's limiting if you ever need 2. You're supposed to have another table for each such attachment -- maybe with a base table if they share many attributes (e.g. title,description,date,etc.) -- so that many-to-one is easy. Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted October 20, 2011 Author Share Posted October 20, 2011 Hi Fenway. Thanks for the help. Aha so maybe I could have an "objects" table containing things like name, description, date etc and then my videos and photos tables could simply contain data specific to each of those e.g. video would have a length whereas photos wouldn't and these tables would use the object_id as their foreign key to connect them to the appropriate item in the objects table? What I'm really struggling with is applying the top N per group stuff to my query i.e. to select all posts as well as the top 3 latest comments per post in the same query. Previously I've done it in an extremely round about way by using many many subqueries and naming the result columns as things like comment1_username, comment1_text, comment2_username, etc etc but it's unefficient and very bulky code wise. Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted October 20, 2011 Author Share Posted October 20, 2011 Also I've redesigned my database several times so I'm really desperate to finally get it right. One aspect of my system is that these items are sorted by album. Which of these would you say is most efficient/best practice? 1. Have a single base objects table with all the common data and a photos table and a videos table with extra data relevant only to those types. And also have a single base album table with all the common album data e.g. album name, date etc and video and photo album tables containing all data relevant to those specific albums OR 2. Have individual tables for everything i.e. no base tables and my tables would basically be - photos, videos, photoAlbums, videoAlbums and in the second case I would have tables such as video_comments, photo_comments as opposed to just an overall comments table for everything with a type id indicating what type of item the comment is about. What type of approach is best as this is something that has caused me much confusion and redevelopment over almost the last year Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2011 Share Posted October 20, 2011 (2) is the preferred way to go. yes, it's easier to have fewer tables, but it makes life more complicated if you never need multiple groups. Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted October 20, 2011 Author Share Posted October 20, 2011 Sorry I'm confused because just earlier in the thread you said that:- You're supposed to have another table for each such attachment -- maybe with a base table if they share many attributes (e.g. title,description,date,etc.) -- so that many-to-one is easy. which would surely be method 1 because in method 2 my videos and photos tables would have no base table and would therefore both contain everything i.e both have name both have description etc aswell as their individual columns. I have 2 database structures planned for this:- Method 1 tables:- OBJECTS id (PK) objectType <Defines if object is a photo, video> albumId (FK) authorId (FK) name description PHOTOS objectID (FK) width height VIDEOS objectID (FK) playTime ALBUMS id (PK) objectType <Defines if album is for photos or videos> authorId (FK) name description Method 2 Tables PHOTOS id (PK) albumId (FK) authorId (FK) name description width height VIDEOS id (PK) albumId (FK) authorId (FK) name description playTime PHOTOALBUMS id (PK) authorId name description VIDEOALBUMS id (PK) authorId name description Which one of the above seems best? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2011 Share Posted October 20, 2011 Method 2 -- no magic types. Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted October 20, 2011 Author Share Posted October 20, 2011 Gotya. Sorry for the constant questions I've just been trying to get this to work for a long time and appreciate the expert advice. The problem with method 2 is when it comes to creating my attachments query as stated at the start of the thread because at the moment user can attach a photo OR a video to a post and right now query looks like this (using method 2):- SELECT posts.id, posts.text, videos.name AS video_name, photos.name AS photo_name FROM posts LEFT JOIN videos ON (posts.attachment_id = videos.id) LEFT JOIN photos ON (posts.attachment_id = photos.id) Obviously the above is a simplified version because lots of data is selected from both tables in the real version but I'm wondering how to improve this because as you've stated this will be fine if a video with the post's attachment id doesn't exist because then video_name etc will all be null but if a photo with id 7 exists and a video with id 7 exists this could cause problems? But if I implemented a photo_attachment_id and a video_attachment_id into my posts table wouldn't I have the same problem anyone? The only way around that I can see is to have some sort of logic checking if a video or a photo is attached within the query but surely this is very hard? My posts table at the moment is like this:- id author_id attachment_id attachment_type text How would you edit this table and the query to account for what I want it to do? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2011 Share Posted October 20, 2011 No, it won't cause problems -- you just need to alias your columns. Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted October 20, 2011 Author Share Posted October 20, 2011 Aha ok then it seems I've been going down the right path all along. Thanks for the help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.