I have made a full stack app. I am trying to see if I could make a search component to search for books by their author, title and published date.

I have these query functions that are fully working.

 ` const getBooksByAuthor = async (author) => {
    const result = await query(`SELECT * FROM books WHERE author ILIKE $1`, [`%${author}%`])
    console.log(" I am the result from model", result.rows[0].author)
    return result.rows
  }

  const getBooksByTitle = async (title) => {
    const result = await query(`SELECT * FROM books WHERE title ILIKE $1`, [`%${title}%`])
    console.log(" I am the result from model", result.rows[0].author)
    return result.rows
  }

  const getBooksByDate = async (dates) => {
    const result = await query(`SELECT * FROM books WHERE date_published = $1`, [`${dates}`])
    console.log(" I am the result from model", result.rows[0].author)
    return result.rows
  }`

I have these routes that are also fully working

 router.get("/", async(req, res)=>{
    const author = req.query.author
    const title = req.query.title
    const dates = req.query.date
    try {
        if (req.query.author!== undefined) {
            const result = await getBooksByAuthor(req.query.author);
            console.log(`this is books by author ${req.query.author}`);
            return res.status(200).json({ success: true, payload: result });
        } else if (req.query.title !== undefined){
            const result = await getBooksByTitle(req.query.title);
            console.log(`this is books by title ${req.query.title}`);
            return res.status(200).json({ success: true, payload: result });
        } else if(  req.query.dates !==undefined){
            const result = await getBooksByDate(req.query.dates);
            console.log(`this is books by dates ${req.query.dates}`);
            return res.status(200).json({ success: true, payload: result });
        }
        
        else{
     
        const result = await getAllBooks();
        const data = result.rows
    
        res.json({success: true, payload: data });
    }
    } catch (err) {
        console.error(err.message);
    }
})

So my question is that when I do my fetch on the front end, could I make only one fetch that will conditionally search for the book based on author, title or date so that I could put author, title or date in some input field and could click search and it would display the books depending on the search criteria. Or, would I need to create three different fetch requests for three different searches. I know how to make three different fetch requests but was just wondering if I could do something to display required books by author, title or date by only writing only one fetch request in some smarter way

Maybe something like this? Any ideas, or someone could direct me on the right path or give the actual code? Thanks!

router.get("/", async(req, res)=>{

try {
    if (req.query.search!== undefined) {
        const result = await getBooksByAuthor(req.query.search);
        console.log(`this is books by author ${req.query.search}`);
        return res.status(200).json({ success: true, payload: result });
    } else if (req.query.search !== undefined){
        const result = await getBooksByTitle(req.query.search);
        console.log(`this is books by title ${req.query.search}`);
        return res.status(200).json({ success: true, payload: result });
    } else if(  req.query.search !==undefined){
        const result = await getBooksByDate(req.query.search);
        console.log(`this is books by dates ${req.query.search}`);
        return res.status(200).json({ success: true, payload: result });
    }
    
    else{
 
    const result = await getAllBooks();
    const data = result.rows

    res.json({success: true, payload: data });
}
} catch (err) {
    console.error(err.message);
}

})

I tried this the latter but the search stops as soon as the first if expression executes. 

CodePudding user response：

Is it compulsory to have separate queries?

You could do just one getBooks query where searchQuery ilike searchQuery since it's just 1 table you're working with.

So,

SELECT * FROM books WHERE author ilike searchQuery OR title ilike searchQuery ...;

This way, whatever the query body entails will search through the author, title and date columns for possible matches

CodePudding user response：

What you need is to get all books of certain author, then display the books on both title and date fields.

const [books, setbooks] = useState([]);
// fetch the books by author and set the books array
// then use this array to fetch by title and date from the frontend, you don't need to request it from the backend.
const [book, setBook] = (values=>({...values, ['author']: '', ['title']: '', ['date']: ''}));

const onAuthorChange = (value)=>{
    // call the fetch function to get books by author from backend.
}
const onTitleChange = (value)=>{
   // value passed should be book unique id
   // then fetch this book from books array and set the data.
   books.map(bo => 
       {
          if(bo.id === value){
            setBook(values=>({...values, ['author']: 'bo.author', ['title']: 
           'bo.title', ['date']: 'bo.date'}));
          }
       });
}
const onDateChange = (value)=>{
   // value passed should be book unique id
   // then fetch this book from books array and set the data.
   books.map(bo => 
       {
          if(bo.id === value){
            setBook(values=>({...values, ['author']: 'bo.author', ['title']: 
           'bo.title', ['date']: 'bo.date'}));
          }
       });
   }

// in your return function
return (
     // to reduse the code I am not writing all select tags
     // for author you need input field
     <input type="text" onChange={onAuthorChange} value={author}/>
     // in your selects tags for titles and date you should map to option 
     <select onChange={onTitleChange}>
        {books.map(bo => <option value={bo.id}>{bo.title}</option>}
     </select>
     // do same for date.
)