How to do relation with array in one table but not in other?

Question

(Updated) I have tables like these:

@Entity(tableName = "author_table")
public class Author {

  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "authorId")
  private int mAuthorId;
    
  @ColumnInfo(name = "authorName")
  private String mAuthorName;

  @ColumnInfo(name = "authorPublisherName")
  private String mAuthorPublisherName;

and

@Entity(tableName = "book_table")
public class Book{

  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "bookId")
  private int mBookId;

  @ColumnInfo(name = "bookName")
  private String mBookName;

  @ColumnInfo(name = "bookAuthors")
  private String[] bookAuthors;

I've been trying and re-trying this for weeks but I can't get anything to work. I've tried work-around ways of doing this without any kinds of relation with no luck. I've tried relations in all manners (foreign keys, relations, joins, etc). It's weird "knowing" database theory and have taken uni courses in this, but I can't get it to work with Android Room. How can I get or query Authors or Author Publishers from a Book? (if I'm asking this correctly...)

I want the publishers from all the Authors from this one Book.

Also, how does Embedding affect storage? If I "embed" an author in every book (thousands of authors and thousands books, in an Android app), does that basically create a duplicate or copy of each Author in every Book entity?

I've tried:

  @Query("select authorPublisherNamefrom author_table where authorName IN (:names)")
  LiveData<List<String>> getAllThePublishers(List<String> names);

This was giving me a list size of 1. I'm assuming it just pings the first name that scores true within the queried list.

Then I tried creating another class like how @ sergiy tikhonov commented and from How can I represent a many to many relation with Android Room? :

@Entity(primaryKeys = {"authName", "bookAuth"})
public class BookAndAuthors{

  private int authName, bookAuth;
}

and

public class BooksWithAuthors{

  @Embedded
  public Book book;
  @Relation(
      parentColumn = "authorName",
      entityColumn = "bookAuthors",
      associateBy = @Junction(
          value = BookAndAuthors.class,
          parentColumn = "authName",
          entityColumn = "bookAuth")
  )
  public List<FastMoveEntity> fastMoveList;
}

But now I don't know how to query to get the Publishers (authorPublisherName).

side note: with sergiy's answer with tying the keys in a class, I couldn't figure out what to do with that or get it to work. Do they have to be inserted with that relation somehow?

score 0 · Answer 1 · answered Oct 23 '20 at 13:06

How do/can I reference or use Foreign Keys if the child table uses a String Array while the parent table has a String value?

I recommend not to search for answer to this, but just to change your table's structure. For all relational DB (and Sqlite is on of them) good (and proven by time) way is to achieve table's normalization.

So instead of two tables just use three. Third would be BookAuthors. Also common way is to use integer Primary keys for your tables.

@Entity(tableName = "author_table")
public class Author {

  @PrimaryKey
  @ColumnInfo(name = "id")
  public int id;

  @ColumnInfo(name = "authorName")
  private String authorName;
  ........

@Entity(tableName = "book_table")
public class Book {

  @PrimaryKey
  @ColumnInfo(name = "id")
  public int id;

  @ColumnInfo(name = "bookName")
  private String bookName;
  ........

@Entity(tableName = "book_authors_table", primaryKeys = {"bookId", "authorId"}) // you could set there foreign keys for both fields if you want
public class BookAuthors {
 
  @ColumnInfo(name = "bookId")
  public int bookId;

  @ColumnInfo(name = "authorId")
  public int authorId;
}

With that structure you could get easily any join's of data you want (books with all authors and author with all written books). How to achieve that - look in documentation for many-to-many relations

my id's are autoGenerated and do not have anything in common with other entities. I couldn't understand how to work in your solution. Does it only work if the entities were inserted into the tables with your third class some how? Plus your answer does not include querying columns that include arrays or lists in one table and not another. — the derek Fawcett, Nov 06 '20 at 19:52

How to do relation with array in one table but not in other?

1 Answers1