firebase
[cloud_firestore]: False `Order by clause cannot contain more fields after the key`
Is there an existing issue for this?
- [X] I have searched the existing issues.
Which plugins are affected?
Database
Which platforms are affected?
Android
Description
On version 5.4.0
We are doing multiple where inequalities and a single order by clause. The first query runs okay. But on the second one it throws
PlatformException (PlatformException(firebase_firestore, com.google.firebase.firestore.FirebaseFirestoreException: INVALID_ARGUMENT: Order by clause cannot contain more fields after the key endsAt, {code: invalid-argument, message: Client specified an invalid argument. Note that this differs from failed-precondition. invalid-argument indicates arguments that are problematic regardless of the state of the system (e.g., an invalid field name).}, null))
The query:
q.where(jobPostFields.addedAt, isGreaterThanOrEqualTo: Timestamp.fromMillisecondsSinceEpoch(0))
.where(jobPostFields.endsAt, isGreaterThan: now)
.orderBy(jobPostFields.addedAt, descending: true),
According to the issue https://github.com/googleapis/nodejs-firestore/issues/1472 , the Order by clause cannot contain more fields after the key is for queries with multiple order by clauses but we only have only one order by clause.
Reproducing the issue
Add multiple documents with multiple number fields. Make a query with multiple inequalities and a single order by clause with a limit clause. On the second query do a startAfterDocument and provide the last document of the first query. And it should throw. It's theoretical though. Don't have the time to reproduce the issue in a sandbox env rn.
Firebase Core version
3.4.0
Flutter Version
3.19.6
Relevant Log Output
No response
Flutter dependencies
Expand Flutter dependencies snippet
Replace this line with the contents of your `flutter pub deps -- --style=compact`.
Additional context and comments
No response
Hi @nayeemtby , thanks for the report. I was able to reproduce this issue.
@Lyokone
Reproducible code(cloud_firestore example app)
enum MovieQuery {
year,
likesAsc,
likesDesc,
rated,
sciFi,
fantasy,
likesGreaterThanOrEqual,
likesGreaterThan,
}
extension on Query<Movie> {
/// Create a firebase query from a [MovieQuery]
Query<Movie> queryBy(MovieQuery query) {
switch (query) {
case MovieQuery.fantasy:
return where('genre', arrayContainsAny: ['fantasy']);
case MovieQuery.sciFi:
return where('genre', arrayContainsAny: ['sci-fi']);
case MovieQuery.likesGreaterThan:
return where('likes', isGreaterThan: 0);
case MovieQuery.likesGreaterThanOrEqual:
return where('likes', isGreaterThanOrEqualTo: 0);
case MovieQuery.likesAsc:
case MovieQuery.likesDesc:
return orderBy('likes', descending: query == MovieQuery.likesDesc);
case MovieQuery.year:
return orderBy('year', descending: true);
case MovieQuery.rated:
return orderBy('rated', descending: true);
}
}
}
/// The entry point of the application.
///
/// Returns a [MaterialApp].
class FirestoreExampleApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Firestore Example App',
theme: ThemeData.dark(),
home: const Scaffold(
body: Center(child: FilmList()),
),
);
}
}
class FilmList extends StatefulWidget {
const FilmList({Key? key}) : super(key: key);
@override
_FilmListState createState() => _FilmListState();
}
class _FilmListState extends State<FilmList> {
MovieQuery query1 = MovieQuery.likesGreaterThan;
MovieQuery query2 = MovieQuery.likesGreaterThanOrEqual;
MovieQuery query3 = MovieQuery.rated;
@override
void initState() {
super.initState();
reproduceIssue13289();
}
Future<void> reproduceIssue13289() async {
final firstQueryResult = await moviesRef
.queryBy(query1)
.queryBy(query2)
.queryBy(query3)
.limit(2)
.get();
if (firstQueryResult.docs.isNotEmpty) {
await moviesRef
.queryBy(query1)
.queryBy(query2)
.queryBy(query3)
.startAfterDocument(firstQueryResult.docs.last)
.get();
}
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: Column(
mainAxisSize: MainAxisSize.min,
crossAxisAlignment: CrossAxisAlignment.stretch,
children: [
const Text('Firestore Example: Movies'),
// This is a example use for 'snapshots in sync'.
// The view reflects the time of the last Firestore sync; which happens any time a field is updated.
StreamBuilder(
stream: FirebaseFirestore.instance.snapshotsInSync(),
builder: (context, _) {
return Text(
'Latest Snapshot: ${DateTime.now()}',
style: Theme.of(context).textTheme.bodySmall,
);
},
),
],
),
actions: <Widget>[
PopupMenuButton<MovieQuery>(
onSelected: (value) => setState(() => query1 = value),
icon: const Icon(Icons.sort),
itemBuilder: (BuildContext context) {
return [
const PopupMenuItem(
value: MovieQuery.year,
child: Text('Sort by Year'),
),
const PopupMenuItem(
value: MovieQuery.rated,
child: Text('Sort by Rated'),
),
const PopupMenuItem(
value: MovieQuery.likesAsc,
child: Text('Sort by Likes ascending'),
),
const PopupMenuItem(
value: MovieQuery.likesDesc,
child: Text('Sort by Likes descending'),
),
const PopupMenuItem(
value: MovieQuery.fantasy,
child: Text('Filter genre fantasy'),
),
const PopupMenuItem(
value: MovieQuery.sciFi,
child: Text('Filter genre sci-fi'),
),
];
},
),
PopupMenuButton<String>(
onSelected: (value) async {
switch (value) {
case 'reset_likes':
return _resetLikes();
case 'aggregate':
// Count the number of movies
final _count = await FirebaseFirestore.instance
.collection('firestore-example-app')
.count()
.get();
print('Count: ${_count.count}');
// Average the number of likes
final _average = await FirebaseFirestore.instance
.collection('firestore-example-app')
.aggregate(average('likes'))
.get();
print('Average: ${_average.getAverage('likes')}');
// Sum the number of likes
final _sum = await FirebaseFirestore.instance
.collection('firestore-example-app')
.aggregate(sum('likes'))
.get();
print('Sum: ${_sum.getSum('likes')}');
// In one query
final _all = await FirebaseFirestore.instance
.collection('firestore-example-app')
.aggregate(
average('likes'),
sum('likes'),
count(),
)
.get();
print('Average: ${_all.getAverage('likes')} '
'Sum: ${_all.getSum('likes')} '
'Count: ${_all.count}');
return;
case 'load_bundle':
Uint8List buffer = await loadBundleSetup(2);
LoadBundleTask task =
FirebaseFirestore.instance.loadBundle(buffer);
final list = await task.stream.toList();
print(
list.map((e) => e.totalDocuments),
);
print(
list.map((e) => e.bytesLoaded),
);
print(
list.map((e) => e.documentsLoaded),
);
print(
list.map((e) => e.totalBytes),
);
print(
list,
);
LoadBundleTaskSnapshot lastSnapshot = list.removeLast();
print(lastSnapshot.taskState);
print(
list.map((e) => e.taskState),
);
return;
default:
return;
}
},
itemBuilder: (BuildContext context) {
return [
const PopupMenuItem(
value: 'reset_likes',
child: Text('Reset like counts (WriteBatch)'),
),
const PopupMenuItem(
value: 'aggregate',
child: Text('Get aggregate data'),
),
const PopupMenuItem(
value: 'load_bundle',
child: Text('Load bundle'),
),
];
},
),
],
),
body: StreamBuilder<QuerySnapshot<Movie>>(
stream: moviesRef
.queryBy(query1)
.queryBy(query2)
.queryBy(query3)
.snapshots(),
builder: (context, snapshot) {
if (snapshot.hasError) {
return Center(
child: Text(snapshot.error.toString()),
);
}
if (!snapshot.hasData) {
return const Center(child: CircularProgressIndicator());
}
final data = snapshot.requireData;
return ListView.builder(
itemCount: data.size,
itemBuilder: (context, index) {
return _MovieItem(
data.docs[index].data(),
data.docs[index].reference,
);
},
);
},
),
);
}
Future<void> _resetLikes() async {
final movies = await moviesRef.get(
const GetOptions(
serverTimestampBehavior: ServerTimestampBehavior.previous,
),
);
WriteBatch batch = FirebaseFirestore.instance.batch();
for (final movie in movies.docs) {
batch.update(movie.reference, {'likes': 0});
}
await batch.commit();
}
}
@SelaseKay I have a similar situation.
Tested on versions 5.2.1, 5.4.2 and 5.4.4
When I have this, it works:
query
.where(CheckInRecordField.checkedOut, isEqualTo: false)
.where(CheckInRecordField.currentEndTime, isLessThanOrEqualTo: nowTimestamp)
.orderBy(CheckInRecordField.startTime, descending: true)
.limit(pageSize)
Immediately I add .limit(pageSize) after the .startAfterDocument() as seen in the code below:
query
.where(CheckInRecordField.checkedOut, isEqualTo: false)
.where(CheckInRecordField.currentEndTime, isLessThanOrEqualTo: nowTimestamp)
.orderBy(CheckInRecordField.startTime, descending: true)
.startAfterDocument(_lastPageDocument!)
.limit(pageSize)
then it complains with this error:
[cloud_firestore/invalid-argument] Order by clause cannot contain more fields after the key currentEndTime.
I am not even sorting by currentEndTime so I am not sure why it complains about that field.
I have an issue with this as well. The moment I add a startAfterDocument() clause to my query, it bombs. Why is this issue occurring? Please fix this asap!
I've been looking into this and I was able to reproduce with a slimmer example:
final collectionRef = FirebaseFirestore.instance.collection('flutter-tests');
final query1 = collectionRef
.where('number', isLessThan: 6);
final firstQueryResult = await query1.get();
if (firstQueryResult.docs.isNotEmpty) {
final last = firstQueryResult.docs.last;
final secondQuery = query1.startAfterDocument(last);
await secondQuery.get();
}
It can be fixed by adding an orderBy() on the field with the inequality field you're querying on. E.g:
// add orderBy('number')
final secondQuery = query1.orderBy('number').startAfterDocument(last);
If you could let me know if this fixes your issue?
I'll be looking at this again next week to figure out whether this is a documentation issue, the exception is coming from firebase-android-sdk.
Here is the exact same issue on the firebase python SDK: https://stackoverflow.com/questions/68898626/firestore-error-when-paginating-with-document-snapshot-and-where-clause
