I don't understand relationships

[rgonzlz]

Hi, I'm working on a development project and using Lucid, but I don’t fully understand how relationships work. I come from Mikro ORM and maybe I’m getting confused.

I have two tables, Clients and Organizations, where a client has an organization.

migration/client.ts

	this.schema.createTable(this.tableName, (table) => {

		table.uuid('client_id').primary();
		table.string('organization_id').nullable().references('organizations.organization_id').onDelete('CASCADE');

		table.timestamp('created_at').notNullable();
		table.timestamp('updated_at').nullable();

	});

migration/organization.ts

	this.schema.createTable(this.tableName, (table) => {
		table.uuid('organization_id').primary();

		table.timestamp('created_at');
		table.timestamp('updated_at');
	});

model/client.ts

export default class Client extends compose(BaseModel, AuthFinder) {

	static selfAssignPrimaryKey = true;

	@column({ isPrimary: true })
	declare clientId: string;

	@hasOne(() => Organization, {
		foreignKey: 'organizationId',
	})
	declare organizationId: HasOne<typeof Organization>;

	@beforeCreate()
	static assignUuid(client: Client) {
		client.clientId = randomUUID();
	}

}

model/organization.ts

export default class Organization extends BaseModel {

	static selfAssignPrimaryKey = true;

	@column({ isPrimary: true })
	declare organizationId: string;

	@beforeCreate()
	static assignUuid(organization: Organization) {
		organization.organizationId = randomUUID();
	}

}

My idea is that when I create the organization, it should update the field in the Clients table.

I thought that by doing this, the UUID of the organization would be automatically assigned in the table, but it’s not working.

const client = auth.getUserOrFail();

const newOrganization = await client.related('organizationId').create(dataNewOrg);

console.log(client.organizationId); // null

The organization is created perfectly, but the organization_id field in the Clients table remains null.

clients table

Clearly, I am confusing something and doing it wrong. What should I do?

Thanks!

[thetutlage]

The client belongs to an organization in this case. The simplest trick to differentiate between belongsTo and hasOne is, the table/model that has FK will create a belongsTo relationship with the other table/model.

[rgonzlz]

Thank you very much for your quick response.

I made the change you mentioned, but I am getting an error.

"Client.organizationId" expects "organizationId" to exist on "Client" model, but is missing

model/client.ts

export default class Client extends compose(BaseModel, AuthFinder) {

	@column({ isPrimary: true })
	declare clientId: string;

	@belongsTo(() => Organization, {
		foreignKey: 'organizationId',
	})
	declare organizationId: BelongsTo<typeof Organization>;

}

Could I have used it incorrectly?

const dataNewOrg = {
	organizationId: randomUUID(),
};
const newOrganization = await Organization.create(dataNewOrg);

await client.related('organizationId').associate(newOrganization);

[rgonzlz]

Well, I'm definitely doing something wrong, and I don't understand what.

I'm using the model in another function where I need organization, and when I load it, it gives me the same error.

	public async details({ response, auth }: HttpContext) {

		const client = auth.getUserOrFail(); // I'm using the Adonis authentication module.

		await client.load('organizationId');

	}

Thanks!

[thetutlage]

I am not sure why you are defining the relationship on the foreign key. Maybe Mikro ORM has this thing. Lemme share how it should be setup.

clients (table)
  client_id
  organization_id
  created_at
  updated_at

class Client extends BaseModel {
  @column()
  clientId: string

  @column()
  organizationId: string

  @column.dateTime()
  createdAt: DateTime

  @column.dateTime()
  updatedAt: DateTime
}

organizations
  organization_id
  created_at
  updated_at

class Organization extends BaseModel {
  @column()
  organizationId: string

  @column.dateTime()
  createdAt: DateTime

  @column.dateTime()
  updatedAt: DateTime
}

Till now, you have one to one mapping between the tables and the models. Now, you define a relationship as follows.

class Client extends BaseModel {
  @column()
  clientId: string

  @column()
  organizationId: string

  @column.dateTime()
  createdAt: DateTime

  @column.dateTime()
  updatedAt: DateTime

+ @belongsTo(() => Organization)
+ organization: BelongsTo<typeof Organization>
}

[rgonzlz]

Hello!

Thank you very much for your response.

Indeed, I was confused with MikroORM; it does map the relationship at the database level as well as in ORM using the same field.

I made the changes you mentioned, and now I'm getting another error:

Exception: ".where" expects value to be defined, but undefined is passed
	at ModelQueryBuilder.validateWhereSingleArgument (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/database/query_builder/chainable.js:106:19)
	at ModelQueryBuilder.where (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/database/query_builder/chainable.js:294:18)
	at Proxy.$getQueryFor (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/orm/base_model/index.js:1579:52)
	at Adapter.update (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/orm/adapter/index.js:70:24)
	at Proxy.save (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/orm/base_model/index.js:1394:34)
	at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
	at async node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/orm/relations/belongs_to/query_client.js:64:13
	at async managedTransaction (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/utils/index.js:141:26)
	at async BelongsToQueryClient.associate (node_modules/.pnpm/@adonisjs+lucid@21.2.0_@adonisjs+assembler@7.8.2_@adonisjs+core@6.13.1_luxon@3.5.0_mysql2@3.11.2/node_modules/@adonisjs/lucid/build/src/orm/relations/belongs_to/query_client.js:59:9)
	at OrganizationsController.store (app/controllers/organizations.ts:67:3)

I found on GitHub that it might be due to the primary key not being defined, but it is. It might not be related, but it's the only thing I found.

Here’s the code as it stands:

models/organization.ts

export default class Organization extends BaseModel {

	@column({ isPrimary: true })
	declare organizationId: string;

	@column()
	declare name: string;

	@column.dateTime({ autoCreate: true })
	declare createdAt: DateTime;

	@column.dateTime({ autoCreate: true, autoUpdate: true })
	declare updatedAt: DateTime;

}

models/client.ts

export default class Client extends compose(BaseModel, AuthFinder) {

	@column({ isPrimary: true })
	declare clientId: string;

	@column()
	declare organizationId: string;

	@column.dateTime({ autoCreate: true })
	declare createdAt: DateTime;

	@column.dateTime({ autoCreate: true, autoUpdate: true })
	declare updatedAt: DateTime;

	@belongsTo(() => Organization, {
		foreignKey: 'organizationId',
	})
	declare organization: BelongsTo<typeof Organization>;
}

controllers/organizations.ts

const newOrganization = await Organization.create({
	organizationId: randomUUID(),
});

await client.related('organization').associate(newOrganization); // line 67

Thanks again, and sorry for so many questions.

[rgonzlz]

Hello!

I'm still testing and experimenting.

It seems that if I set selfAssignPrimaryKey to true in the Client model and assign the UUID, I get an error with the organization when updating the client. Is this behavior correct? If so, can you tell me why? Should I not auto-assign the UUID from the model and instead do it manually?

Thanks!

model/client.ts

export default class Client extends compose(BaseModel, AuthFinder) {

	static selfAssignPrimaryKey = true;

	@column({ isPrimary: true })
	declare clientId: string;

	@column()
	declare organizationId: string;

	@beforeCreate()
	static assignUuid(client: Client) {
		client.clientId = randomUUID();
	}

	@belongsTo(() => Organization, {
		foreignKey: 'organizationId',
	})
	declare organization: BelongsTo<typeof Organization>;

}

model/organization.ts

export default class Organization extends BaseModel {

	static selfAssignPrimaryKey = true;

	@column({ isPrimary: true })
	declare organizationId: string;

	@column()
	declare name: string;

	@column.dateTime({ autoCreate: true })
	declare createdAt: DateTime;

	@column.dateTime({ autoCreate: true, autoUpdate: true })
	declare updatedAt: DateTime;

	@beforeCreate()
	static assignUuid(organization: Organization) {
		organization.organizationId = randomUUID();
	}

}

controller/organization.ts

const newOrganization = await Organization.create({
	name,
});

client.organizationId = newOrganization.organizationId;

await client.save();

[thetutlage]

Okay, so let me share more info around the primary keys this time

• Lucid expects that you will not set the primary keys within the code and they will be auto generated by the database, as it happens with auto incrementing values.
• If you want to define the primary key manually within the code. You have to tell Lucid about it via the selfAssignPrimaryKey flag.

Since you have set the selfAssignPrimaryKey, you will have to create the Organization as follows.

const newOrganization = await Organization.create({
   name,
   organizationId: randomUUID(),
});

[rgonzlz]

Hi!

Thank you very much for your help, I think I've understood everything now.

I already have the project running without any issues!