I think I found a bug

[eiagit]

MY HTTP HEANDLE : http://127.0.0.1:3000/usuario/pesquisa/?USO_NOME=ENN&USO_CAMPO=USO_NOME

MY ROUT :

app.get("/usuario/pesquisa",async(req,res)=>{
    try {
        const params=[];
        params.push(req.query.USO_CAMPO)
        params.push(req.query.USO_NOME)  
         const results = await Execsql  `SELECT "JUSO".*,"TIU_NOME"
        ,"STA_NOME" AS "USO_STANOM"
        ,"USO_FOTO"
        From "JUSO"
        LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
        LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
        WHERE UPPER(${'"'+req.query.USO_CAMPO+'"'}) LIKE 
        ${String.fromCharCode(39)+'%'+req.query.USO_NOME+'%'+String.fromCharCode(39)}
       // ${"'%"+req.query.USO_NOME+"%'"}
        ORDER BY "USO_NOME"`
        res.status(200).json(results.USO_ID)
        console.log(results)
    } catch (err) {
        console.log(err)
        res.status(500).json({ 'error': err })
    }  

THE RESULTS IS []
but when I use a result of my script :

SELECT "JUSO".*,"TIU_NOME"
        ,"STA_NOME" AS "USO_STANOM"
        ,"USO_FOTO"
        From "JUSO"
        LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
        LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
        WHERE UPPER("USO_NOME") LIKE '%ENN%'
        ORDER BY "USO_NOME"

it returns my data, but both scripts are the same

any one can ansori this mistery ?

thank you

[porsager]

Can you try to fix the formatting so that your code becomes readable?

[porsager]

You need to change

WHERE ${'"'+req.query.USO_CAMPO+'"'} LIKE
${"'%'+req.query.USO_NOME+"%'"}

to

WHERE ${ sql(req.query.USO_CAMPO) } LIKE
${ '%' + req.query.USO_NOME +  '%' }

But be aware that this will allow users of your api to search any column.

Besides from that you should really consider using snake case for your postgres columns, eg (uso_campo) rather than USO_CAMPO. That will result in not having to use quotes everywhere.

[eiagit]

app.get("/usuario/pesquisa",async(req,res)=>{
    try {
        const results = await Execsql  `SELECT "JUSO".*,"TIU_NOME"
        ,"STA_NOME" AS "USO_STANOM"
        ,"USO_FOTO"
        From "JUSO"
        LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
        LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
        WHERE ${'"'+req.query.USO_CAMPO+'"'} LIKE 
        ${"'%"+req.query.USO_NOME+"%'"} 
        ORDER BY "USO_NOME"`
        res.status(200).json(results)
        console.log(results)
    } catch (err) {
        console.log(err)
        res.status(500).json({ 'error': err })
    }         
})

with others wrote this code, but it would not be dynamic column and data :

SELECT "JUSO".*,"TIU_NOME"
        ,"STA_NOME" AS "USO_STANOM"
        ,"USO_FOTO"
        From "JUSO"
        LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
        LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
        WHERE "USO_NOME" LIKE
        '%ENN%'
        ORDER BY "USO_NOME"

[porsager]

Compare this:

SELECT "JUSO".*,"TIU_NOME"
,"STA_NOME" AS "USO_STANOM"
,"USO_FOTO"
From "JUSO"
LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
WHERE UPPER("USO_NOME") LIKE '%ENN%'
ORDER BY "USO_NOME"

with this

select 
  juso.*, 
  tiu_nome,
  sta_nome as uso_stanom,
  uso_foto
from juso
left join jtiu on tiu_id = uso_tipo
left join jsta on sta_id = uso_status
where upper(uso_nome) like '%ENN%'
order by uso_nome

[porsager]

Ok, this should work with your current names

app.get("/usuario/pesquisa",async(req,res)=>{
    try {
        const results = await Execsql`
          SELECT "JUSO".*,"TIU_NOME"
          ,"STA_NOME" AS "USO_STANOM"
          ,"USO_FOTO"
          From "JUSO"
          LEFT JOIN "JTIU" ON "TIU_ID"="USO_TIPO"
          LEFT JOIN "JSTA" ON "STA_ID"="USO_STATUS"
          WHERE ${ sql(req.query.USO_CAMPO) } LIKE 
          ${ '%' + req.query.USO_NOME + '%' } 
          ORDER BY "USO_NOME"
        `
        res.status(200).json(results)
        console.log(results)
    } catch (err) {
        console.log(err)
        res.status(500).json({ 'error': err })
    }         
})

but this is much more manageable if you change your naming and do a little prettier formatting

app.get('/usuario/pesquisa', async (req, res) => {
  try {
    const results = await sql`
      select 
        juso.*,
        tiu_nome,
        sta_nome as uso_stanom
        uso_foto
      from juso
      left join jtiu on tiu_id = uso_tipo
      left join jsta on sta_id = uso_status
      where ${ sql(req.query.uso_campo) } like ${ '%' + req.query.uso_nome + '%' } 
      order by uso_nome
    `
    res.status(200).json(results)
    console.log(results)
  } catch (err) {
    console.log(err)
    res.status(500).json({ 'error': err })
  }         
})

[eiagit]

you are rason , the problem is with i write my code.
with your changes the result are ok.
thank you of your suport.
1000 sorryes.

[porsager]

Great to hear it's working for you now 👍 Good luck with your project

[eiagit]

this is only a test to connect any database. than you and Good luck.