如何选择包含特定单词的 postgreSQL 行

Question

尝试基于关键字为 postgreSQl 数据库构建查询。LIKE 不起作用，因为它匹配包含任何字母的任何行。例如：

SELECT * FROM table WHERE column ilike '%jeep%';

这将返回列中包含 aj、e 或 p 的任何行（并且由于某种原因多次返回同一行）。不是“吉普车”这个词。

下面是我的查询结构。使用 Knex 并排队多个表：

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}%`);

    },

提前致谢！

更新：这是 SQL 输出：

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 1

这个查询是一个交叉连接（但 Knex 语法稍微掩盖了这一点）。这将返回列中包含 aj、e 或 p 的任何行（并且由于某种原因多次返回同一行）。它不会多次返回同一行。它从以CROSS JOIN. 这是 Postgres 在FROM子句中命名多个表时的行为（请参阅：docs）。这个：db  .select('*')  .from({    a: 'table_one',    b: 'table_two'  })每次获得匹配项时，都会从每个命名表中返回整行。所以至少你会得到一个由两行组成的对象（或者你在子句中命名的多行）。ILIKEFROM棘手的部分是，Knex 列名称必须映射到 JavaScript 对象。这意味着如果有两个名为idor 的列结果，title最后一个将覆盖结果对象中的第一个。让我们来说明（用袋熊）这是一个迁移和种子，只是为了让它更清楚：table_oneexports.up = knex =>  knex.schema.createTable("table_one", t => {    t.increments("id");    t.string("title");  });exports.down = knex => knex.schema.dropTable("table_one");表二exports.up = knex =>  knex.schema.createTable("table_two", t => {    t.increments("id");    t.string("title");  });exports.down = knex => knex.schema.dropTable("table_two");种子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" }        ])      )  );询问这允许我们玩一些ILIKE匹配。现在我们需要使列名真正明确：  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}%`);这产生：[  {    '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'  }]正如您所看到的，它交叉连接了两个表，但我怀疑您只看到了似乎不匹配的结果（因为匹配在另一个表中，并且title列名是重复的）。那么，查询应该是什么？我认为您（或 Ry 的）使用计划UNION是正确的，但可能值得使用UNION ALL以避免不必要地删除重复项。像这样的东西：  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}%`)    ]);针对我们的测试数据的类似查询会生成结果集：[  { id: 1, title: 'WILLMATCHwombatblahblahblah', type: 'table_one' },  { id: 2, title: 'WILLMATCHWOMBAT', type: 'table_one' },  { id: 2, title: 'wombatWILLMATCH', type: 'table_two' }]

Answer 2

Using.union有效并返回正确的值，但是使用查询中第一个表中的键标记。最后只做了四个单独的查询，但希望这可以帮助其他人！searchAllBoardPosts(db, term) {        return db            .union([db                    .select('id', 'market_place_cat')                    .from('market_place')                    .where('title', 'ilike', `%${term}%`)            ])            .union([db                    .select('id', 'board_id')                    .from('messageboard_posts')                    .where('title', 'ilike', `%${term}%`)            ])            .union([db                    .select('id', 'rental_cat')                    .from('rentals')                    .where('title', 'ilike', `%${term}%`)            ])            .union([db                    .select('id', 'job_cat')                    .from('jobs')                    .where('title', 'ilike', `%${term}%`)            ]);    },

Answer 3

这个表达： WHERE column ilike 'jeep'只匹配值为 的行lower(column) = 'jeep'，例如：吉普车吉普车吉普车它不匹配任何其他表达式。如果使用通配符： WHERE column ilike '%jeep%'然后是'jeep'在lower(column). 它不是逐字符搜索。为此，您将使用正则表达式和字符类：WHERE column ~* '[jep]'如果要在字段中查找单词，通常会使用正则表达式，而不是like/ ilike。