android-sqlite - FROM 子句中的 Android Room 子查询未按预期工作

问题描述

我使用https://sqliteonline.com/测试了我的查询，但无法让它与 Android Room 一起使用，我不明白为什么（相信我我已经尝试过）。为什么我的 DAO 中的查询不起作用？另一方面，我不确定这是实现我想要做的最好的方法，所以请告诉我是否应该完全改变我的方法。

另外，这是我第一次发布问题，如果我遗漏了一些重要的事情，我深表歉意。

我用 sqliteonline 创建了一个测试数据库，并验证了我的查询在那里工作。我在我的项目中使用 Android Room，在我的 build.gradle （应用程序）中有以下几行：

// Room components
implementation 'androidx.room:room-runtime:2.1.0-beta01'
annotationProcessor 'androidx.room:room-compiler:2.1.0-beta01'
androidTestImplementation 'androidx.room:room-testing:2.1.0-beta01'

在测试环境中工作的查询：

SELECT *,
(SELECT COUNT(*) FROM groups WHERE parent_id=a.id) AS gCount,
(SELECT COUNT(*) FROM relays WHERE parent_id=a.id) AS rCount
FROM (SELECT DISTINCT * FROM worksites) a ;

我的 DAO 中的查询：

@Query("SELECT *, " +  
            "(SELECT COUNT(*) FROM relay_groups WHERE worksite_id=a.w_id) AS amountRelayGroups," +  
            "(SELECT COUNT(*) FROM relays_table WHERE worksite_id=a.w_id) AS amountRelays" +  
            "FROM (SELECT DISTINCT * FROM worksites_table) a")  
    LiveData<List<Worksites>> fetchAllWorksites();

我的桌子：

@Entity(tableName = "worksites_table")
    public class Worksites {

        @PrimaryKey(autoGenerate = true)
        @ColumnInfo(name = "w_id")
        private long worksite_id;

        @NonNull
        @ColumnInfo(name = "w_name")
        private String worksiteName;

        @ColumnInfo(name = "w_description")
        private String worksiteDescription;

        @Ignore
        @ColumnInfo(name = "amountRelays")
        private long amountRelays;

        @Ignore
        @ColumnInfo(name = "amountRelayGroups")
        private long amountRelayGroups;

@Entity (tableName = "relay_groups",
            foreignKeys = @ForeignKey(entity = Worksites.class,
            parentColumns = "w_id",
            childColumns = "worksite_id",
            onDelete = CASCADE))
public class RelayGroups {

    public RelayGroups(){

    }

    public RelayGroups(String name){
        this.groupName = name;
    }

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "group_id")
    private long group_id;

    @NonNull
    @ColumnInfo(name = "group_name")
    private String groupName;

    @ColumnInfo(name = "description")
    private String description;

    @NonNull
    @ColumnInfo(name = "worksite_id")
    private long worksiteId;

@Entity(tableName = "relays_table",
        foreignKeys = @ForeignKey(entity = Worksites.class,
        parentColumns = "w_id",
        childColumns = "worksite_id",
        onDelete = CASCADE))
    public class Relays implements Parcelable {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "relay_id")
    private long relayId;

    @NonNull
    @ColumnInfo(name = "relay_name")
    private String relayName;

    @NonNull
    @ColumnInfo(name = "relay_number")
    private String relayNumber;

    @ColumnInfo(name = "relay_started")
    private boolean relayOnOff;

    @NonNull
    @ColumnInfo(name = "relay_type")
    private String relayType;

    @ColumnInfo(name = "registered_master")
    private boolean registeredMaster;

    @ColumnInfo(name = "registered_user")
    private boolean registeredUser;

    @ColumnInfo(name = "security_code")
    private String securityCode;

    @ColumnInfo(name = "groups_string")
    private String groupsString;

    @NonNull
    @ColumnInfo(name ="worksite_id")
    private long worksiteId;

    @ColumnInfo(name ="description")
    private String relayDescription;

我正在尝试从表“worksite”中获取所有列的行以及“relay_groups”和“relays_table”-tables 中匹配的 worksite_ids 的计数。

Java 编译器给我带来以下错误/警告：

error: extraneous input '(' expecting {<EOF>, ';', K_ALTER, K_ANALYZE, K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH, K_DROP, K_END,   K_EXPLAIN, K_INSERT, K_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}
no viable alternative at input 'DISTINCT * FROM worksites_table)'

The query returns some columns [amountRelayGroups, amountRelays] which are not used by PACKAGE.Worksites. You can use @ColumnInfo annotation on the  fields to specify the mapping. PACKAGE.Worksites has some fields [w_description] which are not returned by the query. If they are not  supposed to be read from the result, you can mark them with @Ignore annotation. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the  query: w_name, w_id, amountRelayGroups, amountRelays. Fields in PACKAGE.Worksites: w_id, w_name, w_description.

标签： android-sqliteandroid-room

android-sqlite - FROM 子句中的 Android Room 子查询未按预期工作

问题描述

解决方案

推荐阅读