Hi!
I’m trying to write a query on the following data collection:
The query looks as follows:
var userProfileId = "efad6b27-d9e9-4be0-96d2-759c417851f8";
var index = 0;
var results = await wixData.query("collection")
.eq("field_N.userProfileId", userProfileId) //.eq("field_N[index].userProfileId") does not work in Velo
.descending("field_N.Match") //.descending("field_N[index].Match") does not work in Velo
.limit(12)
.find();
while(results.hasNext()) {
results = await results.next();
}
Unfortunately, I cannot obtain the expected outcome, which is a result that looks as follows:
I thought the issue lied in the fact that descending couldn’t handle the strings, but I tried it for integers as well but that didn’t work either.
Thoughts? It’s not an option to do the sorting after the query, as I want to avoid having to query all items at once.
Hey!
It seems like the issue is that you’re trying to match field_N.userProfileId, but field_N is holding arrays, not objects. So you wouldn’t be able to use the dot property selector for the array.
@Chris Derrell well it seems strange, but that’s actually the way it works in Velo… It would be great to be able to actually work with the indices in the arrays, but Velo doesn’t allow to do that.
Not sure why I’m just seeing this alert that you had responded.
You could try including the indices as a property as well, like field_N.0.userProfileId. But I’m not sure if that would help because it would actually only check the nth record, whichever index you substitute rather than accessing all matching objects.
@chris-derrell unfortunately doesn’t work.
@Velo Pro: not possible then?
@vervoortyves I would change the database structure so that you have a collection of {userProfil,Match, CollectionId}. This way you can do your query on a higher level avoiding the array search.
Could you give a bit more context about what the request is about?
@plomteuxquentin thanks for looking into this. Yes, I know, but that’s not an option.
I really cannot understand why a document-oriented database is offered while the tools provided to actually manage the database are not adapted to it. You are basically forced to work in the traditional relational database paradigm?
Context is the following: I calculate a match% between each user that is on my platform with a particular item. All these match%'s are added to a field (which contains an array of objects) in the user’s Profile. These match%'s are sorted descending. Since I have multiple members with their own match%'s, the items contain a list of userProfileId’s with their particular match%.
At a certain moment, I want to show the items to some user, sorted by match%. I cannot extract 2000 items to then filter the best matches. I want to query 12 by 12 to avoid load issues. I use the copy of Ids in my user’s profile, sorted by match%, to then query through Ids with a chain of or()'s. Initially hasSome() seemed to be an option - by feeding the 2000 Ids - but hasSome() doesn’t adhere to the sorting. See another open issue: https://www.wix.com/velo/forum/coding-with-velo/hassome-field-array-query-result-sequence
This workaround does however not work if I also want to filter on another field. I’d have to query 12 items, see which field matches, ditch those that don’t and query further until I have 12 items. Also I’d really have to query all 2000 items if I don’t reach 12 valid results. It will work, but …
Tried many things already, really cannot understand this. Looked into multi-references as well, but that’s the old paradigm all over again, and also it doesn’t work very well with objects, arrays or arrays of objects. You can’t expect me to copy the data I have in the items database in my user profile database, right?
Again thanks for looking into this, much appreciated.
I’ll figure it out… Just really annoyed by this.
@vervoortyves I don’t understand why you cannot have a flatter collection
objectsUsersMatch: {_id, object, user, match}
Query the best 12 matching objects for a user
wixData.query(COLLECTION).eq("user", userId).limit(12).descending("match").includes("object")
Query the best users for an object
wixData.query(COLLECTION).eq("object", objectId).limit(12).descending("match").includes("user")
The only condition is that your composite key (objectId+userId) should be unique therefore only one match is possible
What is preventing you from organizing your data in such a way?
@plomteuxquentin a flatter collection would result in a collection where the number of records would equals N (items) * M (users). A collection would have already 2.000.000 records in case of 2000 items and 1000 users. Or am I missing something?
Then again, for me this feels like the old paradigm. If you have object like {_id, field_a, field_b, field_c} you basically just reduce the number of columns in your database? For me it should be about data/information architecture instead - putting data/information together which actually belongs together and being able to do that as compact as possible.
I would give it a try, I’ve never work with 2 millions records but if this might work.
if it get too slow then Wix Data is not the system you are looking for. You can always use and external database or build an API on another platform to do the query and display the result on your Wix site
