Trying to retrieve two values from two different collections by sql (V 7.2.1)

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Trying to retrieve two values from two different collections by sql (V 7.2.1)

deniz
I am trying to run a query like below to query against two different
collections:

select collection1.id as collection1id, collection2.id as collection2id from
collection1 join collection2 on collection1.name = collection2.name where
collection1.name = 'dummyname';

And as a result, I am only seeing

{
        result-set=
                                {
                                        docs=
                                                [
                                                        {collection2id=1001},
                                                        {collection2id=1002},
                                                        {collection2id=1003},
                                                        {collection2id=1004},
                                                        {collection2id=1005},
                                                        {collection2id=1001},
                                                        {collection2id=1002},
                                                        {collection2id=1003},
                                                        {collection2id=1004},
                                                        {collection2id=1005},
                                                        {collection2id=1001},
                                                        {collection2id=1002},
                                                        {collection2id=1003},
                                                        {collection2id=1004},
                                                        {collection2id=1005},
                                                        {collection2id=1001},
                                                        {collection2id=1002},
                                                        {collection2id=1003},
                                                        {collection2id=1004},
                                                        {collection2id=1005},
                                                        {collection2id=1001},
                                                        {collection2id=1002},
                                                        {collection2id=1003},
                                                        {collection2id=1004},
                                                        {collection2id=1005},
                                                        {EOF=true, RESPONSE_TIME=2221}
                                                ]
                                }
}

I can understand the reason why the same docs are returned (for each
matching doc on collection1 there are 5 different docs on collection2), but
the thing I dont get is, why collection1id is not anywhere in the result
list while it is in the select statement?



-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: Trying to retrieve two values from two different collections by sql (V 7.2.1)

deniz
found out sth strange regarding this case. If i change one of the values into
sth else, and the field names are not the same any more, then i can get the
different values

so the initial query was

select *collection1.id* as collection1id, collection2.id as collection2id
from
collection1 join collection2 on collection1.name = collection2.name where
collection1.name = 'dummyname';


once i change it into

select *collection1.age* as collection1id, collection2.id as collection2id
from
collection1 join collection2 on collection1.name = collection2.name where
collection1.name = 'dummyname';

I am able to get the age from one collection and id from the second one. but
if use age for both of the collections, like id field, i am getting only one
value from one of the collections.






-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: Trying to retrieve two values from two different collections by sql (V 7.2.1)

Joel Bernstein
Joins are not currently supported with Solr SQL. We should create a ticket
through a proper exception in this  scenario.



Joel Bernstein
http://joelsolr.blogspot.com/


On Tue, Oct 16, 2018 at 10:56 PM deniz <[hidden email]> wrote:

> found out sth strange regarding this case. If i change one of the values
> into
> sth else, and the field names are not the same any more, then i can get the
> different values
>
> so the initial query was
>
> select *collection1.id* as collection1id, collection2.id as collection2id
> from
> collection1 join collection2 on collection1.name = collection2.name where
> collection1.name = 'dummyname';
>
>
> once i change it into
>
> select *collection1.age* as collection1id, collection2.id as collection2id
> from
> collection1 join collection2 on collection1.name = collection2.name where
> collection1.name = 'dummyname';
>
> I am able to get the age from one collection and id from the second one.
> but
> if use age for both of the collections, like id field, i am getting only
> one
> value from one of the collections.
>
>
>
>
>
>
> -----
> Zeki ama calismiyor... Calissa yapar...
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>
Reply | Threaded
Open this post in threaded view
|

RE: Trying to retrieve two values from two different collections by sql (V 7.2.1)

Vadim Ivanov
...but using Streaming Expressions it's possible to achieve the goal, AFAIK
https://lucene.apache.org/solr/guide/7_5/stream-decorators.html#innerjoin

Though, probably it won't be so fast as search
--
Vadim

-----Original Message-----
From: Joel Bernstein [mailto:[hidden email]]
Sent: Thursday, October 18, 2018 1:03 AM
To: [hidden email]
Subject: Re: Trying to retrieve two values from two different collections by sql (V 7.2.1)

Joins are not currently supported with Solr SQL. We should create a ticket
through a proper exception in this  scenario.



Joel Bernstein
http://joelsolr.blogspot.com/


On Tue, Oct 16, 2018 at 10:56 PM deniz <[hidden email]> wrote:

> found out sth strange regarding this case. If i change one of the values
> into
> sth else, and the field names are not the same any more, then i can get the
> different values
>
> so the initial query was
>
> select *collection1.id* as collection1id, collection2.id as collection2id
> from
> collection1 join collection2 on collection1.name = collection2.name where
> collection1.name = 'dummyname';
>
>
> once i change it into
>
> select *collection1.age* as collection1id, collection2.id as collection2id
> from
> collection1 join collection2 on collection1.name = collection2.name where
> collection1.name = 'dummyname';
>
> I am able to get the age from one collection and id from the second one.
> but
> if use age for both of the collections, like id field, i am getting only
> one
> value from one of the collections.
>
>
>
>
>
>
> -----
> Zeki ama calismiyor... Calissa yapar...
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>