Best way to structure relational data

Moralis General
#1

I have an application that stores books, and reviews. It is a one-to-many relationship. But i need to get data for the following scenarios.

  • Scenario 1 Given a book, I want to list all the reviews for that book.
  • Scenario 2 Given a query that returns a list of books, I need to get a count of all the reviews associated with each book.
  • Scenario 3 Given a query that returns a list of reviews (eg, all reviews from a user), I need to get the get the book associated with each of those reviews.

Here are are several approaches i have come up with. Approach 4 is the one that seems to fit my needs the most at the moment, but i am not entirely happy with it.

I would appreciate feedback if you think there is a solution to anything i have considered.

Approach 1

Approach 1. The review object has a pointer to the book.

  • PRO: This makes Scenario 3 very easy.
  • PRO: Scenario 1 can also be easily accomplished with a query that filters for all reviews that have a pointer to the given book.
  • CON: maybe inefficient to perform Scenario 2? Would i have to loop through each book and run a separate query that filters for reviews that have a pointer to the book?
// ----------------------------------------------
// ADD REVIEW TO EXISTING BOOK
// APPROACH 1. Review has pointer to book
// ----------------------------------------------
const user = Moralis.User.current();
const Book = Moralis.Object.extend("Book");
const Review = Moralis.Object.extend("Review");

// 1. Get book
const query = new Moralis.Query("Book")
query.equalTo("title", "Frankenstein")
const book = await query.first()

// 2. Create the review with pointer to the book
const review = new Comment();
review.set("content", "Amazing book!");
review.set("user", user);
review.set("book", book);
review.save();

Approach 2

Approach 2. The book object has a list of pointers to the review objects.

  • PRO: This makes Scenario 1, and Scenario 2 very easy.
  • CON: Might be inefficient to do Scenario 3? Would i have to loop through each review and run a separate query that filters for books that have a pointer to the review?

Approach 3

Apporach 3 review has pointer to book, and book has relationship review.

  • PRO: This makes scenario 1, and scenario 3 easy.
  • What about scenario 2? Would it require some subquery to get a count of reviews for each book? or will it require looping?
  • CON: requires two separate queries to save() the relationship on both ends. Might cause issues if connection is lost before second query is executed.
// ----------------------------------------------
// ADD REVIEW TO EXISTING BOOK
// APPROACH 3. Review has pointer to book, and book has relation to review
// ----------------------------------------------
const user = Moralis.User.current();
const Book = Moralis.Object.extend("Book");
const Review = Moralis.Object.extend("Review");

// 1. Get book
const query = new Moralis.Query("Book")
query.equalTo("title", "Frankenstein")
const book = await query.first()

// 2. Create the review with pointer to the book
const review = new Review();
review.set("content", "Amazing book");
review.set("user", user);
review.set("book", book);
review.save();

// 3. Add relation from book to review
const reviewRelation = book.relation("reviewRelation")
reviewRelation.add(review)
book.save()

Approach 4

Apporach 4 review has pointer to book, and book has list of pointers to review objects.

  • PRO: This makes querying for all scenarios very easy.
  • PRO: Only a single query is needed to save the relationships on both ends.
  • CON: For scenario 2, it might consume too much bandwidth, since the query that returns all books also returns all the actual review objects. See my code below. Is there any way to prevent the actual review objects from being returned? And only return eg the object id instead? Or the aggregate count?
// ----------------------------------------------
// ADD REVIEW TO EXISTING BOOK
// APPROACH 4. Review has pointer to book, and book has list of pointers to reviews
// ----------------------------------------------
const user = Moralis.User.current();
const Book = Moralis.Object.extend("Book");
const Review = Moralis.Object.extend("Review");

// 1. Get book
const query = new Moralis.Query("Book")
query.equalTo("title", "Frankenstein")
const book = await query.first()

// 2. Create the review with pointer to the book
const review = new Review();
review.set("content", "Such amazing prose!");
review.set("user", user);
review.set("book", book);
book.get("reviewList").push(review)
book.save()

// SCENRARIO 2 QUERY
const query = new Moralis.Query("Book")
const books = await query.find()

// Peek inside the results
// Returns the actual full review object
books[0].get("reviewList")[0]

Replicating

Below is a full set of queries to get data on the database to replicate Approach 4 above.

const user = Moralis.User.current();
const Book = Moralis.Object.extend("Book");
const Review = Moralis.Object.extend("Review");

// ------------------------------------
// ADDING BOOKS
// ------------------------------------
const book = new Book();
book.set("title", "Frankenstein");
book.set("author", "Mary Shelly");
book.set("reviewList", []);
book.save()

const book = new Book();
book.set("title", "1984");
book.set("author", "George Orwell");
book.set("reviewList", []);
book.save()

const book = new Book();
book.set("title", "Pygmalion");
book.set("author", "George Bernard Shaw");
book.set("reviewList", []);
book.save()


// ------------------------------------
// ADDING REVIEWS TO A BOOK
// ------------------------------------
const query = new Moralis.Query("Book")
query.equalTo("title", "Frankenstein")
const book = await query.first()

const review = new Review();
review.set("content", "Amazing book!");
review.set("user", user);
review.set("book", book);
book.get("reviewList").push(review)
book.save()

const review = new Review();
review.set("content", "So differnent from the hollywood movies");
review.set("user", user);
review.set("book", book);
book.get("reviewList").push(review)
book.save()


// ------------------------------------
// SCENARIO 2 QUERY
// ------------------------------------
const query = new Moralis.Query("Book")
const books = await query.find()

// Peek inside the results
// Returns the actual full review object
books[0].get("reviewList")[0]
1 Like
#2

Scenario 3 Given a query that returns all the reviews from a user

How do you do that part when you need to connect a user to reviews?

There is also the option to use a table with user_id, review_id, review, book_id columns, adding some indexes like book_id, and having a daily/hourly cache for Scenario 2 if the database grows too big

#3

Ok, I updated all the queries above to include a user field for the review objects created. But the important aspect of Scenario 3 is that given some query that returns a list of reviews, what is the best way to structure my database objects so that i can easily get the associated books for all the reviews.

Sorry, Im having trouble understanding this :frowning:

#4

Depending on how much data you have, it may matter or not this optimisation that you want to make.
Like if you have less than 10k reviews then multiple solutions would work fine.

What I wanted to say was something like using Approach 1 and having and additional table for Scenario 2 that it is not real time and it is generated and cached once every x minutes.

#5

Ok, I came up with another approach which seems to fit my needs.

Approach 5

Approach 5: review has pointer to book, and book has counter of number of reviews that gets updated each time a review is added.

  • PRO: Easy to do the queries for all scenarios.
  • PRO: Only a single save operation is needed to save changes.
  • PRO: Queries for scenario 2 do not waste uneccesary bandwith, or require extra processing in javascript.
// ----------------------------------------------
// ADD REVIEW TO EXISTING BOOK
// APPROACH 5. Review has pointer to book, 
//             Book has count of reviews
// ----------------------------------------------
const Book = Moralis.Object.extend("Book");
const Review = Moralis.Object.extend("Review");

// 1. Get book
const query = new Moralis.Query("Book")
query.equalTo("title", "Frankenstein")
const book = await query.first()

// 2. Create the review with pointer to the book, and update count of reviews
const review = new Review();
review.set("content", "Beautiful book");
book.increment("nReviews")
review.set("book", book);
review.save()

Update 1 (2021-10-18 06:22 UTC)

Changed the final line in the code above from book.save() to review.save()

#6

It looks like you forgot to save the review.

1 Like
#7

Oops, I had pasted the wrong line. I have updated it now to use review.save(), which saves the review, and updates the book object on the database in one go :slight_smile:

1 Like