Hi everyone ;).
As a reminder, I'm doing this Tweeter challenge
Github repository ( Frontend )
Let's take a look at the "real" twitter timeline
For the moment, the feed request is quite simple. I retrieve the list of people the user is following and I retrieve their tweets. I thought it would be good but when I analyzed a bit more closely the twitter timeline, I saw that it also displays the retweets and likes of other users (not those of the logged in user apparently). In the same way if a user you follow likes and retweets the same tweet, it will only display the retweet and not both (I don't know if it's consistent but that's what I noticed). So our query is going to be a little more complicated and I've learned a lot about SQL queries looking for a solution ;). I don't know if it's the best way to do it and I also thought about other ideas but as this way made me dig a bit more into SQL queries, I'm keeping it for now ;). If you have other suggestions, don't hesitate ;)
In the same way I've reached a point where I'm starting to understand in more details the differences between a rest API and GRAPHQL.
An example:
In my case, my API will not be public and only my client application will be able to connect to my API. Therefore, to limit the number of SQL queries, I make a select of all counters (likes, retweets, comments, bookmarks) directly in the main query and I don't use the @FieldResolver with the dataloaders. This saves me four queries but it also means that if I don't need those fields, I'll do four "SELECT COUNT()" for nothing. In my application, I would systematically need the stats so I do it like this. But if I had to expose the API, I think I would do it differently ;). Anyway, let's have a look at the request I came to to get the feed.
Ouch :D
const select = [
'tweets.id',
'tweets.body',
'tweets.user_id',
'tweets.parent_id',
'tweets.visibility',
'tweets.type',
...selectCountsForTweet(db),
]
const tweets = await db
.from(
db
// I do a union from 3 subqueries
.union(
[
// First Query
// I select the tweets from the tweets table
// and it will return the tweets and comments
db
.select([
...select,
'tweets.created_at',
// Need to have the same number of columns for all the 3 queries
db.raw('NULL as like_author'),
db.raw('NULL as retweet_author'),
])
.from('tweets')
// I want the tweets/comments from the followedUsers and
// those from the connected user
.whereIn('tweets.user_id', [...followedUsers, userId]),
// SECOND Query
db
.select([
...select,
'likes.created_at',
// I concat the display_name and username
// I will need that to show "Like by @user" in the client
db.raw(
`concat (users.display_name,',', users.username) as like_author`
),
db.raw('NULL'),
])
.from('tweets')
.innerJoin('likes', 'likes.tweet_id', '=', 'tweets.id')
.innerJoin('users', 'users.id', '=', 'likes.user_id')
// I only want the likes from the followedUsers
.whereIn('tweets.id', function () {
this.select('l.tweet_id')
.from('likes as l')
.whereIn('l.user_id', followedUsers)
})
// And if the user liked and retweeted the tweet, I "ignore" the like
.whereNotIn('tweets.id', function () {
this.select('retweets.tweet_id')
.from('retweets')
.whereIn('retweets.user_id', followedUsers)
})
// I don't want the connected users likes
.andWhere('likes.user_id', '!=', userId),
// Third QUERY
db
.select([
...select,
'retweets.created_at',
db.raw('NULL'),
db.raw(
`concat (users.display_name,',', users.username) as retweet_author`
),
])
.from('tweets')
.innerJoin('retweets', 'retweets.tweet_id', '=', 'tweets.id')
.innerJoin('users', 'users.id', '=', 'retweets.user_id')
.whereIn('tweets.id', function () {
this.select('rt.tweet_id')
.from('retweets as rt')
.whereIn('rt.user_id', followedUsers)
})
.andWhere('retweets.user_id', '!=', userId),
],
// Put parenthesis between the queries (Knex option)
// select * from ((select * from foo) union (select * from bar)) results
true
)
.as('results')
)
// One thing to notice is the order will be by the different created_at Field
// In the first query, I select the tweets.created_at
// In the second query, I select the likes.created_at
// In the third query, I select the retweets.created_at
// I can then have the order by created_at that I want.
.orderBy('created_at', 'desc')
I commented the query for more clarity ( I hope :D ).
I also added timestamps columns for the likes and retweets tables.
I slightly modified the Tweet entity
like_author?: string
retweet_author?: string
@Field(() => LikeRetweetAuthor, { nullable: true })
likeAuthor?: LikeRetweetAuthor
@Field(() => LikeRetweetAuthor, { nullable: true })
retweetAuthor?: LikeRetweetAuthor
src/entity/LikeRetweetAuthor.ts
import { Field, ObjectType } from 'type-graphql'
@ObjectType()
class LikeRetweetAuthor {
@Field()
username: string
@Field()
display_name: string
}
export default LikeRetweetAuthor
src/resolvers/TweetResolver.ts
@FieldResolver(() => LikeRetweetAuthor, { nullable: true })
likeAuthor(@Root() tweet: Tweet) {
if (!tweet.like_author) return null
const splitted = tweet.like_author.split(',')
return {
display_name: splitted[0],
username: splitted[1],
}
}
@FieldResolver(() => LikeRetweetAuthor, { nullable: true })
retweetAuthor(@Root() tweet: Tweet) {
if (!tweet.retweet_author) return null
const splitted = tweet.retweet_author.split(',')
console.log('splitted', splitted)
return {
display_name: splitted[0],
username: splitted[1],
}
}
I think I will have to refactor a bit. I will certainly create a TweetRepository to extract that query and lighten the resolver ;). But we'll see that in a next part. We'll update the front also ;).
Bye and take care ;)!
You learned 2-3 things and want to buy me a coffee ;)?
https://www.buymeacoffee.com/ipscoding
Top comments (0)