Issue with Dynamic Columns in Queries
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 8.7k
- Forks
- 374
- Avg merge
- 11d 16h
- Merged PRs (30d)
- 1
Description
I'm experiencing an issue with dynamic columns in queries when using postgres.js. I have a structure where functions dynamically build the query, as shown below:
function sendConsult(query) {
return dbpg`
SELECT COUNT(_id)
FROM positions
WHERE status = 'active'
AND company_id = ${companyId}
${addFilters('position', companyId, query)}
`;
}
const addFilters = (source, companyId, query) => {
if (source === 'position') {
const date = {
from: moment(query.from).format('YYYY-MM-DD'),
to: moment(query.to).format('YYYY-MM-DD 23:59:59')
}
const columns = { one: 'positions."archivedAt"', two: 'positions."releasedAt"' };
return dbpg`${filterDateBy(columns, '$case', date)}`;
}
};
const filterDateBy = (field, operator, date) => {
const { from, to } = date;
const clause = {
'$case': from && to ? dbpg`AND CASE
WHEN positions.status = 'closed'
THEN DATE_TRUNC('day', ${field.one}) > ${from}
AND DATE_TRUNC('day', ${field.two}) <= ${to}
ELSE DATE_TRUNC('day', ${field.two}) <= ${to}
END` : dbpg``,
};
return clause[operator];
};
When executing the query, I get an error stating that the dynamically passed columns (field.one or field.two) do not exist. However, if I print the generated query (without using the dbpg instance) and execute it directly in DBeaver, it works as expected.
Question:
Is there a native way in postgres.js to handle dynamic columns so the query works correctly? If not, are there any recommendations to address this limitation?
Thank you in advance for the support!
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by examining how the postgres.js tagged template handles interpolations in dbpg${...}, then trace the filterDateBy call using field.one and field.two. Compare the handling of dynamic values with dynamic column references and determine whether the reported behavior is expected; done means documenting the supported approach or confirming a focused fix is needed.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, postgresql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100