CODEWITHSUNDEEP
androidsqliteandroid-room
Kanwarpreet Singh
Kanwarpreet Singh

Reputation: 2086

is there any way to run nested query in room database

I am migrating my old Android project to new standards, I got stuck in between room database, not able to find how to execute these 2 SQLite commands using room database. I have 2 queries which are working in a nested manner, so to achieve the same result using room is there any possibility. 

    default public ArrayList getOfflineRoomsAndObjectsByParentIds(String parentId, String site_id, Context context) {

        databaseHandler = new DatabaseHandler(context);
        Cursor cr = null;
        ArrayList<Room> offlineRoomNamesList = new ArrayList<>();
        try {

           **sqLiteDatabase = databaseHandler.getReadableDatabase();
            String query = "SELECT * FROM " + Constants.TABLE_ROOM_NAMES
                    + " WHERE " + Constants.SITE_ID + " = '" + site_id + "' AND " + Constants.PARENT_ID + "='" + parentId + "'";**

            cr = sqLiteDatabase.rawQuery(query, null);
            if (cr.moveToFirst()) {

                do {

                    Room room = new Room();
                    **room.setId(cr.getString(cr.getColumnIndex(Constants.KEY_ROOM_ID)));
                    query = "SELECT * FROM " + Constants.TABLE_OFFLINE_ROOM_OBJECTS
                            + " WHERE " + Constants.KEY_ROOM_KEY + " = ?"
                            + " AND " + Constants.SITE_ID + " = ? ORDER BY " + Constants.KEY_OBJECT_NAME;**

                    Cursor cursor = sqLiteDatabase.rawQuery(query, new String[]{cr.getString(cr.getColumnIndex(Constants.KEY_ROOM_KEY)), site_id});
                    if (cursor.moveToFirst()) {
                        do {
                            RoomObject roomObject = new RoomObject();
                            roomObject.setRoomKey(cursor.getString(cursor.getColumnIndex(Constants.KEY_ROOM_KEY)));
                            roomObject.setId(cursor.getInt(cursor.getColumnIndex(Constants.KEY_ID)));
                            roomObject.setInstanceNumber(cursor.getInt(cursor.getColumnIndex(Constants.KEY_INSTANCE_NUMBER)));
                            roomObject.setObjectType(cursor.getString(cursor.getColumnIndex(Constants.KEY_OBJECT_TYPE)));


                            String mergeKey = AppSharedPreference.getString(AppSharedPreference.MERGE_KEY, "", context);
                            if (roomObject.getObjectName().contains(mergeKey) && !TextUtils.isEmpty(mergeKey)) {
                                room.addMergeableObjectsInList(roomObject);
                            } else {
                                room.addRoomObjectsInList(roomObject);
                            }


                        }
                        while (cursor.moveToNext());
                    }
                    if (room.getRoomObjectsList().size() > 0)
                        offlineRoomNamesList.add(room);
                    cursor.close();
                } while (cr.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cr.close();
                sqLiteDatabase.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return offlineRoomNamesList;
    }

The only possibility I am looking at this moment is two define 2 different query and execute them into a nested manner.

@Dao
public interface ControllerRoomObjectDao {



    @Query("SELECT * FROM room_names WHERE site_id=:siteId AND parentId=:parentId")
    Single<List<Room>>getOfflineRoomsAndObjectsByParentId(String siteId, String parentId);


    @Query("SELECT * FROM offline_objects WHERE site_id=:siteId AND parentId=:parentId")
    Single<List<RoomObject>>getOfflineObjectsByParentId(String siteId, String parentId);

}

TABLE room_names

TABLE offline_objects

Upvotes: 2

Views: 2571

Answers (2)

Sahjad Ansari
Sahjad Ansari

Reputation: 110

You can use databaseView for your use case eg:

@DatabaseView("SELECT user.id, user.name, user.departmentId," +
        "department.name AS departmentName FROM user " +
        "INNER JOIN department ON user.departmentId = department.id")

public class UserDetail {
    public long id;
    public String name;
    public long departmentId;
    public String departmentName;
}

for more info https://developer.android.com/training/data-storage/room/creating-views#java

Upvotes: 1

Boycott A.I.
Boycott A.I.

Reputation: 18871

Not sure if this meets your requirements exactly, but it may help others with the same general requirement.

Basically, I took the advice from this example that shows how to perform a nested query / subquery in MySQL.

With that same syntax I was able to change my basic DAO query from:

@Transaction
@Query("SELECT * FROM `events` WHERE `item_id` = :currentItemId AND `event_datetime` > :datetimeFrom ORDER BY `event_datetime` ASC LIMIT 1")
LiveData<Event> getNextEventWithSameItemId(long currentItemId, String datetimeFrom);

to

@Transaction
@Query("SELECT * FROM `events` WHERE `item_id` IN (SELECT `item_id` FROM `events` WHERE `_id` = :currentEventId) AND `event_datetime` > :datetimeFrom ORDER BY `event_datetime` ASC LIMIT 1")
LiveData<Event> getNextEventWithSameEventId(long currentEventId, String datetimeFrom);

where my Event entity is

@Entity(tableName = "events")
public class Event {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    public long id;

    @ColumnInfo(name = "item_id") // Relates to `items`.`_id`
    public long itemId;

    @ColumnInfo(name = "event_datetime")
    public String eventDatetime;
    
    ...
}

Upvotes: 0

Related Questions