creating an alias with the AS operator about ktorm

[vincentlauvlwj]

Try this:

val score = match(Articles.body).against("mysql tutorial", SearchModifier.IN_BOOLEAN_MODE).cast(FloatSqlType)

val query = Articles
    .select(Articles.id, Articles.body, score)
    .having { score greater 0F }
    .orderBy(score.desc())

[schmeic]

That doesn't work, the match against sql is used in both the having and order by clauses. Like this:

having (match (articles.body) against (? in boolean mode)) > ? order by match (articles.body) against (? in boolean mode)

and there is no AS score in the SELECT part of the sql.

[schmeic]

Also, what would be the best way to include all of the columns from one table, plus something like this score calculation without having to explicitly list each column? I'd like to be able to do something like this:

select(Articles.columns, score)

[vincentlauvlwj]

Also, what would be the best way to include all of the columns from one table, plus something like this score calculation without having to explicitly list each column? I'd like to be able to do something like this:

select(Articles.columns, score)

You can use the plus operator for collections:

select(Articles.columns + score)

[vincentlauvlwj]

That doesn't work, the match against sql is used in both the having and order by clauses. Like this:

having (match (articles.body) against (? in boolean mode)) > ? order by match (articles.body) against (? in boolean mode)

and there is no AS score in the SELECT part of the sql.

Actually, the AS operator is currently not supported.

Thanks for your feedback. I'm working on it.

[vincentlauvlwj]

Ktorm 2.6 released. Now, we can assign aliases to the selected columns of a query and use them in subsequent clauses such as group by and having, just like the as keyword in SQL. Here is an example. This query selects departments whose average salary is greater than 100, then returns the average salaries along with their department’s IDs.

val deptId = Employees.departmentId.aliased("dept_id")
val salaryAvg = avg(Employees.salary).aliased("salary_avg")

Employees
    .select(deptId, salaryAvg)
    .groupBy(deptId)
    .having { salaryAvg greater 100.0 }
    .forEach { row ->
        println("${row[deptId]}:${row[salaryAvg]}")
    }

Generated SQL:

select t_employee.department_id as dept_id, avg(t_employee.salary) as salary_avg 
from t_employee 
group by dept_id 
having salary_avg > ?