Issue
I’m working on an exercise for a Bootcamp and the function isn’t producing the desired effect. So the goal is to query the database with a first name/part of a first name. I can successfully query the DB and return results. However, when I map the results it’s not mapping everything that I need, it’s only returning the id, while everything else is returning undefined. I have a similar function that grabs all data from the database, returns all information/functionality, and is working fantastically.
This is my DB query:
static async getList(term){
const results = await db.query(`
SELECT id, first_name AS firstName, last_name AS lastName, phone, notes
FROM customers
WHERE first_name LIKE $1
`, [term]);
const customers = results.rows.map(c => new Customer(c));
return customers;
}
This is my route:
router.get("/results", async function(req, res, next){
try{
const term = req.query.customer_query;
const customers = await Customer.getList(`${term}%`);
return res.render("results_list.html", { customers });
} catch(e){
return next(e);
}
});
This is what is returned for customers:
[
Customer {
id: 17,
firstName: undefined,
lastName: undefined,
fullName: 'undefined undefined',
phone: null,
notes: 'Spring business speak nothing.'
},
Customer {
id: 50,
firstName: undefined,
lastName: undefined,
fullName: 'undefined undefined',
phone: '+88(6)4017259339',
notes: ''
},
Customer {
id: 56,
firstName: undefined,
lastName: undefined,
fullName: 'undefined undefined',
phone: null,
notes: ''
}
]
Per request from fardjad:
Console.log results:
{
"command": "SELECT",
"rowCount": 0,
"oid": null,
"rows": [],
"fields": [
{
"name": "id",
"tableID": 16397,
"columnID": 1,
"dataTypeID": 23,
"dataTypeSize": 4,
"dataTypeModifier": -1,
"format": "text"
},
{
"name": "firstname",
"tableID": 16397,
"columnID": 2,
"dataTypeID": 25,
"dataTypeSize": -1,
"dataTypeModifier": -1,
"format": "text"
},
{
"name": "lastname",
"tableID": 16397,
"columnID": 3,
"dataTypeID": 25,
"dataTypeSize": -1,
"dataTypeModifier": -1,
"format": "text"
},
{
"name": "phone",
"tableID": 16397,
"columnID": 4,
"dataTypeID": 25,
"dataTypeSize": -1,
"dataTypeModifier": -1,
"format": "text"
},
{
"name": "notes",
"tableID": 16397,
"columnID": 5,
"dataTypeID": 25,
"dataTypeSize": -1,
"dataTypeModifier": -1,
"format": "text"
}
],
"_parsers": [
null,
null,
null,
null,
null
],
"_types": {
"_types": {
"arrayParser": {},
"builtins": {
"BOOL": 16,
"BYTEA": 17,
"CHAR": 18,
"INT8": 20,
"INT2": 21,
"INT4": 23,
"REGPROC": 24,
"TEXT": 25,
"OID": 26,
"TID": 27,
"XID": 28,
"CID": 29,
"JSON": 114,
"XML": 142,
"PG_NODE_TREE": 194,
"SMGR": 210,
"PATH": 602,
"POLYGON": 604,
"CIDR": 650,
"FLOAT4": 700,
"FLOAT8": 701,
"ABSTIME": 702,
"RELTIME": 703,
"TINTERVAL": 704,
"CIRCLE": 718,
"MACADDR8": 774,
"MONEY": 790,
"MACADDR": 829,
"INET": 869,
"ACLITEM": 1033,
"BPCHAR": 1042,
"VARCHAR": 1043,
"DATE": 1082,
"TIME": 1083,
"TIMESTAMP": 1114,
"TIMESTAMPTZ": 1184,
"INTERVAL": 1186,
"TIMETZ": 1266,
"BIT": 1560,
"VARBIT": 1562,
"NUMERIC": 1700,
"REFCURSOR": 1790,
"REGPROCEDURE": 2202,
"REGOPER": 2203,
"REGOPERATOR": 2204,
"REGCLASS": 2205,
"REGTYPE": 2206,
"UUID": 2950,
"TXID_SNAPSHOT": 2970,
"PG_LSN": 3220,
"PG_NDISTINCT": 3361,
"PG_DEPENDENCIES": 3402,
"TSVECTOR": 3614,
"TSQUERY": 3615,
"GTSVECTOR": 3642,
"REGCONFIG": 3734,
"REGDICTIONARY": 3769,
"JSONB": 3802,
"REGNAMESPACE": 4089,
"REGROLE": 4096
}
},
"text": {},
"binary": {}
},
"RowCtor": null,
"rowAsArray": false
}
The code from the Customer class:
class Customer {
constructor({ id, firstName, lastName, phone, notes }) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.fullName = `${this.firstName} ${this.lastName}`
this.phone = phone;
this.notes = notes;
}
/** find all customers. */
static async all() {
const results = await db.query(
`SELECT id,
first_name AS "firstName",
last_name AS "lastName",
phone,
notes
FROM customers
ORDER BY last_name, first_name`
);
return results.rows.map(c => new Customer(c));
}
/** get a customer by ID. */
static async get(id) {
const results = await db.query(
`SELECT id,
first_name AS "firstName",
last_name AS "lastName",
phone,
notes
FROM customers WHERE id = $1`,
[id]
);
const customer = results.rows[0];
if (customer === undefined) {
const err = new Error(`No such customer: ${id}`);
err.status = 404;
throw err;
}
return new Customer(customer);
}
/*
STEP 7 Syntax:
SELECT first_name, last_name, phone, notes
FROM customers
WHERE first_name LIKE '$1%';
*/
static async getList(term){
const results = await db.query(`
SELECT id, first_name AS firstName, last_name AS lastName, phone, notes
FROM customers
WHERE first_name LIKE $1
`, [term]);
console.log(JSON.stringify(results, null, 2))
const customers = results.rows.map(c => new Customer(c));
return customers;
}
/*
STEP 8 SQL Syntax:
SELECT COUNT(customer_id) AS num_reservations, customer_id FROM reservations
GROUP BY customer_id
ORDER BY num_reservations DESC
LIMIT 10;
*/
/** get all reservations for this customer. */
async getReservations() {
return await Reservation.getReservationsForCustomer(this.id);
}
/** save this customer. */
async save() {
if (this.id === undefined) {
const result = await db.query(
`INSERT INTO customers (first_name, last_name, phone, notes)
VALUES ($1, $2, $3, $4)
RETURNING id`,
[this.firstName, this.lastName, this.phone, this.notes]
);
this.id = result.rows[0].id;
} else {
await db.query(
`UPDATE customers SET first_name=$1, last_name=$2, phone=$3, notes=$4
WHERE id=$5`,
[this.firstName, this.lastName, this.phone, this.notes, this.id]
);
}
}
}
module.exports = Customer;
Solution
So it was something STUPID simple. I thought I caught all the similarities between the different SELECT statements but completely missed the quotes around the AS "firstName" and AS "lastName". After adding that in, it’s working as it should. Just have to figure out some template stuff. Thanks for your help fardjad!!!