Search code examples
joinjdbcself-joinapache-metamodel

How to represent Self join in Apache Metamodel using JdbcContext?

Consider I have an employee table which has the following fields id, name, email, phone, joining_date, manager, department. Now in this table manager field can be used to create a self join and arrive at a result set which says employee e1 reports to manager m1. How do I represent this using JdbcDataContext and the Query POJO? Any pointers here will be much helpful.

Solution

  • You do self-joins just like any other join in MetaModel. Here's an example:

    DataContext dc = ...
Table t = dc.getDefaultSchema().getTableByName("employees");
FromItem left = new FromItem(t).setAlias("e1");
FromItem right = new FromItem(t).setAlias("e2");
SelectItem id = new SelectItem(t.getColumnByName("id"), left);
SelectItem name = new SelectItem(t.getColumnByName("name"), left);
SelectItem manager = new SelectItem(t.getColumnByName("id"), right);
SelectItem managerName = new SelectItem(t.getColumnByName("name"), right);
DataSet ds = dc.query().from(left).innerJoin(right).on(id, manager).select(name, managerName).execute()