1

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 ?

belgoros
  • 3,590
  • 7
  • 38
  • 76

1 Answers1

0

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

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • Igor, thank you for the response. So after using `Base.findAll()` I'll have to get every corresponding column value like that (example for the first element in the results list): `results.get(0).get("RESULT_SET_COLUMN_NAME")` ? – belgoros Dec 08 '17 at 08:16
  • that is correct. Every map will correspond to a row from your ResultSet. – ipolevoy Dec 08 '17 at 14:33