How to deal with views / materialized views?

[SemaLuna]

Intro

This may go a little bit into AdonisJS rather than strictly for this project... but feel free to correct me as needed. Some background info...

Views are almost-like-tables - they are queries that return a set of data, and that response is your new 'table' (and views can be used almost anywhere where tables are used). The data is not stored, so a view always executes a new query under the hood.

Materialized Views are similar, except the data is stored - so accessing a materialized view is not as computationally expensive as a regular query (more akin to accessing a regular table) - not every DB supports this.

Lucid has view support - though I have been having trouble finding how to properly model this data and display it.

I have information that I want to display to the user that's related to existing data (e.g. squad_management) BUT the data is not directly available anywhere and I rather not store it on the database (because then I would have two different ways at arriving at the same information).

Additionally, I do not want to re-calculate on query time every time - so I created a materialized view for this information, and I am trying to display it not as a relationship but as an attribute of squad_management.

This is my attempt to do so.

Goals

I would like to:

• Model a (materialized) view
• Display the data from that view as part of a separate resource

Work so far

Models

I have my original model (Squad Management) which is a regular model, and has a relationship to my materialized view. It has a scope that preloads the relationship to the materialized view - and a getter that calculates the information that I need.

I also have my 'custom' model (since the schema won't look at the DB views) - hopefully that's the appropriate way of doing it.

  // squad_management.ts - excerpt
  ...
  @hasOne(() => SquadManagementPointsPerGameweek)
  declare pointsPerGameweek: HasOne<typeof SquadManagementPointsPerGameweek>

  static hydrated = scope((query: ModelQueryBuilderContract<typeof SquadManagement>) => {
    query.preload('pointsPerGameweek')
  })

  get points() {
    return this.pointsPerGameweek?.totalPoints ?? 0
  }
  
  // OTHER FILE
  // squad_management_points_per_gameweek.ts - full file
  export default class SquadManagementPointsPerGameweek extends BaseModel {
    public static table = 'squad_management_points_per_gameweek'

    static $columns = ['squad_management_id', 'total_points'] as const
    $columns = SquadManagementPointsPerGameweek.$columns
    @column({ isPrimary: true })
    declare squadManagementId: number
    @column()
    declare totalPoints: number

    @belongsTo(() => SquadManagement)
    declare squadManagement: BelongsTo<typeof SquadManagement>
 }

Resource

Using make:jsonapi:resource, I created the resource / controller for my squad_management model.

(!) Note - it would be nice if the utility method also added the resource to the import in config/jsonapi.ts - it mentions it in the output, but I've already forgotten to do so twice and wondered what's wrong :)

  // squad_management_resource.ts - excerpt
  attributes() {
    return {
      ...super.attributes,
      points: this.resource.points,
    }
  }

Controller

In the controller, I made sure to apply the scope in the response, so that the points getter has sufficient information.

  // resource_management_controller.ts
  /**
   * GET /squad-managements
   */
  async index({ jsonApi }: HttpContext) {
    const rows = await jsonApi
      .query(SquadManagement)
      .withScopes((scopes) => scopes.hydrated())
      .paginate(...jsonApi.page)
      
    return jsonApi.render(rows)
  }

Problem

Everything is mostly working as expected - I am preloading the relationship with the scopes, the points attribute is being sent back in the response - however, the field is actually returning a string rather than a number - I assume this is because of the interaction between View and this project, but I have yet to verify (I am technically camping so haven't had enough time for a deep dive).

I did manage to verify that displaying the related resource directly (rather than a relationship) exhibits the SAME problem.

So - let me know if this is a bug OR if I am doing something wrong, or anything else... (and hopefully my foray into views helps others!)

[evoactivity]

Not sure what would cast any attribute in a resource.

If you return/log the direct lucid models do you see points as a number?

async index({ jsonApi }: HttpContext) {
  const rows = await SquadManagement.query()
    .withScopes((scopes) => scopes.hydrated())
  console.log(rows[0])
  return rows[0]
}

[SemaLuna]

@evoactivity

I don't see it there at all - it's not part of Lucid models (since it's a getter to a value from a relationship)

I'd put here the full response, but I solved it :)

The configuration in my code was fine - but the created view had the column type as numeric rather than integer

postgres=# \d squad_management_points_per_gameweek 
Materialized view "public.squad_management_points_per_gameweek"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 squad_management_id | integer |           |          | 
 total_points        | numeric |           |          | 

So, the database type of the column because of the view was numeric - which Lucid maps to internal type decimal, which maps to TS string (to preserve precision) - reference.

Thus the solve - I updated my migration file to specifically cast that column as an integer rather than numeric, and it works fully as intended.

User error at the end, but at least this confirms we can use views normally :)

[evoactivity]

Nice! Glad to know I'm not silently casting things 😆