[PART 24] Creating a Twitter clone with GraphQL, Typescript, and React ( rethinking the timeline )

Hi everyone ;).

As a reminder, I'm doing this Tweeter challenge

Github repository ( Backend )

Github repository ( Frontend )

Db diagram

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