Question 300528 in PostgreSQL Question posted in
The official documentation can be found here.

Use one combined property in knex to query two separate columns in PostgreSQL

I want to query items from Postgres database with knex. In the database, I have two separate columns first_name and last_name, but I need to compare them both to a single value which is formatted "last_name, first_name".

How can I combine those two fields in a .where clause?

For example:

const res = await Database.knex
.select()
.from(`${DB_SCHEMA}.t_contacts as contacts`)
.where({
   'contacts.email': email
})
.andWhere({
   'contacts.status': 'ACTIVE'
});
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The easiest solution was to split the given value by ", " and then have a list of firstNames and lastNames, and then have two where queries for two fields.

    const firstNames = search.names.map((name) => name.split(', ')[1]);
const lastNames = search.names.map((name) => name.split(', ')[0]);

const res = await Database.knex
.select()
.from(`${DB_SCHEMA}.t_contacts as contacts`)
.whereIn(
   'contacts.first_name', firstNames
)
.whereIn(
   'contacts.last_name', lastNames
);

  2. 0

    You can use whereRaw() to combine the columns before comparing to your value:

    const res = await Database.knex
.select()
.from(`${DB_SCHEMA}.t_contacts as contacts`)
.whereRaw("concat(first_name,', ',last_name) = ?", [your_combined_value]);

    You could also split your value and compare its parts to their corresponding columns. You can even let PostgreSQL handle the split

    const res = await Database.knex
.select()
.from(`${DB_SCHEMA}.t_contacts as contacts`)
.whereRaw("first_name = split_part(? ,', ',1)", [your_combined_value]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search