Join on array of ids

[mol...@gmail.com]

I have a problem connecting two collections on SlamData. First collection is Worker and there's an _id field in it. Second one is Order and there is an array of workers id's in it(called pracownicy). My query isn't working:

SELECT worker.nazwisko,SUM(orders.odleglosc)as "suma odleglosci",SUM(orders.cena)as "suma zarobku",SUM(orders.godz)as "suma godzin" 
FROM "/test/test/Order" orders 
JOIN "/test/test/Worker" worker ON orders.pracownicy=worker._id 
GROUP BY worker.nazwisko

I know that this is wrong:

orders.pracownicy=worker._id

but i dont know how to fix it, i read about FIND_IN_SET and i tried but it didnt work neither. Im not even sure if it's implemented in slamdata.

I also tried 

orders.pracownicy[*]=worker._id

but it didnt work anyway.

Is there any other way to do that?

[jo...@slamdata.com]

This version is correct:

orders.pracownicy[*]=worker._id

That is, assuming that `praconicy` is, in fact, an array.

What error message are you getting? And can you provide a small (anonymized) snippet of data?

The query (with the array version above) looks fine, so unless I'm missing something, the structure of the data may not exactly line up to the query. That, or it's possible you've hit some edge case in the MongoDB connector for Quasar.

Regards,

John

[mol...@gmail.com]

I'm getting error in queqe message.
My data looks like this( it's fictional right now):

Workers:

Orders:

I also tried to do something like that:

SELECT *
FROM "/test/test/Order" orders 
where orders.pracownicy[*]=1001 

and that works fine.

[Damon LaCaille]

It isn't clear from you last update a few weeks ago whether or not you got this working.  If you're still having an issue please let us know, or if it's working with what John provided or you went another route, let us know that as well.

Thanks!

[Ho...@editionlingerie.com]

Hi Damon,

I'm also having the same issue and I found this post the closest to a solution. But it's not a solution.

Because the worker._id field is in the type of MongoDB Object Id, so to match it we'll need to use OID function. But the following query wouldn't work.

orders.pracownicy[OID(*)]=worker._id

OID(orders.pracownicy[*])=worker._id

Do you have other alternative to solve this issue?

Cheers,

Homer

[John A. De Goes]

Assuming that `orders.pracownicy` is an array of strings which need to be converted to OID to join on `worker._id`, then the following snippet is correct:

OID(orders.pracownicy[*])=worker._id

Does this match your scenario, and if so, what kind of error or result are you getting back?

--

John A. De Goes

jo...@degoes.net

Follow me on Twitter @jdegoes

--
You received this message because you are subscribed to a topic in the Google Groups "SlamData-User" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/slamdata-user/FfR1H23Olqc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to slamdata-use...@googlegroups.com.
Visit this group at https://groups.google.com/group/slamdata-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/d2bcccf2-a8c2-42a0-bee7-e36c2b811a36%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Homer Dong]

Hi John,

It actually worked. In my case I received the error message because my collection is not consistent regarding rows, some does have the ids array, some don't. So it led to an error.

After I added one more condition to make sure the matching would only be performed on rows with ids array. The issue was solved. Thanks a lot. So the following snippet is the right answer.

OID(orders.pracownicy[*])=worker._id

Homer Dong | CTO | +49 (0) 30 30 87 59 400 | +49 (0) 160 141 8029

Lingerie Édition GmbH | Chantelle Digital Lab

Linienstraße 144 | Berlin 10115 | Germany

Geschäftsführer: Guillaume Kretz; Alexandrine Koegel 

Handelsregister: Amtsgericht Charlottenburg, HRB 140606 

Umsatzsteuer-ID: DE284616645

Édition Lingerie

www.EditionLingerie.de

To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/AE05126A-C456-421E-B335-0BE567E53987%40degoes.net.

[John A. De Goes]

Good to hear! 

However, SlamData should never error on heterogeneous data. If you want to report the error on http://github.com/quasar-analytics/quasar, we can make sure it gets fixed.

To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/CAK0%3DA%3DeSSZ8c39p4wL8E1KKzmedx2TXOFt%2BztQbp%2BgZ-1K-R-Q%40mail.gmail.com.