How to get data from dynamic column name

Question

I was using the room database and got stuck in the below query.

@Query("SELECT * FROM POI WHERE :columName IN (:fieldValue) ORDER BY ABS(Latitude - :latitude) + ABS(Longitude - :longitude) ASC LIMIT :limit")
List<PoiItems> getNearByLocation(String columName, List<String> fieldValue, double latitude, double longitude, int limit);

When I execute the above query it's not giving me any records. I think the issue is with the dynamic column name. Because when I pass the static column name it gives me the records.

So is there any solution regarding this?

Any help would be grateful.

MikeT · Answer 1 · 2023-05-08T08:16:20.717

When you pass a parameter to an @Query the value is bound by the SQLite interface. SQLite does not allow bound values for identifiers (e.g. table,column,view names). So instead of comparing the column against the list of values, the value of the passed column name as a literal value is compared against the list of values.

There are 2 ways that you could do this

one would require a CASE WHEN THEN ELSE END constructs and this would be reliant upon the number of potential values. Something like (assuming 3 possible columns being col1, col2 and col3)

:-

SELECT * FROM POI WHERE 
    CASE 
        WHEN :columnName /*<<<<< passed value */ = 'col1' THEN col1 IN (:fieldValue) 
        WHEN :columnName /*<<<<< passed value */ = 'col2' THEN col2 IN (:fieldValue)
        WHEN :columnName /*<<<<< passed value */ = 'col3' THEN col3 IN (:fieldValue)
    END
ORDER BY ABS(Latitude - :latitude) + ABS(Longitude - :longitude) ASC LIMIT :limit
;

the other would be to use an @RawQuery, where the column name is replaced in the query string. This method has the disadvantage that the resultant SQL is not checked at compile time, so is more open to run time errors. However, it is typically the simpler method as far as SQL is concerned.
you may find this useful

How to get data from dynamic column name

1 Answers1