时间戳

首页 > 解决方案 > knex js中的嵌套查询和unnest函数

knex.js - knex js中的嵌套查询和unnest函数

问题描述

我想重写以下查询以使用 knex 查询生成器。鉴于我正在使用嵌套查询和unnest函数，我不确定如何继续进行。

SELECT * FROM (
    SELECT DISTINCT ON (id) *
    FROM (
        SELECT unnest(other_titles) aliases, *
        FROM book
    ) b
    WHERE (b.aliases like 'Harry Potter%' OR b.book_title ILIKE 'Harry Potter%' OR b.metadata ILIKE 'Harry Potter%')
) t
ORDER BY book_title
LIMIT 50;

标签： knex.js

解决方案

https://runkit.com/embed/vefz65n26yer

const Knex = require('knex');

const knex = Knex({
  client: 'pg',
});

const inner2 = knex('book').select(knex.raw('unnest(??) aliases', ['other_titles']), '*').as('b');

const inner1 = knex(inner2).select(knex.raw('distinct on (??) *', ['id']))
  .where(builder => {
    builder.where('b.aliases', 'like', 'Harry Potter%')
           .orWhere('b.book_title', 'ilike', 'Harry Potter%')
           .orWhere('b.metadata', 'ilike', 'Harry Potter%');
  }).as('t');

knex(inner1).orderBy('book_title').limit(50).toSQL()

输出：

select * 
  from (
    select distinct on ("id") * 
    from (
      select unnest("other_titles") aliases, * 
      from "book"
    ) as "b" 
    where (
      "b"."aliases" like ? or 
      "b"."book_title" ilike ? or 
      "b"."metadata" ilike ?
    )
  ) as "t" 
  order by "book_title" asc limit ?

推荐阅读