两个工作查询的Jooq组合因FROM子句错误而失败 [英] Jooq combination of two working queries failing with FROM clause error

问题描述

我有两个分别工作的查询，但是当我尝试将它们组合在一起时，我收到一条错误消息：错误：表 A的FROM子句条目缺失。查询的目的是查看组合的两个表的结果，这些表的JSONB列的格式为[ tag1， tag2， tag3]，列名称为TAGS。两个查询都可以工作，但是当我尝试将它们组合时，我得到了错误。但是我尝试了几种方法都无济于事，如何解决这个问题的任何想法？结果应该是一个唯一的标记列表，这些标记将查询与末尾的通配符相匹配，下面是查询及其组合。我正在使用PostgreSQL 9.5.3

I have two queries that are working individually but when I attempt to combine them I am getting an error saying: "ERROR: missing FROM-clause entry for Table "A". The purpose of the queries is to look at the combined results of two tables which have a JSONB column in the format ["tag1","tag2","tag3"] with column name TAGS. Individually both queries work but when I attempt to combine them I get the error. I must be missing something but I've tried several approaches to no avail. Any idea how to resolve this issue? The result should be a distinct list of tags that match the query with a wildcard at the end. Below are the queries and the combination of them. I am using PostgreSQL 9.5.3

    final Field<String> value = field(name("A", "value"), String.class);

    final Table<Record1<String>> c1 = sql.dsl()
            .selectDistinct(value)
            .from(CAMPAIGN,lateral(table("jsonb_array_elements_text({0})", CAMPAIGN.TAGS)).as("A"))
            .where(CAMPAIGN.STORE_KEY.equal(campaign.getStoreKey()))
            .and(CAMPAIGN.CAMPAIGN_KEY.notEqual(campaignKey))
            .and(value.like(search + "%%"))
            .asTable("c1");

    final Table<Record1<String>> c2 = sql.dsl()
            .selectDistinct(value)
            .from(STOREFRONT, lateral(table("jsonb_array_elements_text({0})", STOREFRONT.TAGS)).as("A"))
            .where(STOREFRONT.STORE_KEY.equal(campaign.getStoreKey()))
            .and(value.like(search + "%%"))
            .asTable("c2");

    final Result<Record1<String>> result = sql.dsl()
            .selectDistinct(value)
            .from(c1, c2)
            .limit(30)
            .fetch();

推荐答案

您需要使用联盟。尝试以下操作（没有可测试jooq的工作空间，但类似的东西应该可以工作）：

You need to use a Union. Try this (don't have a workspace with jooq to test, but something like this should work):

final Field<String> value = field(name("A", "value"), String.class);

final Select<Record1<String>> c1 = sql.dsl()
        .selectDistinct(value)
        .from(CAMPAIGN,lateral(table("jsonb_array_elements_text({0})", CAMPAIGN.TAGS)).as("A"))
        .where(CAMPAIGN.STORE_KEY.equal(campaign.getStoreKey()))
        .and(CAMPAIGN.CAMPAIGN_KEY.notEqual(campaignKey))
        .and(value.like(search + "%%"));

final Select<Record1<String>> c2 = sql.dsl()
        .selectDistinct(value)
        .from(STOREFRONT, lateral(table("jsonb_array_elements_text({0})", STOREFRONT.TAGS)).as("A"))
        .where(STOREFRONT.STORE_KEY.equal(campaign.getStoreKey()))
        .and(value.like(search + "%%"));

final Result<Record1<String>> result = sql.dsl()
        .selectFrom(c1.asTable())
        .union(c2)
        .limit(30)
        .fetch();

这篇关于两个工作查询的Jooq组合因FROM子句错误而失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！