porsager / porsager/postgres

Issue with Dynamic Columns in Queries

Open
#1,011 0 comments 1 reaction 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.