JOIN Operation

There are two types of JOIN operations. INNER JOIN and OUTER JOIN. INNER JOIN is also known as just JOIN.

Assuming that table Orders and table Customers has one-to-many relationships. To INNER JOIN these tables use the following:

Cursor cursor = db.get("Orders O")
                     .join("Customers C", "C.Id = O.CustomerId")
                     .select(10, "Name = ?", "John Doe")
                     .query();

Which equates to calling:

SELECT * FROM Orders O 
INNER JOIN Customers C
  ON C.Id = O.CustomerId
WHERE Name = 'John Doe'
LIMIT 10;

To OUTER JOIN use the following:

Cursor cursor = db.get("Orders O")
                     .outerJoin("Customers C", "C.Id = O.CustomerId")
                     .select(10, "Name = ?", "John Doe")
                     .query();

Which equates to calling:

SELECT * FROM Orders O 
OUTER JOIN Customers C
  ON C.Id = O.CustomerId
WHERE Name = 'John Doe'
LIMIT 10;

#Multiple JOIN DbQuery now supports multiple joining multiple tables. For example these are the Modeling for all your tables in the database

...
modeling.add("Orders")
		.addPrimaryKey("Id")
		.add("Quantity", "INTEGER")
		.add("ProductId", "INTEGER")
		.add("CustomerId", "INTEGER");

modeling.add("Products")
		.addPrimaryKey("Id")
		.add("Name", "TEXT")
		.add("Price", "FLOAT");

modeling.add("Customers")
		.addPrimaryKey("Id")
		.add("Name", "TEXT")
		.add("Address", "TEXT");
...

You can easily joining Orders, Customers and Products:

...
String customerName = ...
String productName = ...
Cursor cursor = db.get("Orders O")
		   .join("Products P", "P.Id = O.ProductId")
		   .join("Customers C", "C.Id = O.CustomerId")
		   .select("C.Name = ? AND P.Name = ?", customerName, productName)
		   .columns("O.Id AS Id", "Quantity", "P.Name AS ProductName", "C.Name AS CustomerName")
		   .query();

// SELECT  O.Id AS Id, Quantity, P.Name AS ProductName, C.Name AS CustomerName FROM Orders O  
// INNER JOIN Products P ON P.Id = O.ProductId 
// INNER JOIN Customers C ON C.Id = O.CustomerId  
// WHERE C.Name = <customerName> AND P.Name = <productName>

And to create an IEntity called Order, map the following

public class Order implements IEntity {
    ....
    
    @Override
    public void map(Mapper mapper) {

		mapper.mapId(new Action.TypeId(this) {
			public void set(Long value) {
				setId(value);
			}
		});

		mapper.map("Quantity", new Action<Integer>(Integer.class){

			@Override
			public void set(Integer value) {
				setQuantity(value);
			}

			@Override
			public Integer get() {
				return getQuantity();
			}
		});

		mapper.map("ProductName", new Action<String>(String.class){

			@Override
			public void set(String value) {
				setProductName(value);
			}

			@Override
			public String get() {
				return getProductName();
			}
		});

		mapper.map("CustomerName", new Action<String>(String.class){

			@Override
			public void set(String value) {
				setCustomerName(value);
			}

			@Override
			public String get() {
				return getCustomerName();
			}
		});
    }
}