Joins

SQL joins simplify and speed up queries. Daobab is the only library having ORM support for them.

Simple join table on column

Select.from(db, tabPizza.colName(),tabPizzaPrice.colPrice())
	.join(tabPizzaPrice,tabPizza.colPizzaId())
	.orderAscBy(tabPizza.colName())
	.result();

Join table on 'where and' conditions

Select.field(db, tabPizzaSize.colName())
            .join(tabPizza,tabPizza.colPizzaId(),AND().and(tabPizza.colName(),"CIAO ROMA"))
            .result();

Join table on many columns and conditions

Select.from(db, tabPizza.colName(),tabIngredient.colName())
	.join(tabPizza,AND().and(tabPizza.colName(),"CIAO ROMA"))
	.join(tabPizzaIngredient,tabPizza.colPizzaId(),tabIngredient.colIngredientId())
	.result();

Smart joins

This functionality builds joins automatically, basied on relations between tables into Daobab.

Example: Give me all pizzas ordered by customer having only his phone no:

Select.from(db, tabPizza)
	.where(tabCustomer.colPhone(),"888580087")
	.orderAscBy(tabPizza.colName())
	.smartJoins()
	.distinct()
	.result();
SQL: select distinct ihs1.pizza_id,ihs1.name,ihs1.description
 from pizza ihs1 
 inner join order_pizza ihs3 on ihs3.pizza_id = ihs1.pizza_id
 inner join customer ihs2 on ihs2.customer_id = ihs3.customer_id
 where  ihs2.phone = '888580087'
 order by ihs1.name asc

Join types

All join types are supported, but Inner Join is default one. Choose JoinType to change.

Select.field(db, tabPizzaSize.colName())
            .join(JoinType.RIGHT_JOIN,tabPizza,tabPizza.colPizzaId(),AND().and(tabPizza.colName(),"CIAO ROMA"))
            .result();
SQL: select ihs1.name
 from pizza_size ihs1 
 right join pizza ihs2 on ihs2.pizza_id = ihs2.pizza_id and  ( ihs2.name = 'CIAO ROMA')