Need help resolving firebase query

Question

My node backend is connected to Firestore. I need to perform this MySQL equivalent query:

SELECT * FROM offer WHERE zip = '..' AND category = '...' AND status != 1 ORDER BY ..

So far I tried below Firebase query:

const docRef = db.collection('ads');
await docRef.where('status', '<', 4).where('status', '>', 5).orderBy('status')
.where('zip', '==', searchTerm).where('subCategory', '==', subCategory).orderBy('createdAt')

This query returns empty array. I'm fairly new to Firebase.

Your query is checking for status less than 4 and greater than 5. Based on your MySQL query I think you want `where('status', '!=', 1)` — jmalenfant, Dec 17 '19 at 23:47
@jmalenfant Firebase does not have an inequality (`'<>'` or `'!='`) [operator](https://firebase.google.com/docs/firestore/query-data/queries#query_operators) that can be used in `where(...)`. — samthecodingman, Dec 18 '19 at 00:14
@samthecodingman Odd. He could just use `.query()` and use SQL then, correct? — jmalenfant, Dec 18 '19 at 00:36
To keep Firestore queries performant, they just aren't supported. Take a look at [this answer](https://stackoverflow.com/a/47252340). — samthecodingman, Dec 18 '19 at 00:45

score 0 · Answer 1 · answered Dec 18 '19 at 00:33

Firstly, a CollectionReference (from db.collection(...)) should be named colRef or similar. While similar to a DocumentReference, they work differently and mean different things.

Your query defined by this line, says "find documents where status must be less than 4 AND greater than 5 AND sort them by their value of status".

colRef.where('status', '<', 4).where('status', '>', 5).orderBy('status')

Because the value of status cannot be both larger than 5 and smaller than 4, you get no results.

If you wanted to translate your SQL query, you would use:

colRef.where('status', '>', 1).orderBy('status')
.where('zip', '==', searchTerm).where('subCategory', '==', subCategory).orderBy('createdAt');

This assumes that status is never less than 1. If it is, you will also need a second, separate query:

colRef.where('status', '<', 1).orderBy('status')
.where('zip', '==', searchTerm).where('subCategory', '==', subCategory).orderBy('createdAt')

Need help resolving firebase query

1 Answers1