JOIN clause wne using raw SQL in javalite JDBC

Question

Is it possible to use a JOIN clause when using a raw SQL ? If so, how should I map the result as there are at least 2 models involved ?

Answer 1

you can use any clause your database provides. When running a non-standard query, use method Model.findBySQL(...).

However, before using, read its documentation:

http://javalite.github.io/activejdbc/snapshot/org/javalite/activejdbc/Model.html#findBySQL-java.lang.String-java.lang.Object...-

Specifically, this:

Ensure that the query returns all columns associated with this model, so that the resulting models could hydrate themselves properly. Returned columns that are not part of this model will be ignored, but can be used for clauses like above.

In other words, whatever query you execute, the columns it returns should match that of this model. Technically speaking, your query can span multiple tables, views, even execute custom functions, but the resultset needs to have the columns that match this model. Why is that? Because your model is 'O' in the ORM. In other words, it represents a single record from a table as an object.

If your results are a mix of more than one model, or a partial model, or anything in between, do not use a model API. Use Base:

List<Map> results = Base.findAll("complex custom query");

http://javalite.github.io/activejdbc/snapshot/org/javalite/activejdbc/Base.html#findAll-java.lang.String-

Look at variations of find(...) and findAll(...) methods