如何搜索共享外键的两个表(我想这是对的...)? [英] How to search two tables sharing a foreign key (I think I'm asking this right....)?

问题描述

狗实体

@Entity(tableName = "dog_table")
public class DogEntity {

  private int mId;
  private String mName, mBreed;
  etc..
}

玩具实体

@Entity(tableName = "toy_table")
public class ToyEntity {

  private int mId;
  private String mName, mBrand;
  etc..
}

DogAndToy联接表实体

@Entity(tableName = "dog_and_toy_join_table",
    primaryKeys = {"mDogID", "mToyId"},
    foreignKeys = {
        @ForeignKey(
            entity = DogEntity.class,
            parentColumns = "mId",
            childColumns = "mDogID",
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        @ForeignKey(
            entity = ToyEntity.class,
            parentColumns = "mId",
            childColumns = "mToyId",
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    },
    indices = {@Index("mDogID"), @Index("mToyId")}
)
public class DogAndToyJoinEntity{

  private final int mDogID, mToyId;

  public DogAndToyJoinEntity(int mDogID, int mToyId) {
    this.mDogID = mDogID;
    this.mToyId = mToyId;
  }
  etc..
}

DogAndToy数据类

public class DogAndToy {

  @Embedded
  public Dog mDog;

  @Relation(
      parentColumn = "mId",
      entityColumn = "mId",
      entity = ToyEntity.class,
      associateBy =
      @Junction(
          value = DogAndToyJoinEntity.class,
          parentColumn = "mDogId",
          entityColumn = "mToyId"
      )
  )
  public List<ToyEntity> toyList;
}

注释:所有的狗都可以有多个玩具，并且玩具可以与多个狗相关联.狗与狗玩具实体不共享任何字段(例如-狗没有toyId等)

我已经尝试了几天来把头缠住

I've been trying for a few days to wrap my head around

如何查询/获取与一个玩具相关的所有狗(按名称)

how to query/get all dogs associated with one Toy (by name)

我将DogAndToy数据类用于RecyclerView中的显示目的.

JOIN和INNER JOIN查询让我感到困惑，我一直在尝试多种变体，但始终以零搜索结果结束.这是我最近的尝试:

JOIN and INNER JOIN queries are baffling to me and I've been trying multiple variations but keep ending up with zero search results. Here's my most recent try:

  @Transaction
  @Query("SELECT dog_table.* FROM dog_table" +
      "INNER JOIN dog_and_toy_join_table ON dog_table.mId = dog_and_toy_join_table.mDogId" +
      "INNER JOIN toy_table ON toy_table.mId = dog_and_toy_join_table.mToyId " +
      "WHERE toy_table.mName LIKE :query")
  LiveData<List<DogAndToy>> findDogsByToyName(String query);

有人可以在Android Room中建议这些查询的分步说明吗?我在此处或互联网上任何地方找到的任何JOIN文章/示例都没有加入"链接，(外键)参考...我什至以正确的方式尝试了吗?

Can anyone suggest a step-by-step description of these queries in Android Room? Any of the JOIN articles/examples I find here or anywhere on the internets don't have a "join" (foreign key) reference... Am I even trying this in the right manner?

更新:为了澄清，我有FTS表和基本"表.搜索工作正常(例如，按名称搜索等)

推荐答案

用变量替换:toyName

Replace :toyName with the variable

SELECT d.mName FROM dog_table AS d
WHERE d.mId IN ( 
  SELECT j.mDogID FROM dog_and_toy_join_table AS j
  WHERE j.mToyId = (
    SELECT t.mId FROM toy_table AS t
    WHERE t.mName = :toyName));

编辑

TBH，不知道为什么它只选择一行.也许其他人可以回答.

TBH, no idea why it only selects one row. Maybe someone else can answer it.

这是平均时间:

select d.mName
from dog_table d 
    INNER join dog_and_toy_join_table dt
        on d.mid = dt.mDogID 
    INNER JOIN toy_table t
        ON dt.mToyId = t.mId
    WHERE t.mName = 'toy1'

这篇关于如何搜索共享外键的两个表(我想这是对的...)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！