How to work with multiple-item references?

vervoortyves · March 16, 2021, 9:47pm

Hi!

I’m trying to write a query which outputs following result:

This result is a combination of columns from 2 collections:

Collection_1 Collection_2

Can this be done in Wix without first having to query Collection_1 and Collection_2 to then manually create an object which has the above format? Do note that field_1 of collection_2 is a composite data type.

Normally one would write a ‘join’ statement like

SELECT column_name(s) FROM collection_1 INNER JOIN collection_2 ON collection_1.column_name =   collection_2.column_name

but I cannot seem to find the equivalent in Wix. I’ve read a couple of things about reference fields, but to me that doesn’t make sense - I just want to write 1 query which returns the structure I require. Also I don’t think it will help me in the case of composite data types.

Thanks!

marlowe-shaeffer · March 16, 2021, 11:40pm

Reference fields should serve this purpose, along with the Data API. This feature page , with links to articles and the API reference, should help you get started.

vervoortyves · March 17, 2021, 1:13am

Hi @marlowe-shaeffer . Thanks for your input on this, but I already went through all that documentation - to me it’s not clear what the best approach would be in my case.

First of all, I cannot use intermediary datasets because they do not provide enough flexibility (e.g. filtering/sorting when working with composite data types is not possible - well filtering is, but …). This means I extract my data, store them in an object at the application layer and continue from there. Hence most of the documentation is already irrelevant.

Secondly, I’d like to keep things simple. I would expect there’s a simple query I could write to call both datasets, manipulate them and store the result. From what I understand - there are two ways: either I use query/include(), or I use queryReferenced(). Seems that query/include() has its limitations though (only 50 referenced items can be returned), so I cannot use it if the result contains more than 50 rows. The latter only works for multiple-item reference fields, so somehow I need to create these multiple-item reference fields.

Knowing that, my assumption is that I should use insertReference( ), but this is something that you do on item-level, or? That’s what I understand from the API reference. Hence I need to loop over all my row to set that reference? That can’t be the case right? And how can you make it a multiple-item reference field? Like I said, I really don’t get it… It seems overly complex for a simple inner join…

So if you could look at my use case again, taken into account that I do everything in code (no datasets usage, no specific settings in the Wix editor, …) and provide me with some pseudo-code, that would help me a lot.

yoav-wix · March 17, 2021, 8:06pm

@yvervoort Wix Data is a document based database, not a relational. As such, it has advantages and disadvantages.

Relational databases are well known and great products. They are solving all problems using inner joins and transactions - as a hammer that looks for a problem. With other databases, like document databases, we focus on the specific problem at hand and try to give a dedicated solution - trading other advantages and optimizations to the inner joins and transactions of relational DBs.

With Wix Data, we have 3 (or even 4) different options -

nested objects
single references (1:N)
multi references (N:M)
Aggregate API

Depending on your need, one of the above tools will probably work. Can you share more details as to what you are trying to solve? I mean, in terms of the outcome you expect, as we need more guidance compared to just equivalent of inner join - as a document DB does not have inner joins.

One way to work with a document database is to create complex objects with nested array - you can store in the database objects like

{
  field_1: ...
  field_2: ...
  field_3: ...
  field_4: [
    {field_5: ..., field_6:...},  
    {field_5: ..., field_6:...},
    {field_5: ..., field_6:...}      
  ]
}

With nested objects like the above, you can do all kind of operations, like filter on the child objects with

wixData.query('collection')
  .eq('field_4.field_5', some-value)
  .find()

which will find any top level item that has one (or more) child elements in field_4 who has field_5 equal to the some-value.

With this approach, you have a guarantee of consistency of the whole top level item.

Another approach is to model your data with two collections using a reference field. Using a reference field, you have a N:1 relation, similar in some sense to a foreign key, yet different in other regards.

You can do query on the referencing collection, and using the .include operator include the referenced item in the result. You can query the referenced item and using the .include operator get up to 50 referencing items.

e.g.
if we have Album and Track collections with a relationship Track N : 1 Album, query of
Album include Track returns tracks as an array, up to 50 elements.
Track include Album returns the one album referenced.

Last, you can do multi-reference, which is an N:M relationship, more for cases like Picture : Color or Car Model : Car Part, etc.

With multi-reference, you have a dedicated query model for the relationship, because of the potential of both N and M to be large - using the queryReferenced APIs.

The aggregate API may also be relevant, depending on your actual need.

vervoortyves · March 18, 2021, 10:07pm

@yoav-wix , sorry but have you even read the content of my post and the point I made below when answering to @marlowe-shaeffer ? @marlowe-shaeffer this is not an answer to my question, so it’s not a ‘best answer’.

“Can you share more details as to what you are trying to solve? I mean, in terms of the outcome you expect” - Sure, please read what I described…

“With Wix Data, we have 3 (or even 4) different options” - I know that, as you can see I opt for nested objects in Collection_2. Also as you can read in my reply to @marlowe-shaeffer I don’t now how to use multi references from code to achieve the outcome I want ( see above for the expected outcome)

For the record, you say: “with nested objects like the above, you can do all kind of operations, like filter on the child objects” - unfortunately that doesn’t work if you want to access specific indices of an array, so you’ll need to query the entire object to do the filtering on the level of the application layer using .filter(function () {});

vervoortyves · March 22, 2021, 12:57pm

If anyone could shed some light on how to build a query in following format

results = await wixData.query( “…” )
.limit( 12 )
.find();
while (results.hasNext()) {
results = await results.next();
}

that outputs following table, that would be great:

I really cannot figure out how to properly use insertReference( ) for my entire database content from code .

Basically, I want to avoid having to query 12 items by chaining or statements like in below prototype. It works fine, but this shouldn’t be the way to do this.

results = await wixData.query("Collection_2")
    .eq("_id", "e682d2b8-5411-4d61-92aa-ef371e58f8b2") //some _id in collection_2
    .find()
    console.log("results (1):")
    console.log(results._items[0].Field_1);

 //sort by match%
    results._items[0].Field_1.sort((a, b) => (
        parseFloat(b.Match)/100.0 > parseFloat(a.Match)/100.0
      ) ? 1 : -1) //descending - empty ones come first (ensure this doesn't happen)

    console.log("results (2):")
    console.log(results._items[0].Field_1);

 //query 12 items
    query = await wixData.query("Collection_1")
    .eq("_id", results._items[0].Field_1[0].collectionId)
    .or(
        wixData.query("Collection_1")
        .eq("_id", results._items[0].Field_1[1].collectionId)
        .or(
            wixData.query("Collection_1")
            .eq("_id", results._items[0].Field_1[2].collectionId)
            .or(
                wixData.query("Collection_1")
                .eq("_id", results._items[0].Field_1[3].collectionId)
                .or(
                    wixData.query("Collection_1")
                    .eq("_id", results._items[0].Field_1[4].collectionId)
                    .or(
                        wixData.query("Collection_1")
                        .eq("_id", results._items[0].Field_1[5].collectionId)
                        .or(
                            wixData.query("Collection_1")
                            .eq("_id", results._items[0].Field_1[6].collectionId)
                            .or(
                                wixData.query("Collection_1")
                                .eq("_id", results._items[0].Field_1[7].collectionId)
                                .or(
                                    wixData.query("Collection_1")
                                    .eq("_id", results._items[0].Field_1[8].collectionId)
                                    .or(
                                        wixData.query("Collection_1")
                                        .eq("_id", results._items[0].Field_1[9].collectionId)
                                        .or(
                                            wixData.query("Collection_1")
                                            .eq("_id", results._items[0].Field_1[10].collectionId)
                                            .or(
                                                wixData.query("Collection_1")
                                                .eq("_id", results._items[0].Field_1[11].collectionId)
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
    .find()

    console.log("results (3):")
    console.log(query);

vervoortyves · April 28, 2021, 10:23am

This is still an open question, would appreciate some input…

giedrius-grazevicius · April 28, 2021, 5:41pm

Hi @vervoortyves ,

as Wix Data is a Document Store. As is typical to this type of databases, we don’t really provide ability to do arbitrary joins. As @yoav-wix has explained, join operation as such is only possible on properly defined relationships (references).

But in your case it can be done using two queries, as you are already doing. I would optimize your solution a bit to (logs removed for brevity):

results = await wixData.query("Collection_2").eq("_id", "e682d2b8-5411-4d61-92aa-ef371e58f8b2") //some _id in collection_2.find()

results._items[0].Field_1.sort((a, b) => (parseFloat(b.Match)/100.0 > parseFloat(a.Match)/100.0) ? 1 : -1) //descending - empty ones come first (ensure this doesn't happen)

// Take all ids to match
const idsToMatch = results._items[0].Field_1.map(f => f.collectionId);

query = await wixData.query("Collection_1").hasSome("_id", idsToMatch).find()

hasSome is a short way of saying "match if fields holds one of the values in the supplied array).

Regarding filtering by indexes, it is possible.

If you want to find element in Collection_2 that has specific collectionId, you can query

wixData.query("Collection_2")
  .eq("Field_1.collectionId", "some id")
  .find()

This will check all objects in Field_1 array and will match if at least one of them contain “some id”.

And you could do a full two query “join” by

const collection1 = await wixData.query("Collection_1")
  // filters omited
  .find()

const collection2 = await wixData.query("Collection_2")
  .hasSome("Field_1.collectionId", collection1.items.map(item => item._id))
  .find();

You could even combine the results with some magic of lodash.

const collection1ById = _.keyBy(collection1.items, item => item._id);

const combined = collection2.items.map(
  item => item.Field_1.map(
    field => Object.assign({}, collection1Mapped[field.collectionId], field})
  )
);

And further flatten to get the table:

const flat = _.flatten(combined)

You can adjust what goes into Object.assign(…) to get the exact mapping with the fields you need.

Just don’t forget to include lodash with

import _ from 'lodash'

I hope this helps.

CODE-NINJA · April 28, 2021, 5:43pm

@giedrius-grazevicius
Thumbs up! Good one!

vervoortyves · April 28, 2021, 6:16pm

@giedrius-grazevicius thank you very much for this! This is exactly the “inner join equivalent” I was looking for.

vervoortyves · May 17, 2021, 10:24am

@giedrius-grazevicius although you did help me to a certain extent, I do have some issues with the fact that hasSome does not adhere to the sequence of the elements that are stored in the array. Is there some way to force that? My use case does not allow me to sort after the query. I want the elements to be returned in the right order so that I can query in steps e.g. per 12 items. Opened a separate topic https://www.wix.com/velo/forum/coding-with-velo/have-hassome-field-array-adhere-to-the-sequence-of-elements-in-array

giedrius-grazevicius · May 17, 2021, 4:02pm

Currently, there is no way to enforce the order.

API will always apply some sort, of no sort is supplied, it will sort by _createdDate.

Topic		Replies	Views
[wix-data] Multiple References - another post :) Ask the community code , question	3	491	September 7, 2020
Showing tables with several multi-reference fields Ask the community code , question	1	225	February 18, 2021
Collection with multiple referenced items not querying Ask the community code , question	1	96	October 25, 2019
Query using a Multi-reference Field Ask the community code , question	3	3048	March 11, 2019
Have not seen any tutorials on multiple item reference fields since their release. In particular setting up searches of these fields Ask the community code , question	4	170	January 14, 2019

How to work with multiple-item references?

Related topics