ORMLite joins queries and Order by

Question

I'm tring to make join in two tables and get all columns in both, I did this:

QueryBuilder<A, Integer> aQb = aDao.queryBuilder();
QueryBuilder<B, Integer> bQb = bDao.queryBuilder();
aQb.join(bQb).prepare();

This equates to:

SELECT 'A'.* FROM A INNER JOIN B WHERE A.id = B.id;

But I want:

SELECT * FROM A INNER JOIN B WHERE A.id = B.id;

Other problem is when taking order by a field of B, like:

aQb.orderBy(B.COLUMN, true);

I get an error saying "no table column B".

Answer 1

Actually, I managed to do it without writing my whole query as raw query. This way, I didn't need to replace my query builder codes (which is pretty complicated). To achieve that, I followed the following steps:

(Assuming I have two tables, my_table and my_join_table and their daos, I want to order my query on my_table by the column order_column_1 of the my_join_table)

1- Joined two query builders & used QueryBuilder.selectRaw(String... columns) method to include the original table's + the columns I want to use in foreign sort. Example:

QueryBuilder<MyJoinTable, MyJoinPK> myJoinQueryBuilder = myJoinDao.queryBuilder();
QueryBuilder<MyTable, MyPK> myQueryBuilder = myDao.queryBuilder().join(myJoinQueryBuilder).selectRaw("`my_table`.*", "`my_join_table`.`order_column` as `order_column_1`");

2- Included my order by clauses like this:

myQueryBuilder.orderByRaw("`order_column_1` ASC");

3- After setting all the select columns & order by clauses, it's time to prepare the statement:

String statement = myQueryBuilder.prepare().getStatement();

4- Get the table info from the dao: TableInfo tableInfo = ((BaseDaoImpl) myDao).getTableInfo();

5- Created my custom column-to-object mapper which just ignores the unknown column names. We avoid the mapping error of our custon columns (order_column_1 in this case) by doing this. Example:

RawRowMapper<MyTable> mapper = new UnknownColumnIgnoringGenericRowMapper<>(tableInfo);

6- Query the table for the results:

GenericRawResults<MyTable> results = activityDao.queryRaw(statement, mapper);

7- Finally, convert the generic raw results to list:

List<MyTable> myObjects = new ArrayList<>();
for (MyTable myObject : results) {
    myObjects.add(myObject);
}

Here's the custom row mapper I created by modifying (just swallowed the exception) com.j256.ormlite.stmt.RawRowMapperImpl to avoid the unknown column mapping errors. You can copy&paste this into your project:

import com.j256.ormlite.dao.RawRowMapper;
import com.j256.ormlite.field.FieldType;
import com.j256.ormlite.table.TableInfo;

import java.sql.SQLException;

public class UnknownColumnIgnoringGenericRowMapper<T, ID> implements RawRowMapper<T> {

    private final TableInfo<T, ID> tableInfo;

    public UnknownColumnIgnoringGenericRowMapper(TableInfo<T, ID> tableInfo) {
        this.tableInfo = tableInfo;
    }

    public T mapRow(String[] columnNames, String[] resultColumns) throws SQLException {
        // create our object
        T rowObj = tableInfo.createObject();
        for (int i = 0; i < columnNames.length; i++) {
            // sanity check, prolly will never happen but let's be careful out there
            if (i >= resultColumns.length) {
                continue;
            }
            try {
                // run through and convert each field
                FieldType fieldType = tableInfo.getFieldTypeByColumnName(columnNames[i]);
                Object fieldObj = fieldType.convertStringToJavaField(resultColumns[i], i);
                // assign it to the row object
                fieldType.assignField(rowObj, fieldObj, false, null);
            } catch (IllegalArgumentException e) {
                // log this or do whatever you want
            }
        }
        return rowObj;
    }
}

It's pretty hacky & seems like overkill for this operation but I definitely needed it and this method worked well.

Answer 2

When you are using the QueryBuilder, it is expecting to return B objects. They cannot contain all of the fields from A in B. It will not flesh out foreign sub-fields if that is what you mean. That feature has not crossed the lite barrier for ORMLite.

Ordering on join-table is also not supported. You can certainly add the bQb.orderBy(B.COLUMN, true) but I don't think that will do what you want.

You can certainly use raw-queries for this although it is not optimal.