Relational Queries

Objective

1. Prisma queries.
2. Cascade delete and update.
3. Relation queries, projections, and inclusions.
4. Filtering, sorting, and full-text search.
5. Aggregation, grouping, and summarization.

1 Bank Accounts

1. Extend the schema from 10.2 Bank Accounts to include clients and link them to their accounts.

   prisma/schema.prisma

   model Client {
   }
    
   model Account {
     accountClient Client  @relation(fields: [client], references: [id], onDelete: Cascade, onUpdate: Cascade)
     client        String
   }
    
   model Transaction {
     transactionAccount Account  @relation(fields: [account], references: [id], onDelete: Cascade, onUpdate: Cascade)
     account            String
   }

2. Create a data repository service, repos/clients.js, with CRUD methods, and update the other services, if required:

   repos/clients.js

   import prisma from "@/repos/prisma";

3. Extend the data repository services with the following methods:

   1. getAccountBalanceAvg() returns the average account balances by type.
   2. getAccountMinMax() returns the accounts with the smallest and largest balances.
   3. getAccountTop3() returns the top three accounts with the largest balance.
   4. getTransactionAccount(id, from, to) returns the transactions for a particular account within a date range.
   5. getTransactionTotal(from, to) return the total amount of withdrawals and deposits made within a date range.
   6. getTransactionAccountTotal(id, from, to) returns the total amount of withdrawals and deposits for an account, made within a date range.
   7. getClientReport(id) returns all the information about a certain client, including their accounts, and for each account, its corresponding transactions.
   8. getClientTop() returns the client with the largest total balance.

2 Project Tracker

1. Extend the schema from 10.1 Task Tracker to manage multiple projects, with each project having multiple tasks.

2. Update the schema to be able to attach files to projects and tasks.
3. Update the schema to be able to create tasks within other tasks.
4. Show the total number of projects that are past their deadline.
5. Show the total number of tasks that are past their deadline.
6. Show for every project the total number of its tasks.
7. Show for every task the total number of its subtasks.

Resources

• Prisma Client Queries (opens in a new tab)