I'm just starting with Node.js, PostgresSQL and pg-promise and in my API's get endpoint I check what parameters are present so in cascade if city, region, country are present I call 'get-city-products' query, if region, country are present I call 'get-region-products', and if country is present I call 'get-country-products'.
When I pass only region, country or country it calls 'get-city-products', which not having city or city, region fails and return no products..
Why the else if (city, region, country) succeeds when missing city parameter? Can you spot what is wrong with my business logic?
Here's the inherent code:
// city
else if (city, region, country) {
await db.any({
name: 'get-city-products',
text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3',
values: [city, region, country]
})
.then(result => {
console.log('get-city-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-city-products error:', error);
});
}
// region => never gets called
else if (region, country) {
await db.any({
name: 'get-region-products',
text: 'SELECT * FROM products WHERE region = $1 AND country = $2',
values: [region, country]
})
.then(result => {
console.log('get-region-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-region-products error:', error);
});
}
// country => never gets called
else if (country) {
await db.any({
name: 'get-country-products',
text: 'SELECT * FROM products WHERE country = $1',
values: [country]
})
.then(result => {
console.log('get-country-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-country-products error:', error);
});
}
And here is the complete method:
exports.findProducts = async (req, res) => {
// customers queries
// sorted
if (city, region, country, category, minPrice, maxPrice, orderedBy, sorted) {
/// this will create a unique PreparedStatement name for each query type
const name = 'getCityCategoryPriceRangeOrderedBy' + orderedBy + '' + sorted + ' Products';
const text = pgp.as.format(`SELECT * FROM products WHERE city = $/city/
AND region = $/region/ AND country = $/country/
AND category = $/category/
AND price BETWEEN $/minPrice/ AND $/maxPrice/
ORDER BY $/orderedBy:name/ $/sorted:value/`, {
city, region, country, category,
minPrice, maxPrice, orderedBy, sorted
});
console.log('query for ' + name + ' is: ', text,);
await db.any({ name: name, text: text })
.then(result => {
console.log(name + ' results: ', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log(name + ` error:`, error);
});
}
// price range
else if (city, region, country, category, minPrice, maxPrice) {
await db.any({
name: 'get-city-category-price-range-products',
text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4 AND price BETWEEN $5 AND $6',
values: [city, region, country, category, minPrice, maxPrice]
})
.then(result => {
console.log('get-city-category-price-range-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-city-category-price-range-products error:', error);
});
}
// city category
else if (city, region, country, category) {
await db.any({
name: 'get-city-category-products',
text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4',
values: [city, region, country, category]
})
.then(result => {
console.log('get-city-category-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-city-category-products error:', error);
});
}
// city
else if (city, region, country) {
await db.any({
name: 'get-city-products',
text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3',
values: [city, region, country]
})
.then(result => {
console.log('get-city-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-city-products error:', error);
});
}
// region => never gets called
else if (region, country) {
await db.any({
name: 'get-region-products',
text: 'SELECT * FROM products WHERE region = $1 AND country = $2',
values: [region, country]
})
.then(result => {
console.log('get-region-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-region-products error:', error);
});
}
// country => never gets called
else if (country) {
await db.any({
name: 'get-country-products',
text: 'SELECT * FROM products WHERE country = $1',
values: [country]
})
.then(result => {
console.log('get-country-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-country-products error:', error);
});
}
// shops queries
else if (vendor) {
await db.any({
name: 'get-vendor-products',
text: 'SELECT * FROM products WHERE vendor = $1',
values: [vendor]
})
.then(result => {
console.log('get-vendor-products:', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found.'
});
}
})
.catch(function (error) {
console.log('get-vendor-products error:', error);
});
}
else {
db.any({
name: 'get-all-products',
text: 'SELECT * FROM products ORDER BY id ASC',
})
.then(result => {
console.log('get-all-products', result);
if (result.length > 0) {
res.status(200).send({
data: result
});
}
else {
res.status(404).send({
error: 'No product found'
});
}
})
.catch(function (error) {
console.log('get-cityProducts error:', error);
});
}
};
Should I just use 1 parameter per query instead?
