CODEWITHSUNDEEP
sqlpostgresqlgo
sdeLevelNegativeTwo
sdeLevelNegativeTwo

Reputation: 23

Go language single database query to address many parameters

I am building a Rest API, in the format of query/{occupation}/{location}

Now, in the .go file, I have

position := params["occupation"]
loc := params["location"]
rows, err := db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1 AND location=$2`,
                       position, loc);

This works great if we call my API with both occupation and location exist. I want it to be more flexible so I can ignore some parameters, for example, I want to call abc.com/query/dentist/NULL.

Then I could have this code:

if loc == nil && position == nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable);
} else if loc == nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1`, position);
} else if position = nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable WHERE location=$1`, loc);
} else {
    rows, err := db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1 AND location=$2`,
                       position, loc);
}

But this is not sustainable since my RestAPI may support 10 parameters and the if else clauses would grow to 2^10.

I am wonder just using one query to address all cases.

This maybe a simple SQL question, I just cannot figure it out (I'm using postgres).

Upvotes: 1

Views: 177

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

To ignore a parameter pass it as null and change the query to :

select occupation, location
from mytable
where
    (occupation = $1 or $1 is null)
    and
    (location = $2 or $2 is null)

This will scale linearly with the number of parameters.

Upvotes: 0

Aret
Aret

Reputation: 475

Maybe you should use some ORM for better design.

This should help: https://github.com/jinzhu/gorm#query-with-where-struct--map

Or you can compile the where condition by concatenating SQL and adding bind parameters to query. But watch out for SQL injection.

Upvotes: 0

OneOfOne
OneOfOne

Reputation: 99234

It's really a basic variable checking, you don't need to do all 3 queries, for example :

var (
    rows     *sql.Rows
    err      error
    position = params["occupation"]
    loc      = params["location"]
)
switch {
case loc != "" && position != "":
    rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE occupation=$1 AND location=$2'`, position, loc)
case loc != "":
    rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE location=$1`, loc)
case position != "":
    rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE occupation=$1`, position)
default:
    rows, err = db.Query(`SELECT occupation, location FROM myTable`)
}
if err != nil {
    panic(err)
}

Upvotes: 1

Related Questions