Knex joining multiple tables with self joins, inner joins etc

Question

I've been struggling with this join tangle for a day now and I believe it's wiser to ask a pro's help :) Thank you for being patient with me.

I have 5 tables that I need to join, left join, inner join, self join and who knows what. I'm using Knex and Postgres.

Recipes

id SERIAL PRIMARY KEY
user_id integer NOT NULL REFERENCES users(id)
title text
description text 
etc...

Users

id SERIAL PRIMARY KEY
username character varying(100) NOT NULL UNIQUE
etc...

Reviews - holds info about which user has reviewed a recipe, who's the review and recipe author

id SERIAL PRIMARY KEY
user_id integer NOT NULL REFERENCES users(id)
author_id integer NOT NULL REFERENCES users(id)
recipe_id integer REFERENCES recipes(id)
etc...

Followings - holds info about which user follows whom

id SERIAL PRIMARY KEY
user_id integer NOT NULL REFERENCES users(id)
chef_id integer NOT NULL REFERENCES users(id)

Review_votes - holds info about who has up or down voted on a recipe review

id SERIAL PRIMARY KEY
user_id integer NOT NULL REFERENCES users(id)
author_id integer NOT NULL REFERENCES users(id)
review_id integer NOT NULL REFERENCES reviews(id)
recipe_id integer NOT NULL REFERENCES recipes(id)
vote integer CHECK (vote = ANY (ARRAY[1, 0, - 1]))

What I want is to fetch all review related data and reviewer related data for one recipe_id

1. Review data from reviews
2. Reviewer data from users
3. Reviewer created reviews list as array from reviews
4. Reviewer created recipes list as array from recipes
5. Reviewer followers list as array from followings
6. Review vote from review_votes as sum

I want everything and right now from one query, typical woman... Doesn't seem like a big hassle, but somehow I seem to fail with it.

What I have tried so far :

const userRef = database.ref('reviews.user_id');
const reviewRef = database.ref('reviews.id');
const reviewed = database('reviews').where('reviews.user_id', userRef).count('id as reviewed').as('reviewed');
const liked = database('review_votes').where('review_votes.review_id', reviewRef).sum('vote').as('liked')

const reviews = await database('reviews')
  .rightJoin('users', 'users.id', 'reviews.user_id')
  .leftJoin('followings', 'followings.chef', userRef)
  .leftJoin('recipes', 'recipes.user_id', userRef)
  .select({
    username: 'users.username',
    review_pic: 'reviews.review_pic',
    user_pic: 'users.user_pic',
    id: 'reviews.id',
    user_id: 'reviews.user_id',
    author_id: 'reviews.author_id',
    text: 'reviews.text',
    recipe_id: 'reviews.recipe_id',
  },
    reviewed,
    liked
  )
  .count({ followers_count: 'followings.id' })
  .count({ created_recipes: 'recipes.id' })
  .where('reviews.recipe_id', +recipe_id)
  .groupBy(
    'followings.chef',
    'recipes.user_id',
    'reviews.id',
    'users.id',
  );

It almost works, except the reviewed subquery is giving me all reviews count, not the count of a specific reviewer.

Any ideas what I should try to fix this? Rearrange tables? Use array_agg? Stop programming and start growing sheep or something? Thank you! :)

Answer 1

After some contemplation and with some help from a coworker we split the query to more subqueries and fixed the typo in reviewed subquery .where(). The correct query is this if anyone cares :)

const userRef = database.ref('reviews.user_id');
const reviewRef = database.ref('reviews.id');

const reviewed = database('reviews AS reviewed').where('reviewed.user_id', userRef).count('id as reviewed').as('reviewed');
const liked = database('review_votes').where('review_votes.review_id', reviewRef).sum('vote').as('liked');
const followers_count = database('followings').where('followings.chef', userRef).count('*').as('followers_count');
const created_recipes = database('recipes').where('recipes.user_id', userRef).count('*').as('created_recipes');

const reviews = await database('reviews')
  .rightJoin('users', 'users.id', 'reviews.user_id')
  .select({
    username: 'users.username',
    review_pic: 'reviews.review_pic',
    user_pic: 'users.user_pic',
    id: 'reviews.id',
    user_id: 'reviews.user_id',
    author_id: 'reviews.author_id',
    text: 'reviews.text',
    recipe_id: 'reviews.recipe_id',
  },
    reviewed,
    liked,
    followers_count,
    created_recipes,
  )
  .where('reviews.recipe_id', +recipe_id)
  .groupBy(
    'reviews.id',
    'users.id',
  );