I have defined a one-to-one relationship between the Customer table and the Address table using ORM Sequelize
The source code for Customer.js is as follows:
// One to One Relationship
// Customer.js
// Include Sequelize module.
const Sequelize = require('sequelize');
const Datatypes = require('Sequelize');
const path = require("path");
// Import sequelize object,
// Database connection pool managed by Sequelize.
// Import db_sequelize
const db_sequelize = require(path.join(__dirname,'..','config','db_sequelize.js'));
const Customer = db_sequelize.define('Customer', {
customer_id:{
// Sequelize module has INTEGER Data_Type.
type:Datatypes.INTEGER,
// To increment user_id automatically.
autoIncrement:true,
// user_id can not be null.
allowNull:false,
// For uniquely identify user.
primaryKey:true
},
first_name: { type: Datatypes.STRING, allowNull:true },
last_name: { type: Datatypes.STRING, allowNull:true },
email: { type: Datatypes.STRING, allowNull:true},
address_id: { type: Datatypes.INTEGER, allowNull:true},
// dates => current time
myDate: { type: Datatypes.DATE,
defaultValue: Sequelize.NOW },
// Timestamps
createdAt: Datatypes.DATE,
updatedAt: Datatypes.DATE,
});
// Exporting Customer, using this constant
// we can perform CRUD operations on
// 'Customer' table.
console.log(Date() + " " + "Model Customer has been defined");
module.exports = Customer;
The source code for Address.js is as follows:
// Address.js
// Include Sequelize module.
const Sequelize = require('sequelize');
const Datatypes = require('sequelize');
const path = require("path");
// Import sequelize object,
// Database connection pool managed by Sequelize.
// Import db_sequelize
const db_sequelize = require(path.join(__dirname,'..','config','db_sequelize.js'));
const Address = db_sequelize.define('Address', {
address_id:{
// Sequelize module has INTEGER Data_Type.
type:Datatypes.INTEGER,
// To increment user_id automatically.
autoIncrement:true,
// user_id can not be null.
allowNull:false,
// For uniquely identify user.
primaryKey:true
},
address: { type: Datatypes.STRING, allowNull:true },
postal_code: { type: Datatypes.STRING, allowNull:true },
district: { type: Datatypes.STRING, allowNull:true},
// dates => current time
myDate: { type: Datatypes.DATE,
defaultValue: Datatypes.NOW },
// Timestamps
createdAt: Datatypes.DATE,
updatedAt: Datatypes.DATE,
});
const Customer = require(path.join(__dirname,'..','models', 'Customer.js'));
// 1:1 Relation between Customer and Address
// Each Customer has only One Address
Customer.hasOne(Address, {
foreignKey: {
name: 'address_id'
}
});
Address.belongsTo(Customer, {
foreignKey: 'address_id'
});
// Exporting Address, using this constant
// we can perform CRUD operations on
// 'Address' table.
console.log(Date() + " " + "Model Address has been defined");
module.exports = Address;
When displaying the address value associated with the customer, the address is displayed correctly without using the .ejs template
// TEST ONE TO ONE RELATIONSHIP
// Get Customer address for customer_id equal 1
let get_address_customer_id = async function(){
try {
let customer = await Customer.findByPk(1);
// find by primary key
let customerAddress = await customer.getAddress();
//Address belongs to Customer
//then customer.getAddress();
//get the address
//for customer_id equal 1
console.log("NUI del Customer : ", customer.customer_id);
console.log("Customer Name : ", customer.first_name + " " + customer.last_name);
console.log("Customer Address : ", customerAddress.address);
} catch (error) { console.error("Error log", error);}
}
get_address_customer_id();
NUI del Customer : 1
Customer Name : First name 1 Last name 1
Customer Address : Address 1
However, when using the .ejs template the one to one relationship between Customer and Address is not working. In the one to one relationship test I use let customerAddress = await customer.getAddress(); However, I do not use this in the .ejs template, I assume this is the difference. However it is assumed that if the 1 to 1 relationship works you should get the address linked to the customer
// list_customers.js
// Set router
const express = require("express");
const router = express.Router();
const path = require("path");
router.get("/", async (req, res) => {
//Get the model for Customer
const Customer = require(path.join(__dirname,'..', 'models', 'Customer.js'));
// Find all Customers
const customers = await Customer.findAll();
res.render("view_listar_customers.ejs", { model: customers });
});
module.exports = router;
The .ejs template code to display the relationship data between Customer and Address is as follows:
<tbody>
<% for (const customer of model) { %>
<tr>
<td><%= customer.customer_id %></td>
<td><%= customer.first_name %>, <%= customer.last_name %></td>
<td><%= customer.address %></td>
<td class="d-print-none">
<a class="btn btn-sm btn-warning" href="/edit_customer/<%= customer.customer_id %>">Edit</a>
<a class="btn btn-sm btn-danger" href="/delete_customer/<%= customer.customer_id %>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
A guide to solve the problem would be highly appreciated. Thank you.
I found an alternative to display the data using Sequelize's Raw Queries. I attach the source code section in case it is useful for someone who wants to display the result of a query of all the records using a one to one relationship with ORM Sequelize and have the results displayed in a .ejs template.
For the implementation of the One to One relationship, the following documentation was used. Thanks to Allan James Lapid SEE: https://gist.github.com/ajLapid718/ca67efc0360c617e5eebb6f1342ae53e
Address.js
Customer.js
app.js
view: