# Multi-table Data Retrieval via Single Frontend Request

**We will&#x20;**_**not**_**&#x20;implement this at this time**, as we will prioritize simplicity and clarity by adhering to the five conventional Rails actions. This approach ensures that we consistently deliver "pure" models or arrays of models to the frontend, and as such, **we won't be implementing custom multi-table retrieval via a single request in the immediate future.**

***

**Overview:**

* [**Reasons**](#reasons)
* [**Potential Implementation Scenarios**](#potential-scenarios-for-custom-controller-actions) (via custom controller actions)
* [**ChatGPT conversation about this**](#chatgpt-conversation-image-version-markdown-version) (for context & background info on decision)

***

## Reasons:

* **Consistent TypeScript Typing for Predictability**
  * Using consistent response types for standard CRUD actions ensures TypeScript types remain predictable.
    * **Example:**
      * A **GET request** to `/projects` endpoint should reliably return data of type `Project` (**:show**) or `Project[]` (**:index**) ([review our API schema here](https://app.gitbook.com/o/dwlkkRKwRTEt4oiL8yTR/s/kVQm1PpPMvCvQccIZPyj/)).
      * Knowing this allows for consistent typing on the frontend, enhancing scalability and maintainability.
      * Also, these frontend types should then be expected to stay in sync with their respective database models --`project.rb` in this example
  * Varying this response format arbitrarily makes frontend typing challenging and can introduce scalability concerns.
* **Maximizing React Query's Caching Benefits**
  * React Query provides built-in caching that can be effectively leveraged when sticking to standard CRUD actions.
  * By retrieving pure, singular data types, we can use these as "building blocks", pulling from cache and combining them on the frontend as needed and however needed. This provides great flexibility, optimizes performance, and reduces unnecessary backend calls.
* **Simplicity in Architecture**
  * Sticking to conventional CRUD operations maintains a clear and straightforward backend structure, reducing complexity.
    * (Introducing combined data endpoints can add layers of complexity to both the backend and frontend, potentially leading to maintenance challenges and inefficiencies.  See more about this in the ChatGPT conversation below.)\


**Additional Points**:

* **Enhanced Debuggability**
  * Keeping the data retrieval pattern consistent makes it easier to debug issues. When each endpoint has a predictable behavior and output, it's simpler to pinpoint and address potential problems.
* **Scalability with Evolving Requirements**
  * Future features or changes might necessitate new data combinations or transformations. Keeping the initial design simple and modular allows for more effortless scalability and adaptations based on evolving requirements.

***

## **Potential Scenarios for Custom Controller Actions**:

While the above reasons make a strong case for keeping CRUD actions pure and predictable, there are scenarios where **custom controller actions** can be beneficial:

* **Complex Business Logic Demands**:
  * Sometimes, a particular business process requires a unique combination of data that would be inefficient to assemble on the frontend. In such cases, a custom backend endpoint might be the optimal solution.

<details>

<summary>Scenario:  Real Estate Platform Consolidated Report</summary>

**Scenario**: Imagine a real-estate platform where an agent wants a consolidated report for a specific property. This report includes details about the property, its current valuation, its historical valuations, recent nearby sales, and any related comments from potential buyers.

Fetching each of these individually from the frontend would require multiple requests: one for property details, another for the valuation history, one for nearby sales, and yet another for comments. Beyond the multiple API calls, you'd also need to correlate this data on the frontend, which can be cumbersome.

Instead, a custom backend endpoint that provides a unified report would be more efficient.

**Example Code**:

```ruby
rubyCopy code# app/controllers/reports_controller.rb
class ReportsController < ApplicationController
  def property_report
    property = Property.find(params[:id])
    report = {
      property_details: property.attributes,
      current_valuation: property.current_valuation,
      valuation_history: property.valuations.order('date DESC'),
      nearby_sales: Property.nearby_sales(property),
      comments: property.comments
    }
    render json: report
  end
end
```

```ruby
rubyCopy code# config/routes.rb
resources :reports, only: [] do
  get 'property_report/:id', on: :collection, action: :property_report
end
```

In this example, by hitting the `property_report` endpoint with a specific property ID, the agent can retrieve a consolidated report in a single API call. This can be especially useful if this type of report is frequently requested, as it keeps frontend logic simple and reduces the total number of API calls.

***

Let's break down the example to emphasize the distinct data sources:

1. **Property Details**: Direct attributes of the `Property` model.
2. **Current Valuation**: Maybe the `Property` has a `has_one` relation with a `Valuation` model, with the latest valuation being treated as the current one.
3. **Valuation History**: This suggests the `Property` has a `has_many` relationship with a `Valuation` model.
4. **Nearby Sales**: This could be a method on the `Property` model (or a service) that fetches sales of other properties in the vicinity (perhaps from a `Sale` model/table).
5. **Comments**: Indicates a `has_many` relationship between `Property` and a `Comment` model.

To make it clearer in our code:

```ruby
app/models/property.rb
class Property < ApplicationRecord
  has_many :valuations
  has_many :comments

  def current_valuation
    valuations.last
  end

  def self.nearby_sales(property)
    # This method would return properties sold in the vicinity of the provided property.
    # Placeholder logic; real implementation would depend on property attributes and your application specifics.
    where("id != ? AND location = ?", property.id, property.location).limit(10)
  end
end

# app/models/valuation.rb
class Valuation < ApplicationRecord
  belongs_to :property
end

# app/models/comment.rb
class Comment < ApplicationRecord
  belongs_to :property
end
```

Now, with these relationships set, the `ReportsController` is aggregating data across multiple tables/models (`Property`, `Valuation`, and `Comment`, with the potential to involve others like `Sale` for nearby sales).

</details>

* **Performance Considerations**:
  * If combining data at the database level (through joins or other techniques) and retrieving it in a single call provides a significant performance boost over making multiple separate requests, a custom endpoint might be justified.

<details>

<summary>Scenario: E-commerce Inventory Report</summary>

#### Performance Considerations: An E-commerce Inventory Report

**Scenario**: Imagine you run an e-commerce platform. As part of daily operations, the platform's inventory managers need to pull a report of products. This report is not just a list of products but also includes:

1. **Product Details**: The basics like name, SKU, price, etc.
2. **Current Stock**: The quantity of each product currently in stock.
3. **Supplier Information**: Data about the supplier providing that particular product.
4. **Last 5 Sales**: Information about the last five sales of each product.

Fetching each of these data sets individually can cause a lag in the user experience, especially if there are thousands of products. As such, there's a need to fetch all this data in a single API request for efficiency.

**Models and Relationships**:

* `Product` has many `Sales`.
* `Product` belongs to a `Supplier`.
* `Product` has an attribute, `current_stock`, representing the stock count.

```ruby
rubyCopy code# app/models/product.rb
class Product < ApplicationRecord
  belongs_to :supplier
  has_many :sales, -> { order(created_at: :desc).limit(5) } # This will get the last 5 sales

  def last_five_sales
    sales.limit(5)
  end
end

# app/models/sale.rb
class Sale < ApplicationRecord
  belongs_to :product
end

# app/models/supplier.rb
class Supplier < ApplicationRecord
  has_many :products
end
```

**Controller**: To reduce the number of separate database hits and the overhead associated with making multiple API requests, you decide to create a custom endpoint to fetch this inventory report.

```ruby
rubyCopy code# app/controllers/reports_controller.rb
class ReportsController < ApplicationController
  def inventory_report
    products = Product.includes(:supplier, :sales).all

    render json: products.map { |product|
      {
        product_details: product.attributes,
        current_stock: product.current_stock,
        supplier_information: product.supplier.attributes,
        last_five_sales: product.last_five_sales.map(&:attributes)
      }
    }
  end
end
```

Here, using the `includes` method, we're making use of Rails' eager loading to pull the related supplier and sales data for each product in one go, reducing the number of separate database queries and thus improving performance. The controller then sends a structured report in a single API call.

</details>

* **Aggregated Data Needs**:
  * For operations like analytics or reporting, where aggregated data from multiple tables is required, a custom endpoint that retrieves the combined data set is often more efficient than multiple separate calls.
* **Third-Party Integrations**:
  * When integrating with third-party services, there might be instances where a custom data format or combination is needed. Custom controller actions can cater to these specific requirements.

<details>

<summary>Scenario: Integrating with a Shipping Partner</summary>

#### Third-Party Integrations: Integrating with a Shipping Partner

**Scenario**: Imagine you're running an online store. As orders come in, you're integrating with a third-party shipping partner's API to get shipping quotes and handle shipping logistics. This shipping partner requires specific data about the order, including:

1. **Order Details**: Order ID, total price, and other basics.
2. **Ordered Items**: Information about each item in the order, including weight and dimensions.
3. **Customer Details**: Shipping address, including name, street, city, postal code, etc.

Instead of forcing the frontend to make multiple calls (one for the order, one for the items, and another for the customer), it's more efficient to create a single custom endpoint that gathers all the required data in the right format for the shipping partner.

**Models and Relationships**:

* `Order` has many `Items`.
* `Order` belongs to `Customer`.

```ruby
rubyCopy code# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :customer
  has_many :items
end

# app/models/item.rb
class Item < ApplicationRecord
  belongs_to :order
end

# app/models/customer.rb
class Customer < ApplicationRecord
  has_many :orders
end
```

**Controller**: Given the requirements of the third-party shipping partner, a custom endpoint is designed to gather and format the data needed:

```ruby
rubyCopy code# app/controllers/shipping_controller.rb
class ShippingController < ApplicationController
  def get_shipping_data
    order = Order.includes(:items, :customer).find(params[:order_id])

    render json: {
      order_details: {
        order_id: order.id,
        total_price: order.total_price,
        date: order.created_at
      },
      items: order.items.map do |item|
        {
          name: item.name,
          weight: item.weight,
          dimensions: "#{item.length}x#{item.width}x#{item.height}"
        }
      end,
      customer_details: {
        name: order.customer.name,
        street: order.customer.street,
        city: order.customer.city,
        postal_code: order.customer.postal_code
      }
    }
  end
end
```

In this approach, the `get_shipping_data` endpoint compiles all required data for the shipping partner in a single call, ensuring that the frontend can seamlessly integrate with the third-party service without additional complexity or multiple API calls.

</details>

* **Frontend Complexity Mitigation**:
  * If combining data on the frontend becomes too intricate, leading to excessive logic, state management, or rendering challenges, it might be simpler and more maintainable to introduce a custom backend action to streamline the process.

However, even when implementing custom controller actions for these scenarios, it's essential to maintain consistency in response formats and ensure proper documentation to keep the system understandable and maintainable.

***

## **ChatGPT Conversation (image version; markdown version)**

**Image:**

<figure><img src="https://3766999655-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfadiBKK9pXrNoqGjTmhS%2Fuploads%2FB98vGsVAS6AQIxC1k4S2%2F14.png?alt=media&#x26;token=aff30cc6-4fed-49d4-b04a-22818d22ca39" alt=""><figcaption></figcaption></figure>

**Markdown (copy/pasted convo) (same as image):**

> Ok, I have a question.\
> \
> I like that with our API design, we will have conventional Rails standard CRUD actions and can expect back "pure" models (arrays of models) from those index/show functions.\
> \
> However, sometimes on the front end, we want to combine data from several different tables (as an example, let's say 3 total tables).\
> \
> If we stick with standard CRUD actions, we have to make 3 api calls, then combined the data on the frontend.\
> \
> I think it would be more efficient to be able to make 1 api call, and handle getting the data from the 3 tables on the backend.\
> \
> What are the standard ways to go about doing this? (the ones that come to mind are: (1) query params (e.g. "joins"), (2) creating new controller actions that specifically do this (though this doesn't seem very scalable), (3) ...that's all I've got for now)ChatGPT

You're correct in noting that while the conventional Rails CRUD actions provide a predictable API structure, sometimes the needs of a client application necessitate retrieving data that spans multiple models or tables. To serve such combined data, there are several strategies you can use:

1. **Query Parameters with Conventional Actions**: This approach involves using query parameters to signal to the API that additional related data should be included with the response.
   * _Pros_: Reuses conventional actions. Can be standardized with proper documentation.
   * _Cons_: Overloading conventional actions might make them complex over time.
   * _Example_: `/users?include=posts,comments`
2. **Custom Actions**: If a specific combined dataset is requested frequently, you can create a custom controller action to serve it.
   * _Pros_: Explicit and clear about its intent.
   * _Cons_: Can make controllers bloated if overused.
   * _Example_: `def user_with_posts_and_comments` in `UsersController`.
3. **Dedicated Endpoints**: Create dedicated controllers for combined datasets.
   * _Pros_: Isolates logic and makes it easier to manage combined datasets.
   * _Cons_: Might introduce new patterns which deviate from standard Rails conventions.
   * _Example_: A `UserReportsController` that provides data-driven insights combining multiple models.
4. **Graph-Based APIs (GraphQL)**: GraphQL is a query language for APIs. Instead of having multiple endpoints, you have a single endpoint where the client specifies exactly what data it needs.
   * _Pros_: Extremely flexible. Frontend can request exactly what it needs.
   * _Cons_: Adds a new layer of complexity. Requires learning GraphQL.
   * _Example_: Using the `graphql-ruby` gem to setup a GraphQL endpoint.
5. **Service Objects**: Instead of stuffing logic into controllers, use service objects. These are plain Ruby objects responsible for handling specific business logic, like gathering data from multiple models.
   * _Pros_: Encourages single responsibility principle and keeps controllers clean.
   * _Cons_: Adds more files and abstractions.
   * _Example_: A `UserReportService` that pulls data from `User`, `Post`, and `Comment` models.
6. **JSON Views (e.g., Jbuilder)**: Jbuilder is a DSL for crafting JSON responses. You can craft complex JSON structures that span multiple models.
   * _Pros_: Offers more control over the JSON output. Can combine data from multiple models in one view.
   * _Cons_: Another layer of abstraction.
   * _Example_: Using Jbuilder to craft a combined `user`, `posts`, and `comments` JSON view.
7. **Includes & Eager Loading**: If the goal is to reduce the number of database queries, consider using `.includes` or `.eager_load` to load related data in a single or fewer queries.
   * _Pros_: Reduces database query count.
   * _Cons_: Might load more data than needed. Might introduce complex SQL joins.
   * _Example_: `User.includes(:posts, :comments)`
8. **Serializer Libraries (e.g., Fast JSON API, ActiveModel::Serializers)**: These libraries allow you to define how a model should be serialized. They often support including associated records.
   * _Pros_: Standardized way of serializing data. Can include related records.
   * _Cons_: Additional abstraction and dependency.
   * _Example_: Using Fast JSON API to serialize a `user` with its `posts` and `comments`.

**Recommendation**:

If the combined datasets are standard across the application, and you anticipate needing more such combinations, consider using GraphQL. It's a bit of an initial investment but can be worth it in the long run.

If the need is sporadic, using a combination of query parameters, dedicated endpoints, or service objects might be more appropriate. You can also utilize serializer libraries or JSON views to craft the desired output.

Remember, the goal is to find a balance between frontend and backend efficiency, code maintainability, and developer happiness.

***

> Hmmm. Since we are using React Query on the front end (which has a good caching system, etc.), maybe it would be overkill (e.g. introduce too much complexity) to try to introduce this right now.ChatGPT

Using React Query on the frontend can indeed help alleviate some of the challenges associated with making multiple requests, thanks to its caching and data synchronization features. Given this context, let's evaluate the decision:

**Advantages of Sticking with React Query and Multiple Requests**:

1. **Caching**: React Query provides caching out of the box, which means subsequent requests for the same data can be served from cache instead of hitting the server again.
2. **Simplicity on the Backend**: By not introducing new combined endpoints or adding GraphQL, you keep your Rails backend simpler and closer to conventional CRUD actions.
3. **Incremental Requesting**: If the user navigates between views/pages, only the new data might need to be fetched, while the rest can be pulled from React Query's cache. This could result in better perceived performance.
4. **Flexibility**: Fetching data in smaller, more granular chunks gives you more flexibility on the frontend. If you decide to change how data is displayed, you might not need to make any backend changes.

**Potential Challenges**:

1. **Overhead from Multiple Requests**: Even with caching, making multiple requests can have more overhead than a single consolidated request, especially if the requests can't be parallelized effectively.
2. **Data Consistency**: Managing data consistency across multiple requests might be a bit more challenging. For example, if data from one request is updated, it might affect data from another request.
3. **Complexity in Frontend Logic**: If combining data from multiple requests frequently, the logic on the frontend can become complex.

Given the capabilities of React Query and considering the potential challenges, here's what you might consider:

1. **Start Simple**: If you're in the early stages of your application or feature, it's often a good idea to start simple. Make multiple requests and let React Query handle caching. As the application evolves and you identify specific performance bottlenecks or areas of complexity, consider optimizations.
2. **Evaluate Real-World Usage**: It's essential to understand how users interact with your application. If they typically access views that require combined data from multiple models, then optimizing for that scenario makes sense. Otherwise, it might be premature optimization.
3. **Avoid Over-Optimizing Early**: While it's tempting to optimize for all possible scenarios from the beginning, it's often more efficient to build what you know you need now and then refactor or optimize based on real-world usage and performance data.

In summary, given React Query's capabilities, starting with multiple requests and leveraging its caching and data synchronization might be a good approach. As you gather more data on how your application is used and where the bottlenecks are, you can make more informed decisions on potential optimizations or backend changes.
