How to select a postgreSQL row that contains a specific word

Trying to build a query for a postgreSQl DB based on a keyword. LIKE doesn’t work as it matches any row that contains any of the letters. For Example:

SELECT * FROM table WHERE column ilike ‘%jeep%’;

This returns any row that a j,e or p in the column (and the same row multiple times for some reason). Not the word ‘jeep’.

Below is my query structure. Using Knex and queuing multiple tables:

searchAllBoardPosts(db, term) {
        return db
            .select('*')
            .from({
                a: 'messageboard_posts',
                b: 'rentals',
                c: 'market_place',
                d: 'jobs'
            })
            .where('a.title', 'ilike', `%${term}%`)
            .orWhere('b.title', 'ilike', `%${term}%`)
            .orWhere('c.title', 'ilike', `%${term}%`)
            .orWhere('d.title', 'ilike', `%${term}%`);
    },

Thanks in advance!

UPDATE: Here is the SQL output:

select * 
from "messageboard_posts" as "a", 
"rentals" as "b",
"market_place" as "c", 
"jobs" as "d" 
where "a"."title" ilike '%jeep%'
or "b"."title" ilike '%jeep%' 
or "c"."title" ilike '%jeep%' 
or "d"."title" ilike '%jeep%'

Answer

This query is a cross join

(But the Knex syntax masks that, a little).

This returns any row that a j,e or p in the column (and the same row multiple times for some reason).

It’s not returning the same row multiple times. It’s returning everything from each table named in a CROSS JOIN. This is the behaviour of Postgres when more than one table is named in the FROM clause (see: docs). This:

db
  .select('*')
  .from({
    a: 'table_one',
    b: 'table_two'
  })

will return the entire row from each of the named tables every time you get an ILIKE match. So at minimum you’ll always get an object consisting of two rows joined (or however many you name in the FROM clause).

The tricky part is, Knex column names have to map to JavaScript objects. This means that if there are two column results named, say, id or title, the last one will overwrite the first one in the resulting object.

Let’s illustrate (with wombats)

Here’s a migration and seed, just to make it clearer:

table_one

exports.up = knex =>
  knex.schema.createTable("table_one", t => {
    t.increments("id");
    t.string("title");
  });

exports.down = knex => knex.schema.dropTable("table_one");

table_two

exports.up = knex =>
  knex.schema.createTable("table_two", t => {
    t.increments("id");
    t.string("title");
  });

exports.down = knex => knex.schema.dropTable("table_two");

Seed

exports.seed = knex =>
    knex("table_one")
      .del()
      .then(() => knex("table_two").del())
      .then(() =>
        knex("table_one").insert([
          { title: "WILLMATCHwombatblahblahblah" },
          { title: "WILLMATCHWOMBAT" }
        ])
      )
      .then(() =>
        knex("table_two").insert([
          { title: "NEVERMATCHwwwwwww" },
          { title: "wombatWILLMATCH" }
        ])
      )
  );

Query

This allows us to play around a bit with ILIKE matching. Now we need to make the column names really explicit:

  return db
    .select([
      "a.id as a.id",
      "a.title as a.title",
      "b.id as b.id",
      "b.title as b.title"
    ])
    .from({
      a: "table_one",
      b: "table_two"
    })
    .where("a.title", "ilike", `%${term}%`)
    .orWhere("b.title", "ilike", `%${term}%`);

This produces:

[
  {
    'a.id': 1,
    'a.title': 'WILLMATCHwombatblahblahblah',
    'b.id': 1,
    'b.title': 'NEVERMATCHwwwwwww'
  },
  {
    'a.id': 1,
    'a.title': 'WILLMATCHwombatblahblahblah',
    'b.id': 2,
    'b.title': 'wombatWILLMATCH'
  },
  {
    'a.id': 2,
    'a.title': 'WILLMATCHWOMBAT',
    'b.id': 1,
    'b.title': 'NEVERMATCHwwwwwww'
  },
  {
    'a.id': 2,
    'a.title': 'WILLMATCHWOMBAT',
    'b.id': 2,
    'b.title': 'wombatWILLMATCH'
  }
]

As you can see, it’s cross-joining both tables, but I suspect you were only seeing results that appeared not to match (because the match was in the other table, and the title column name was a duplicate).

So, what should the query be?

I think your (or Ry’s) plan to use UNION was correct, but it’s probably worth using UNION ALL to avoid unnecessary removal of duplicates. Something like this:

  return db
    .unionAll([
      db("market_place")
        .select(db.raw("*, 'marketplace' as type"))
        .where("title", "ilike", `%${term}%`),
      db("messageboard_posts")
        .select(db.raw("*, 'post' as type"))
        .where("title", "ilike", `%${term}%`),
      db("rentals")
        .select(db.raw("*, 'rental' as type"))
        .where("title", "ilike", `%${term}%`),
      db("jobs")
        .select(db.raw("*, 'job' as type"))
        .where("title", "ilike", `%${term}%`)
    ]);

A similar query against our test data produces the result set:

[
  { id: 1, title: 'WILLMATCHwombatblahblahblah', type: 'table_one' },
  { id: 2, title: 'WILLMATCHWOMBAT', type: 'table_one' },
  { id: 2, title: 'wombatWILLMATCH', type: 'table_two' }
]