Join query

[loozyoni]

Do you support JOIN query? If so, please provide small example, how to do it.

Thanks

[Fizcko]

You can use LEFT JOIN with @DatabaseView for example:

// books.dart

@Entity(tableName: 'books')
class Book {
    @PrimaryKey(autoGenerate: true)
    int id; 
    String name;
    int location;
}

@Entity(tableName: 'locations')
class Location {
    @PrimaryKey(autoGenerate: true)
    int id;
    @ColumnInfo(name: 'location_name')
    String locationName;
}

@DatabaseView('SELECT b.id, b.name, l.location_name as location FROM books as b LEFT JOIN locations as l ON b.location = l.id', viewName: 'bookAndMoreView')
class BookAndMore {
    int id;
    String name;
    String location;

    BookAndMore({this.id, this.name, this.location});

}

Modify your database file:

// database.dart

@Database(version: 1, entities: [Book, Location], views: [BookAndMore])
abstract class AppDatabase extends FloorDatabase {
  BookDAO get bookDAO;
  LocationDAO get locationDAO;
}

Then in your DAO you can query this view as a normal table

// books_dao.dart

@Query('SELECT * FROM bookAndMoreView')
Future<List<BookAndMore>> getBooksAndMore();

[loozyoni]

Can I use GROUP BY? (for example to get count of distinct books names) Also, can I use WHERE? (for example to get books for specific author)

[dkaera]

@loozyoni

Can I use GROUP BY? (for example to get count of distinct books names)

I believe yes. There is no specific sample provided in the example project, the fact that it returns a Dart core type suggests that it should function correctly.

Also, can I use WHERE? (for example to get books for specific author)

Yes, it works, here is a sample here

[loozyoni]

About GROUP BY the problem is that I need to reference to the original table and in this example: group by books.name, and my question is if it is possible. About WHERE I did not asked about query on table but query on view. Hope someone know the answer. Thanks

[Fizcko]

About GROUP BY the problem is that I need to reference to the original table and in this example: group by books.name, and my question is if it is possible.

Yes it is just a normal SQL query nothing special for floor

@DatabaseView('SELECT b.id, b.name, l.location_name as location FROM books as b LEFT JOIN locations as l ON b.location = l.id GROUP BY b.name', viewName: 'bookAndMoreView')

About WHERE I did not asked about query on table but query on view.

The views works as a normal table the only difference is that you can only "read" (select)

@Query('SELECT * FROM bookAndMoreView WHERE name="SQL For Dummies"')
Future<List<BookAndMore>> getBooksAndMore();

[SEGVeenstra]

@loozyoni did the answers help you? Please let us know so we can close the issue.

[loozyoni]

@SEGVeenstra the answer is helped me partially, but finally I found the way to implement dynamic query with join. What I did is to write static query for databaseview so it will generate the class with the relevant field and than write almost the same query with additional dynamic parameters in the dao. The reason is because in the database view you can not pass parameters and in the dao you can not write join without database view, so I found the hack i wrote above. Probably it will be good if you will fix it by allowing to pass parameters to database view or by allowing to just write query in the join without database view.

Thanks

[SEGVeenstra]

@SEGVeenstra the answer is helped me partially, but finally I found the way to implement dynamic query with join. What I did is to write static query for databaseview so it will generate the class with the relevant field and than write almost the same query with additional dynamic parameters in the dao. The reason is because in the database view you can not pass parameters and in the dao you can not write join without database view, so I found the hack i wrote above. Probably it will be good if you will fix it by allowing to pass parameters to database view or by allowing to just write query in the join without database view.

Thanks

Thank you for your response.

Maybe it's good if you create a new issue for a feature request?