UPDATE: A friend (Hi Tommy) wise pointed out that the UNION and double selects could be handled by adding an OR clause. So… that’s what I did.

In the old Kin the timeline, ie. the basic view that included all the posts viewable by a given character was built in a less than great way.

Because we allowed for heavy segmentation; ie. there were essentially three kinds of timelines one could post on (Users, Pages and Groups) and there were several types of posts (Default, Relationships, etc) we needed to develop a system where a post was decidedly distinct from the timeline on which it occurred.

We’d call that with an timelineType IN (‘user’,’group’) type definition. That returned the ID’s of the individual posts we needed to pull. Because I was still getting my head wrapped around PHP’s OOP model at the time I ended up with something that was essentially a mess of looping through the rows of the timeline query, then doing separate calls for each row into the Posts table, and then when rendering those I did separate calls yet again to include Author data (because a Post Author could be both a User and a Page).

For Kin 2, I’ve managed to whittle all that down to a single SQL statement. Which just goes to show how much I’ve learnt since building the first version. We still have all the same data available and we can still page through it with OFFSET and LIMIT.

So here’s the new and improved. 

SELECT 
	posts.id as postID,
	combined_timeline.timelineID, 
	combined_timeline.timelinetype,
	posts.authorID,
	posts.authorType,
    IF(combined_timeline.timelinetype = 'page', pages.name, characters.name) as authorName,
	IF(combined_timeline.timelinetype = 'page', pages.slug, characters.slug) as authorSlug,
	postType,
	posts.contents,
	timestamp
FROM (
	SELECT DISTINCT timelines.contentID, timelines.timelineID, timelines.timelineType FROM timelines WHERE timelineType IN('user','page') 
	UNION ALL 
	SELECT timelines.contentID, timelines.timelineID, timelines.timelineType FROM timelines WHERE timelineType = 'group' AND timelineID IN (7,8,10,11)
) as combined_timeline
LEFT JOIN posts ON posts.id = combined_timeline.contentID 
LEFT JOIN characters ON characters.id = combined_timeline.timelineID AND combined_timeline.timelineType = 'user' 
LEFT JOIN pages ON pages.id = combined_timeline.timelineID AND combined_timeline.timelineType = 'page' 
ORDER BY posts.timestamp DESC;

In the above snippet I’ve intentionally left out the OFFSET and LIMIT functionality. I’ll probably add some hard limits to that in the API.

Much improved version

And here’s the improved version, per Tommy’s comments. 

SELECT 
	posts.id as postID,
	combined_timeline.timelineID, 
	combined_timeline.timelinetype,
	posts.authorID,
	posts.authorType,
	IF(combined_timeline.timelinetype = 'page', pages.name, characters.name) as authorName,
	IF(combined_timeline.timelinetype = 'page', pages.slug, characters.slug) as authorSlug,
	postType,
	posts.contents,
	timestamp
FROM (
	SELECT DISTINCT timelines.contentID, timelines.timelineID, timelines.timelineType FROM timelines WHERE timelineType IN('user','page') OR timelineType = 'group' AND timelineID IN (7,8,10,11)
) as combined_timeline
LEFT JOIN posts ON posts.id = combined_timeline.contentID 
LEFT JOIN characters ON characters.id = combined_timeline.timelineID AND combined_timeline.timelineType IN ('user','group') 
LEFT JOIN pages ON pages.id = combined_timeline.timelineID AND combined_timeline.timelineType = 'page' 
ORDER BY posts.timestamp DESC LIMIT 25