How do I fix a PostgreSQL order query (ASC/DESC) error (for complex queries) once I have seeded the database with data?

Question

I am trying to do 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 and order appropriately. Order is a problem now See the Insomnia images below:

1st query: GET request: localhost:3000/api/birds?diet=piscivorous&sort_by=weight_g

This works fine

But this query does not work:(the request hangs and I get error timeout) error with this query: localhost:3000/api/birds?diet=piscivorous&sort_by=weight_g&order=desc

Also the terminal shows:

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 && !queryValues.length){
        queryValues.push(order.toUpperCase())
        queryStr += ` ORDER BY $1`;
    }
    if(order && !queryValues.length === 2){
        queryValues.push(order.toUpperCase())
        queryStr += ` $3`;
    }

    if(order && !queryValues.length === 1){
        queryValues.push(order.toUpperCase())
        queryStr += ` $2`;
    }

    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?

Some more information

//relations(tables) involved

function createBirds_rel(){ //birds table
    return db.query(`
    CREATE TABLE birds (
        bird_id SERIAL PRIMARY KEY,
        common_name VARCHAR(60) UNIQUE NOT NULL,
        species_name VARCHAR(70) UNIQUE NOT NULL,
        wing_colour VARCHAR(60) NOT NULL,
        diet VARCHAR(60),
        can_Fly BOOLEAN NOT NULL,
        length_cm INT,
        weight_g INT,
        lay_season VARCHAR(50),
        fun_fact text,
        wingspan_cm INT,
        f_id INT REFERENCES bird_families(family_id)
    )
    `)
}

function createBirdFamilies_rel(){//bird_families table
    return db.query(`
    CREATE TABLE bird_families (
        family_id SERIAL PRIMARY KEY,
        scientific_fam_name VARCHAR(55) UNIQUE NOT NULL,
        f_description TEXT,
        clutch_size INT, 
        no_of_genera INT,
        no_of_species INT,
        o_id INT REFERENCES bird_orders(order_id)
    )
    `)
}

//Structure of  example birds data that is seeded to database

const bird_data = [{
    common_name: "Yellow-legged tinamou",
    species_name: "Crypturellus noctivagus",
    wing_colour: "brownish-grey",
    diet: "granivorous/frugivorous/insectivorous",
    can_Fly: "TRUE",
    length_cm: 29,
    weight_g: 150,
    lay_season: "spring",
    fun_fact:"This bird is widespread throughout Brazil and is found in wooded and shrubby habitats.",
    wingspan_cm: 31,
    family: "Tinamidae"
  }]

Query below does work but the results do not seem ordered in ascending length localhost:3000/api/birds?diet=piscivorous&sort_by=length_cm&order=asc