0

I want to select from multiple tables and convert the result to json using ActiveJDBC

http://javalite.io/record_selection

I can do the following but it will of course return only the columns in the model book, I don't see the columns from the authors model. How would it be possible to achieve that?

LazyList<Book> book = Book.findBySQL("select books.*, authors.* from books, authors where books.author_id = author.id");
jsonOutput = book.toJson(true);

1 Answers1

2

First, you clearly have a one-to-many association, and as a result you do not need to use findBySQL(). This method is used in cases when the framework cannot help, but your case is standard. According to this page: http://javalite.io/one_to_many_associations as well as this one: http://javalite.io/generation_of_json you need to write something like this: 

LazyList<Author> authors = Author.findAll().include(Book.class);
String json = authors.toJson(true);

Alternatively you can write the same in one line:

String json = Author.findAll().include(Book.class).toJson(true);

Of course, this will load all authors and their books into memory. You might want to replace the Author.findAll() with Author.where(..) with appropriate criteria.

I hope this helps!

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • yes but what if I have a complicated query that pulls from multiple tables book-author-publisher-etc ... there is no way of getting this right? –  Oct 22 '14 at 16:43
  • I just want to do a generic select * from multiple tables –  Oct 22 '14 at 16:55
  • In that case, you cannot use models, because they are an ORM - Object to Relation Mapping:). I would suggest to look at [Base#findAll()](http://javalite.github.io/activejdbc/org/javalite/activejdbc/Base.html#findAll-java.lang.String-java.lang.Object...-) or [Base#find()](http://javalite.github.io/activejdbc/org/javalite/activejdbc/Base.html#find-java.lang.String-java.lang.Object...-) Just beware that `findAll()` or `all()` will load entire resultset into your list (heap), while the `find()` will allow you to stream millions of records one at the time as a cursor. – ipolevoy Oct 22 '14 at 18:52