Introducció

En molts casos només es necessita consultar la informació en un sola taula, sobretot si l'esquema no està normalitzat, però en altres casos la informació necessària està en més d'una taula.

TODO juntar amb query

Joining Functions

Ktorm supports joining queries by some extension functions, there are four built-in join types provided in the core module:

| Join Type | Extension Function Name | Corresponding SQL Key Word | | inner join | innerJoin | inner join | | left join | leftJoin | left join | | right join | rightJoin | right join | | cross join | crossJoin | cross join |

The functions above are all extensions of QuerySource, a simple usage is given as follows:

val joining = database.from(Employees).crossJoin(Departments)

Here, the function from wraps a table object as a QuerySource instance, then crossJoin cross joins the instance to another table and returns a new QuerySource as the result.

For most of the time, it’s useless for us to hold a QuerySource instance, we need a Query object instead to perform a query and obtain our results.

Remember how to create a Query from a QuerySource? Yes, we just need to call select:

val query = database.from(Employees).crossJoin(Departments).select()

This query cross joins the Employees table to the Departments table and returns all records of the joining (cartesian product).

Generated SQL:

select * 
from t_employee 
cross join t_department

That’s so simple, but honestly, such a simple joining query doesn’t make any sense to us in practical use.

Here is a more practical example, we want to list those employees whose salary is greater than 100, and return their names and the departments they are from.

Here, we specify the second parameter on of the function leftJoin, that’s the joining condition.

val query = database
    .from(Employees)
    .leftJoin(Departments, on = Employees.departmentId eq Departments.id)
    .select(Employees.name, Departments.name)
    .where { Employees.salary gt 100L }

Generated SQL:

select t_employee.name as t_employee_name, t_department.name as t_department_name 
from t_employee 
left join t_department on t_employee.department_id = t_department.id 
where t_employee.salary > ?