I am trying to some complex queries in my bird database to get all the birds back but I want to be able to use queries in the url to get back by diet and to sort accordingly. See the Insomnia images below:
1st query: GET request: localhost:3000/api/birds?sort_by=weight_g&diet=carnivorous
2nd query: GET request localhost:3000/api/birds?diet=piscivorous&sort_by=wingspan_cm&order=DESC
Now I will show you the Javascript code. Error handling has been left out until I get the basics done. I can successfully seed the database and get nodemon up and running. And when I connect to the database using psql and c and execute the inner join query (scroll down to see query) with the tables I get rows of the results back with not error. The error is coming for the express request/response cycle as you saw in the Insomnia images above.
//app.js
app.use((req, res, next) => {
console.log('Request method: ', req.method);
console.log('Request url: ', req.url);
let currentdate = new Date();
let datetime = `Date and time request method made(format:day/month/year|hours:minutes:seconds): ${currentdate.getDate()}/${(currentdate.getMonth()+1)}/${currentdate.getFullYear()}|${currentdate.getHours()}:${currentdate.getMinutes()}:${currentdate.getSeconds()}`
console.log(datetime)
next()
});
const {getAllBirds} = require('./controllers/birds.controller.js')
const express = require('express');
const app = express();
app.use(express.json())
app.get('/api/birds',getAllBirds)
//birds.controller.js
const {selectAllBirds} = require('../models/birds.models.js')
exports.getAllBirds = (req,res) =>{
const {order} = req.query
const {sort_by} = req.query
const {diet} = req.query
selectAllBirds(sort_by,order,diet).then((birds)=> res.status(200).send({birds})).catch(err => console.log(err))
}
//bird.models.js
const db = require('../db/index.js')
exports.selectAllBirds = (sort_by,order,diet) =>{
const sortbyGreenList = ['length_cm,','weight_g','wingspan_cm']
const orderbyGreenList = ['ASC,DESC']
let queryValues = [];
let queryStr = `SELECT bird_id,common_name,species_name,wing_colour,diet,can_Fly
,length_cm,weight_g,lay_season,fun_fact,wingspan_cm,f_id FROM birds
INNER JOIN bird_families ON birds.f_id = bird_families.family_id`;
if(sort_by && !sortbyGreenList.includes(sort_by)){
return Promise.reject({status: 400, msg: 'Invalid sort_by query string'})
}
if(order && !orderbyGreenList.includes(order.toUpperCase())){
return Promise.reject({status: 400, msg: 'Invalid order query string'})
}
if(diet){
queryValues.push(`%${diet}%`)
queryStr += `WHERE diet ILIKE $1`;
}
if(sort_by){
if(!queryValues.length){
queryValues.push(sort_by)
queryStr += `ORDER BY $1`;
}else{
queryValues.push(sort_by)
queryStr += `ORDER BY $2`;
}
}
if(order){
queryStr += `${order.toUpperCase()}`;
}
return db.query(queryStr,queryValues).then((result)=>{
if(!(result.rows.length)){
return Promise.reject({status: 404, err: 'No results found for query'})
}else{
return result.rows
}
})
}
I get these errors in the terminal when I try to the GET requests above that you saw in the Insomnia images
Error with 1st query: GET request: localhost:3000/api/birds?sort_by=weight_g&diet=carnivorous
Request method: GET
Request url: /api/birds?sort_by=weight_g&diet=carnivorous
Date and time request method made(format:day/month/year|hours:minutes:seconds): 22/2/2024|11:21:1
/home/mbans8a1/Northcoders/northcoders/projects/bird_rookery_project/node_modules/pg-pool/index.js:45
Error.captureStackTrace(err);
^
error: syntax error at or near "diet"
at /home/moddy456/Programs/program/projects/bird_rookery_project/node_modules/pg-pool/index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
length: 94,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '213',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1176',
routine: 'scanner_yyerror'
}
Error with 2nd query:/api/birds?diet=piscivorous&sort_by=wingspan_cm&order=DESC
Request method: GET
Request url: /api/birds?diet=piscivorous&sort_by=wingspan_cm&order=DESC
Date and time request method made(format:day/month/year|hours:minutes:seconds): 22/2/2024|11:49:50
{ status: 400, msg: 'Invalid order query string' }
Any ideas of how I can fix these problems?
