TLDR;
You can easily use a multiref field in a WHERE clause. But it seems nearly impossible to use a multiref field in a SELECT clause. How do people work around this limitation?
(I’m going to put some things in SQL terms, just for clarity…)
I have one…let’s call it…“master” table, which, in addition to containing regular fields, also contains 7 columns that join to reference tables.
3 of those fields are single refs
4 of those fields are multi refs
On the dynamic (All) page, I would like to SELECT (not filter!) reference fields as well as first class fields. This is so I can show some info in the repeater.
Here is a concrete example:
Master table:
Plant
SingleRef: Classification (perennial, annual, etc)
SingleRef: …others…these are not the problem, so I’ll stop here
Multiref: Butterfly (does this plant attract butterflies? options are adults, larvae, or both)
Multiref: Color (what color(s) is the plant’s flower? Plant can have more than one color, and options are pink, purple, red, etc)
I have a dynamic page for the Plant collection. On the dynamic (All) page, I would like to display the attributes of a plant in the repeater. Let’s just take Butterfly for example.
If there is anything in the butterfly column, display something in the repeater. ie, if a plant attracts butterflies of any kind, put a little butterfly icon in the repeater row. So basically, treat the multiref like a boolean.
For other multiref columns, I’d like to display the titles for the elements.
Example: for color, display a string like “pink, purple”. This is just a list join on title.
This is for SELECTing, not for use in a WHERE clause . The WHERE (filters) are working fine. It’s actually very easy to filter a dataset by its references.
This is for the (All) page, not for the (Title) page . It seems impossible to DISPLAY multiref fields in the (All) page. In the (Title) page (plants/{title}), it is easy to display referenced fields, because you can add datasets to the referenced collections that are filtered by the main dataset. This is not the problem. The problem is displaying anything from a multiref column in the (All) page .
Here are the approaches I have considered/tried.
- Use router hooks. This doc describes how you can use the router to modify the query on a dynamic page. I used the customizeQuery hook to modify the query like this:
return query.included = [“butterfly”];
When I log the query, I do see that that includes contains butterfly, but the data IS NOT in the result . The docs on include and queryReference are really confusing. There is a table describing when to use each one, but I’m afraid that table makes no sense. For example, I have absolutely no idea what this sentence is actually saying: " Can reference one item that references multiple items in one query ". Some examples would really help here. The docs for include say you can only use include on one multiref column . I am only adding ONE multiref column as an include in my query, so this should work. Other forum conversations I’ve read state/imply that include only works if the COLLECTION contains only one multiref column. I don’t understand that…
This solution is not optimal, because of the limitation that you can only include one referenced field. I have 2 that I want to display. Also, it doesn’t work . If I could get this to work, I would just live with only displaying ONE multiref, and not the 2 that I would like to display.
UPDATE: This does work, but only sometimes…and only sort of…and it seems REALLY REALLY REALLY REALLY BUGGY.
For some bizarre reason, when I print out the query in the router (but do nothing to modify it), it automatically includes a DIFFERENT reference…and that reference is a single reference. Not a multiref. Here is what it shows when I console.log the query in router.js:
included: [“water”]
I HAVE ABSOLUTELY NO IDEA WHY THAT REFERENCE IS IN THE QUERY. I DID NOT CONSCIOUSLY PUT IT THERE.
In the router customizeQuery, if I replace it with the multiref I want, and for that one instance, it works. HOWEVER, my search page performs filters on the dataset, and then reloads the data. When this happens, the included that I did in the router is NO LONGER THERE. It has been replaced with the mystery random included ref.
I really really really really really don’t understand what could be putting the random reference in the include…why it is including a single ref, which you don’t need to explicitly include, since single refs are returned anyway…also, when my dataset does its filter, the include that I set in the router is gone. Anyway, this is random, weird, stupid, and buggy enough that there is no way I can use it.
- Use onInsert/onUpdate on the master collection to “flatten” its reference fields into a new column. I have mulled over the idea of adding another column to my master table JUST to hold reference data info for display…you can create a column of type Object, which is a JSON blob. I could do something like:
referenceData: { butterfly: true, color: [ “pink._id”, “purple._id” ] }
So…on Insert/Update, I could do a queryReference on all the reference fields and put them in a big JSON blob.
This seems really hacky and gross, but I’ll do it if I cannot find a more elegant/supported/robust solution.
-
Use onInsert/onUpdate on the master collection to “flatten” its reference fields into a new SingleRef. This solution involves creating one more Collection. Something like PlantRef, which contains 2 columns. a reference to the plant, and an Object/JSON blob as described in the previous approach. The Plant collection would have a single ref to the new table, and thus, you could actually include its values in the result. This would just save me from having to pollute my master table. But it does add a little more complexity, and possibly (probably) costs more in terms of performance.
-
Create a separate table that is used like a search index. It is completely flattened. This is unnecessary, because…as I said…searching is easy. It’s displaying the results that’s a pain in the butt.
-
do a queryReference at runtime in the repeater. This just makes me squeamish. To do this in a repeater will likely have severe performance issues.
I read on the Velo Power User forum that multirefs were introduced for non-devs, and that developers should not use them. But the author gives no suggestion about what we should use instead? Is this author suggesting that we create separate JOIN tables and manage them manually? Is he suggesting that we use Objects and JSON blobs instead?
Using reference fields has a lot of benefits. Filtering becomes simple. You can more easily/automatically enforce having known “enum” values in columns. If reference fields really aren’t for devs, then WHAT IS?
What are people’s strategies for displaying multiref data in a repeater?