Search code examples
androidsqliteandroid-room

Room - SQLiteLog: (1) too many SQL variables

I have a table of about 10,000 records.

Example, I have the following entity:

@Entity(tableName="cupcakes")
public class CupcakeEntity {

  @PrimaryKey(autoGenerate = true)
  @ColumnName(name = "id")
  long id;

  @ColumnName(name = "name")
  String name;

  @ColumnName(name = "parent_id")
  Long parentId;
}

Then, I self-join CupcakeEntity with this class:

public class CupcakeModel {

  @Embedded
  CupcakeEntity cupcake;

  @Relation(parentColumn = "id", entityColumn = "parent_id")
  List<CupcakeEntity> parent;
}

When I fetch CupcakeModel with an SQL Statement:

SELECT * FROM cupcakes WHERE name LIKE '%' || :keyword || '%'

The query will throw an exception of:

too many SQL variables (code 1): , while compiling: 
SELECT id,name,parent_id FROM `cupcakes` WHERE parent_id IN
(?,?,?,?...............................

I've looked into the generated code of Room and found out that it's looping for all entities.

Is there any other workaround for this other than creating my own JOIN statements?

Solution

  • I'll just leave this answer here in case somebody bumps into this issue.

    As suggested by @CommonsWare, I added a LIMIT to my query which now looks like:

    SELECT * FROM cupcakes WHERE name LIKE '%' || :keyword || '%' LIMIT 500

    Credits to @CommonsWare for this workaround.