title: How to get the full entity object with a query Room using a table with FK
body: <p>Let's say I have these tables :</p>
  <h2>Table - User</h2>
  <blockquote>
  <p>Stores the users</p>
  </blockquote>
  <pre><code>@Entity(
      tableName = &quot;USER&quot;
  )
  data class User(
      @PrimaryKey(autoGenerate = true)
      @ColumnInfo(name = &quot;user_id&quot;)
      val id: Int,
  
      @ColumnInfo(name = &quot;user_name&quot;)
      val name: String
  )
  </code></pre>
  <h2>Table - Item</h2>
  <blockquote>
  <p>Store the items it's like a product</p>
  </blockquote>
  <pre><code>@Entity(
      tableName = &quot;ITEM&quot;
  )
  data class Item(
      @PrimaryKey(autoGenerate = true)
      @ColumnInfo(name = &quot;item_id&quot;)
      val id: Int,
  
      @ColumnInfo(name = &quot;item_name&quot;)
      val name: String,
  
      @ColumnInfo(name = &quot;item_description&quot;)
      val description: String
  )
  
  </code></pre>
  <h2>Table - Special</h2>
  <blockquote>
  <p>Store an speciality for product 1 Special needs a Product to exist</p>
  </blockquote>
  <pre><code>@Entity(
      tableName = &quot;SPECIAL&quot;,
      foreignKeys = [ForeignKey(
          entity = Item::class,
          parentColumns = [&quot;item_id&quot;],
          childColumns = [&quot;special_item_id&quot;]
      )]
  )
  data class Special(
      @PrimaryKey(autoGenerate = true)
      @ColumnInfo(name = &quot;special_id&quot;)
      val id: Int,
  
      @ColumnInfo(name = &quot;special_item_id&quot;)
      val coupon_product_id: Int,
  
      @ColumnInfo(name = &quot;special_name&quot;)
      val name: String,
  
      @ColumnInfo(name = &quot;special_description&quot;)
      val description: String
  
  )
  
  </code></pre>
  <h2>Table -- Favourite</h2>
  <blockquote>
  <p>Stores the favourite Specials from an user</p>
  </blockquote>
  <pre><code>@Entity(
      tableName = &quot;TB_FAVOURITE&quot;,
      foreignKeys = [ForeignKey(
          entity = User::class,
          parentColumns = [&quot;user_id&quot;],
          childColumns = [&quot;favourite_user_id&quot;]
      ), ForeignKey(
          entity = Special::class,
          parentColumns = [&quot;special_id&quot;],
          childColumns = [&quot;favourite_special_id&quot;]
      )]
  )
  data class Favourite(
      @PrimaryKey
      @ColumnInfo(name = &quot;favourite_user_id&quot;)
      val id: Int,
  
      @ColumnInfo(name = &quot;favourite_special_id&quot;)
      val specialId: Int
  
  )
  </code></pre>
  <p>My question is, how can I make a query to select all the Specials and then creating like a class that stores if it's favourite of the user or not. It's one user app for the moment, it's for a demo app. So, user will always be the same so I can hardcode the <code>findById</code> and send the id of the user.</p>
  <h2>The goal</h2>
  <p>Is to get a result of a query as List that contains :</p>
  <ol>
  <li>All of the Specials</li>
  <li>In the SomeClass should contain the Item of the Special inside</li>
  <li>A flag to know if it's favourite for that driver or not</li>
  </ol>
  <p>The thing is I want to be able to map the result of the room database to my desired object, so I guess the query is more important than the mapper, I know how to do the mapper.</p>
  <p>Note that I'm using an <code>assets/database/mydb.db</code> file to start the database I don't know if it matters.</p>
  <p>How would be the query to do that? Is there any improvement of the structure of the db to make it easier?</p>