Solr and inner joins1

["Antony Corfield [awc]" <aw...@aber.ac.uk>]

Hi,

I have a solr core to which I can add simple documents (essentially these consist of a triple: resource URL, referrer URL and timestamp) that provide information on activity data for open-access repositories. 

Replicating this in a SQL database I can retrieve other resource URLs (for a given resource URL) that have been viewed or downloaded within a specified time frame (e.g. 10 minutes) by the using referrer URLs.

The SQL query would involve an inner join e.g.

select distinct a1.resource_url from activity a1, activity a2 where a1.referrer=a2.referrer and a1.resource_url <> a2.resource_url and a2.resource_url ='http://resource/1' and (a2.timestamp, interval '00:10') overlaps (a1.timestamp, interval '00:10');

My question(s): Can this be represented by a solr query? Would it be possible using nested queries or would I need to filter a result set and/or perform a second solr query? Or should I simply use an sql database for this type of query..

Any help or guidance would be much appreciated.
 

Thanks,
Antony
--
Antony Corfield
Project developer
AEIOU Project
Tel. 01970 628724
http://aeiouproject.blogspot.com/

Re: Solr and inner joins1

[Otis Gospodnetic <ot...@yahoo.com>]

Hi Antony,

Check out hit #1: http://search-lucene.com/?q=solr+join

Otis
----
Sematext :: http://sematext.com/ :: Solr - Lucene - Nutch
Lucene ecosystem search :: http://search-lucene.com/



----- Original Message ----
> From: Antony Corfield [awc] <aw...@aber.ac.uk>
> To: "solr-user@lucene.apache.org" <so...@lucene.apache.org>
> Sent: Thu, April 14, 2011 6:56:21 AM
> Subject: Solr and inner joins1
> 
> Hi,
> 
> I have a solr core to which I can add simple documents (essentially  these 
>consist of a triple: resource URL, referrer URL and timestamp) that  provide 
>information on activity data for open-access repositories. 
>
> 
> Replicating this in a SQL database I can retrieve other resource URLs  (for a 
>given resource URL) that have been viewed or downloaded within a  specified time 
>frame (e.g. 10 minutes) by the using referrer URLs.
> 
> The  SQL query would involve an inner join e.g.
> 
> select distinct  a1.resource_url from activity a1, activity a2 where 
>a1.referrer=a2.referrer and  a1.resource_url <> a2.resource_url and 
>a2.resource_url ='http://resource/1' and  (a2.timestamp, interval '00:10') 
>overlaps (a1.timestamp, interval  '00:10');
> 
> My question(s): Can this be represented by a solr query? Would  it be possible 
>using nested queries or would I need to filter a result set  and/or perform a 
>second solr query? Or should I simply use an sql database for  this type of 
>query..
> 
> Any help or guidance would be much appreciated.
> 
> 
> Thanks,
> Antony
> --
> Antony Corfield
> Project developer
> AEIOU  Project
> Tel. 01970 628724
> http://aeiouproject.blogspot.com/ 
> 
> 
>