Question: select from joined tables

[agrebnev]

Hi

Could you please give an example (or tip) how to make SELECT (inner join or left outer join) from several tables?

Thanks in advance.

[jakenjarvis]

Hi, @agrebnev
Sorry, Reply is now late.
Android-OrmLiteContentProvider is a very thin wrapper of OrmLite.
This question of you, it makes more sense to Ask community of OrmLite is a good idea.

I will not make a sample for this. But, Issue4 is might be useful to you.
#4

[agrebnev]

@jakenjarvis thank you for your answer.

I do not want to use ORMLite to make queries. I woule like to use usual SQL.

Let me clarify my opinion.

The current implementation of the OrmLiteSimpleContentProvider.onQuery allows as tables uses only one table name, see

builder.setTables(target.getTableInfo().getName());

I offer to make the possibility to register some 'named queries' in the MatcherController, e.g.

.add(SomeEntity.class, NewType.COMPLEX, "complex", SomeEntityContract.CONTENT_URI_PATTERN_COMPLEX)//

and have some map of patternCodes and SQL_TABLES e.g.

namedQueries.put(SomeEntityContract.CONTENT_URI_PATTERN_COMPLEX,
        "one_table LEFT OUTER JOIN two_table ON (one_table.id = two_table.one_table_id)");

than we will be able to use it in the

builder.setTables(namedQueries.get(SomeEntityContract.CONTENT_URI_PATTERN_COMPLEX));

Moreover I offer to make more flexible version, which uses rawQuery

rawQueries.put(SomeEntityContract.CONTENT_URI_PATTERN_VERY_COMPLEX,
        "SELECT %s FROM one_table LEFT OUTER JOIN two_table ON (one_table.id = two_table.one_table_id) WHERE %s GROUP BY ... HAVING ... LIMIT ... ORDER BY %s");

I this case I offer instead of SQLiteQueryBuilder use rawQuery(), e.g.

String orderBy = getSortOrderStringForQuery(target, parameter);
String rawSQL = rawQueries.get(SomeEntityContract.CONTENT_URI_PATTERN_VERY_COMPLEX);
rawSQL = String.format(rawSQL, parameter.getProjection(), parameter.getSelection(), orderBy);
return db.rawQuery(rawSQL, parameter.getSelectionArgs());

Above code is not real implementation, it is idea only. If you would like we can discuss it in details.

I think it really extends usability of Android-OrmLiteContentProvider. Unfortunately current implementation is not so useful because of lack of joined queries.

[jakenjarvis]

Hi, @agrebnev
Oops, sorry! I did not understand your intentions. This was a suggestion from you.

First impression, Your proposal is very interesting. I thought that we should implement it.
Hmm, however, I thought a little bit, but this is a little troublesome.
It is because the implementation is very different compared with OrmLiteSimpleContentProvider.

If I implement a minimum this, I will add a tag information of type string to MatcherController.
By passing the SQL statement to this tag information, you will be able to realize them.

Then, I thought to make a new class that is similar to OrmLiteSimpleContentProvider.
Hmm, this is not very interesting.

These are the implementation that came up first.(Thought is probably not enough)
Do you can suggest how to implement specific you are thinking?

[agrebnev]

@jakenjarvis
I think you understand your own architecture more deeper. :)

To tell you the truth your library is not useful for me at all without this functionality. So I think this implementation have to be in the OrmLiteSimpleContentProvider class.

Have a look at the following classes. Probably they could give you some ideas.

https://code.google.com/p/iosched/source/browse/#git%2Fandroid%2Fsrc%2Fmain%2Fjava%2Fcom%2Fgoogle%2Fandroid%2Fapps%2Fiosched%2Fprovider

[jakenjarvis]

Hi, @agrebnev
Thanks!
I was impressed with it to look a little. I appreciate your suggestion. There were found some in it.

• I did not know the existence of ContactsContract.CALLER_IS_SYNCADAPTER.
• It is assembled queries using well the interface and SelectionBuilder class.

I have to understand this more.

I can not immediately this work for busy. However, in the near future, Android-OrmLiteContentProvider library is going to be as useful to you. :)

[agrebnev]

I was pressed by time. So I have implemented my idea myself: universal implementation of ContentProvider which supports OrmLite and could be flexible configured. You can find my implementation here https://github.com/blandware/android-atleap

[jakenjarvis]

Oh, I will look at it later. Thanks!