Need help with relationships


#1

I have a couple of issues that I’ve tried to isolate, but I think they have are more related to the way I’m structuring my relationships, so I want to see if I can get some help with my overall relationships and maybe that can solve some of my specific issues.

So, to outline my structure:

A “User” is created through the google OAuth flow.
A “User” belongsTo “Baby”
A “Baby” hasMany “User”
A “Baby” hasMany “DiaryItem”
A “DiaryItem” belongsTo “Baby”

A “User” is connected manually through an admin panel to a “Baby” using .associate() once the “User” has logged in and the “Baby” has been created.

First off, after outlining my structure, does it seem like the proper way to do it? Should I have the “Baby” belongToMany “User” and the “User” hasOne “Baby”? Would I need a pivot table for that at all?

Now, I want to be able to create/list/destroy etc. "DiaryItem"s. This is where my first problem arises. To list only the DiaryItems of the Baby that is associated with the top-level User, I need to do something like this:

async index({ auth }) {
    const user = await auth.getUser()
    const baby = await user.baby().fetch()
    if (baby) {
      return baby.diary_items().fetch()
    }
    return []
  }

This is fairly verbose and clunky, and involves multiple seemingly unnecessary DB calls.
What I want is something like this:

  async index({ auth }) {
    const user = await auth.getUser()
    return await user
      .baby()
      .diary_items()
      .fetch()
  }

This is much cleaner.

I was told about manyThrough, but that doesn’t seem to want to cooperate with my relationship structure. Even if I could get that working, what about “oneThrough” when it is a one -> one -> one relationship?

I want to remove my current verbosity, and I think my relationships might have some underlying issues causing it, so I hope a thorough explanation might help.


#2

Your relationships are correctly structured. Lemme share how they should look in the database.

  1. table (users) should have the baby_id column.
  2. table (diary_items) should have the baby_id column too.

The relationships inside models, should look like this.

User.js

class User {
  baby () {
    return this.belongsTo('App/Models/Baby')
  }

  diaryItems () {
    return this.manyThrough('App/Models/Baby', 'diaryItems')
  }
}

DiaryItem.js

class DiaryItem {
  baby () {
    return this.belongsTo('App/Models/Baby')
  }
}

Baby.js

class Baby {
  users () {
    return this.hasMany('App/Models/User')
  }

  diaryItems () {
    return this.hasMany('App/Models/DiaryItem')
  }
}

Now if I got it right, then you want to get the diaryItems for the baby which is associated with the current user.

In that case your query must be as follows.

const diaryItems = await auth.user.diaryItems().fetch()

There is no need of oneThrough here, since each baby hasMany diaryItems.


#3

Oh ok, I was using manyThrough incorrectly I think.

If for example a Baby hasOne DiaryItem rather than hasMany, would I still use manyThrough?

Thanks for the help!


#4

Yes, manyThrough respects the parent relationship


#5

Gotcha, thanks


#6

I’m getting an error now that I was getting before when trying to use manyThrough:

db_1      | 2018-07-02 19:44:30.881 UTC [84] ERROR:  column babies.user_id does not exist at character 112
db_1      | 2018-07-02 19:44:30.881 UTC [84] STATEMENT:  select "diary_items".* from "diary_items" inner join "babies" on "babies"."id" = "diary_items"."baby_id" where "babies"."user_id" = $1
server_1  | { error: column babies.user_id does not exist
server_1  |     at Connection.parseE (/code/server/node_modules/pg/lib/connection.js:553:11)
server_1  |     at Connection.parseMessage (/code/server/node_modules/pg/lib/connection.js:378:19)
server_1  |     at Socket.<anonymous> (/code/server/node_modules/pg/lib/connection.js:119:22)
server_1  |     at Socket.emit (events.js:182:13)
server_1  |     at addChunk (_stream_readable.js:283:12)
server_1  |     at readableAddChunk (_stream_readable.js:264:11)
server_1  |     at Socket.Readable.push (_stream_readable.js:219:10)
server_1  |     at TCP.onread (net.js:635:20)
server_1  | From previous event:
server_1  |     at Client_PG._query (/code/server/node_modules/knex/lib/dialects/postgres/index.js:287:12)
server_1  |     at Client_PG.query (/code/server/node_modules/knex/lib/client.js:206:17)
server_1  |     at Runner.<anonymous> (/code/server/node_modules/knex/lib/runner.js:155:36)
server_1  | From previous event:
server_1  |     at /code/server/node_modules/knex/lib/runner.js:61:21
server_1  |     at runCallback (timers.js:696:18)
server_1  |     at tryOnImmediate (timers.js:667:5)
server_1  |     at processImmediate (timers.js:649:5)
server_1  | From previous event:
server_1  |     at Runner.run (/code/server/node_modules/knex/lib/runner.js:47:31)
server_1  |     at Builder.Target.then (/code/server/node_modules/knex/lib/interface.js:39:43)
server_1  |   name: 'error',
server_1  |   length: 113,
server_1  |   severity: 'ERROR',
server_1  |   code: '42703',
server_1  |   detail: undefined,
server_1  |   hint: undefined,
server_1  |   position: '112',
server_1  |   internalPosition: undefined,
server_1  |   internalQuery: undefined,
server_1  |   where: undefined,
server_1  |   schema: undefined,
server_1  |   table: undefined,
server_1  |   column: undefined,
server_1  |   dataType: undefined,
server_1  |   constraint: undefined,
server_1  |   file: 'parse_relation.c',
server_1  |   line: '3293',
server_1  |   routine: 'errorMissingColumn',
server_1  |   status: 500 }

This seems like it wants me to have a user_id on my Baby, but that isn’t how my relationships are set up, hence why I thought I was doing something wrong.


#7

Does manyThrough not work with the relationship structure since User belongsTo Baby rather than hasOne Baby?


#8

As I said earlier, the problem is not with manyThrough but instead with the fields missing in Database tables for relationships to work.

You have to take one relationship at a time, read the docs and fix your table structure


#9

All my models and tables are set up as you stated, so I don’t know what I could’ve done incorrectly. After reading the docs, and looking at the resulting SQL query, it looks like it isn’t possible with my relationships.

manyThrough selects all the diaryItems where babies.id = diary_items.baby_id where the babies.user_id = $1 (the user id from the caller model).Everything

This makes sense if the user hasMany or hasOne baby, where now the baby would have a user_id to check for, but it doesn’t seem to work when the user has a baby_id instead.

I’d like it to work, but it looks like it is made to work with a has => has relationship rather than a belongs => has relationship.


#10

Can you share the table structure for all the tables?


#11

Sure.

The User table:

   this.create('users', table => {
      table.increments()
      table
        .string('email')
        .unique()
        .notNullable()
      table.string('token').notNullable()
      table.integer('baby_id').unsigned()

      table.timestamps()
    })

The Baby table:

    this.create('babies', table => {
      table.increments()
      table.timestamps()
    })

The DiaryItem table:

this.create('diary_items', table => {
      table.increments()
      table
        .integer('baby_id')
        .unsigned()
        .notNullable()
      table.timestamps()
    })

I removed table columns that weren’t related to relationships for brevity


#12

Cool, lemme try to set it up and see what’s wrong


#13

Alright, thanks