Room database fails to return group_concat column

Question

I'm trying to create a DatabaseView in room to get a desired data from several tables. This is my database view:

@DatabaseView("SELECT site.name AS address, group_site.name AS groupName, group_site.member_id AS memberId " +
        "FROM site, group_site " +
        "INNER JOIN groupsite_join_site " +
        "ON site.id = groupsite_join_site.site_id AND group_site.id = groupsite_join_site.group_site_id " +
        "UNION SELECT preloaded_site.name AS address, preloaded_group_site.name AS groupName, preloaded_group_site.memberId AS memberId " +
        "FROM preloaded_site, preloaded_group_site " +
        "INNER JOIN preloaded_groupsite_join_site ON preloaded_site.id = preloaded_groupsite_join_site.site_id AND preloaded_site.id = preloaded_groupsite_join_site.site_id"
)
public class SiteDetail {
    long memberId;
    String address;
    String groupName;
}

And this is my DAO:

@Dao
public interface SiteDetailDao {

    @Query("SELECT address, group_concat(groupName, ', '), memberId FROM sitedetail WHERE memberId = :memberId OR memberId = -1 GROUP BY address LIMIT 10")
    LiveData<List<SiteDetail>> getSiteDetailForMember(long memberId);
}

The view is created as supposed, but, the query on the view returns all groupNames as null. I've checked the Query with DB Browser which run the query on sqlite itself. DB Browser returns the expected data. A list with 10 records, each like this:

|id|  address  |           groupName            |
| 1|youtube.com|videos, entertainment, streaming|

Why does this happen? Does room have any problems with group_contact? This is the SiteDetailDao_Impl:

  @Override
  public LiveData<List<SiteDetail>> getSiteDetailForMember(final long memberId) {
    final String _sql = "SELECT address, group_concat(groupName, ', '), memberId FROM sitedetail WHERE memberId = ? OR memberId = -1 GROUP BY address LIMIT 10";
    final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1);
    int _argIndex = 1;
    _statement.bindLong(_argIndex, memberId);
    return __db.getInvalidationTracker().createLiveData(new String[]{"sitedetail"}, false, new Callable<List<SiteDetail>>() {
      @Override
      public List<SiteDetail> call() throws Exception {
        final Cursor _cursor = DBUtil.query(__db, _statement, false);
        try {
          final int _cursorIndexOfAddress = CursorUtil.getColumnIndexOrThrow(_cursor, "address");
          final int _cursorIndexOfMemberId = CursorUtil.getColumnIndexOrThrow(_cursor, "memberId");
          final List<SiteDetail> _result = new ArrayList<SiteDetail>(_cursor.getCount());
          while(_cursor.moveToNext()) {
            final SiteDetail _item;
            _item = new SiteDetail();
            final String _tmpAddress;
            _tmpAddress = _cursor.getString(_cursorIndexOfAddress);
            _item.setAddress(_tmpAddress);
            final long _tmpMemberId;
            _tmpMemberId = _cursor.getLong(_cursorIndexOfMemberId);
            _item.setMemberId(_tmpMemberId);
            _result.add(_item);
          }
          return _result;
        } finally {
          _cursor.close();
        }
      }

      @Override
      protected void finalize() {
        _statement.release();
      }
    });
  }

Answer 1

The posting of SiteDetailDao_Impl shows that code is not generated to fetch the value of result-column group_concat(groupName, ', '). I would guess this is because it has no simple name. So, as @sergiytikonov suggests in his comment, you need to provide an alias for that column. Something like:

group_concat(groupName, ', ') AS concatName

To support the new alias, create another class (name as you like):

public class SiteInfo {
    long memberId;
    String address;
    String concatName;
}

And change your query to return LiveData<List<SiteInfo>>:

@Query("SELECT address, group_concat(groupName, ', ') AS concatName, memberId FROM sitedetail'" +
    " WHERE memberId = :memberId OR memberId = -1 GROUP BY address LIMIT 10")
LiveData<List<SiteInfo>> getSiteDetailForMember(long memberId);