在Slick生成的查询中删除别名 [英] Remove alias in Slick generated queries

问题描述

我在应用程序中使用Slick进行数据库操作（使用Postgres作为数据库）。我在尝试建立通用查询时遇到一些问题。
我曾在SO中问过一个有关使用表名而不是别名的问题（此处），但发现不可能。

I am using Slick for database operations in my application (using Postgres as the database). I am facing some issues while trying to build generalized queries. I have asked a question in SO for using the table name instead of alias (here), but found out that its no possible.

所以我现在正尝试在光滑的一侧修复该问题。

So I am now trying to fix the issue in the Slick side.

def ++= (selectStatement:String) ={
    this.customSelectStatement = Some(selectStatement + " E where " +  getQuery(filterPropertyList))
  }

filterQuery ++= (TableQuery[SkillTable] innerJoin TableQuery[SkillCategoryTable] on (_.skillCategoryId === _.id)).sortBy(_._1.id).map{ case (a,at) => (a.id,a.name,at.name)}.selectStatement
    val result = filter[SkillContainer](filterQuery)

方法 getQuery 根据接收到的属性构建SQL条件查询。我正在使用Slick表达式构建select子句，然后将其转换为SQL查询，然后附加通过 getQuery 构建的where条件。

The method getQuery build a SQL where condition query based on the properties it receives. I am building the select clause using the Slick expressions and then converting it to SQL query and then appending the where condition built through getQuery.

运行代码时，查询如下生成：

When I run the code, the query is getting generated as below:

SELECT x2.x3,
       x2.x4,
       x5.x6
FROM (
  SELECT x7."SkillId" AS x3,
         x7."Name" AS x4,
         x7."Description" AS x8,
         x7."IsRemoved" AS x9,
         x7."SkillCategoryId" AS x10
  FROM "base"."Skill" x7
) x2
  INNER JOIN (
    SELECT x11."SkillCategoryId" AS x12,
           x11."Name" AS x6,
           x11."Description" AS x13,
           x11."IsRemoved" AS x14
    FROM "base"."SkillCategory" x11
  ) x5 ON x2.x10 = x5.x12
WHERE base."Skill"."IsRemoved" = 'false' 
LIMIT 10 offset 0

但上述查询将失败，因为 base。 Skill。 IsRemoved 无法解析，因为我使用了Slick生成的别名查询。但是，如果我将其更改为 x2.x9 ='false'，则查询将成功执行。

But the above query will fail since base."Skill"."IsRemoved" will not be resolved as I Slick generates the query with aliases. However, if I change it to x2.x9 = 'false', then the query executes successfully.

where条件查询是通过 getQuery 方法构建的，它不知道要使用的别名。
有什么方法可以要求Slick不要为列生成别名，还可以在表达式本身中提供子查询别名（ x2 ），这样我就可以控制子查询的别名。？

The where condition query is built by getQuery method, and it doesn't know the aliases to be used. Is there any way in which I can ask Slick not to generate aliases for columns and also provide the sub query alias(x2) in the expression itself, so that I will have control over the alias of the sub query.?

推荐答案

maybefilter可能允许您使用类型安全的Slick api来实现此目的连接SQL字符串。 https://gist.github.com/cvogt/9193220

The "maybefilter" probably allows you to do this with the type-safe Slick api without concatenating SQL strings. https://gist.github.com/cvogt/9193220

除此之外，在可能的情况下删除别名将是对查询编译器的一个很好的改进，尽管我在过去有时也这样做。请添加票证： https://github.com/slick/slick/issues

Other than that, removing the aliases, when possible would be a nice improvement of the query compiler, which I have though of in the past sometimes, too. Please add a ticket: https://github.com/slick/slick/issues

这篇关于在Slick生成的查询中删除别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！