You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by "Peng, Wei" <We...@XEROX.COM> on 2010/06/28 18:19:01 UTC
solr data config questions
Hi All,
I am a new user of Solr.
We are now trying to enable searching on Digg dataset.
It has story_id as the primary key and comment_id are the comment id
which commented story_id, so story_id and comment_id is one-to-many
relationship.
These comment_ids can be replied by some repliers, so comment_id and
repliers are one-to-many relationship.
The problem is that within a single returned document the search results
shows an array of comment_ids and an array of repliers without knowing
which repliers replied which comment.
For example: now we got comment_id:[c1,c,2...,cn],
repliers:[r1,r2,r3....rm]. Can we get something like
comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
{r1,r2} is corresponding to c1?
Our current data-config is attached:
<dataConfig>
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
autoreconnect="true" netTimeoutForStreamingResults="1200"
url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
password=" "/>
<document>
<entity name="story" pk="story_id" query="select * from
story"
deltaImportQuery="select * from story where
ID=='${dataimporter.delta.story_id}'"
deltaQuery="select story_id from story where
last_modified > '${dataimporter.last_index_time}'">
<field column="link" name="link" />
<field column="title" name="title" />
<field column="description" name="story_content" />
<field column="digg" name="positiveness" />
<field column="comment" name="spreading_number" />
<field column="user_id" name="author" />
<field column="profile_view" name="user_popularity" />
<field column="topic" name="topic" />
<field column="timestamp" name="timestamp" />
<entity name="dugg_list" pk="story_id"
query="select * from dugg_list where
story_id='${story.story_id}'"
deltaQuery="select SID from dugg_list where
last_modified > '${dataimporter.last_index_time}'"
parentDeltaQuery="select story_id from story where
story_id=${dugg_list.story_id}">
<field name="viewer" column="dugger" />
</entity>
<entity name="commenttable" pk="comment_id"
query="select * from commenttable where
story_id='${story.story_id}'"
deltaQuery="select SID from commenttable where
last_modified > '${dataimporter.last_index_time}'"
parentDeltaQuery="select story_id from story where
story_id=${commenttable.story_id}">
<field name="comment_id" column="comment_id" />
<field name="spreading_user" column="replier" />
<field name="comment_positiveness" column="up" />
<field name="comment_negativeness" column="down" />
<field name="user_comment" column="content" />
<field name="user_comment_timestamp"
column="timestamp" />
<entity name="replytable"
query="select * from replytable where
comment_id='${commenttable.comment_id}'"
deltaQuery="select SID from replytable where
last_modified > '${dataimporter.last_index_time}'"
parentDeltaQuery="select comment_id from
commenttable where comment_id=${replytable.comment_id}">
<field name="replier_id" column="replier_id" />
<field name="reply_content" column="content" />
<field name="reply_positiveness" column="up" />
<field name="reply_negativeness" column="down" />
<field name="reply_timestamp" column="timestamp" />
</entity>
</entity>
</entity>
</document>
</dataConfig>
Please help me on this.
Many thanks
Vivian
RE: solr data config questions
Posted by "Peng, Wei" <We...@xerox.com>.
Thank you for your answer, Alex.
I tried it, but I got some weird output "commentreply":["[B@1b06a21","[B@107dcfe","[B@13dcd27","[B@67cd84","[B@e5ace9","[B@bb05de","[B@7e56....
It is supposed to be "commentreply":["234234,2","87979,343",...
Both comment_id and reply_id are integers. Concat should be able to combine them into string.
Vivian
-----Original Message-----
From: Alexey Serba [mailto:aserba@gmail.com]
Sent: Monday, June 28, 2010 4:41 PM
To: solr-user@lucene.apache.org
Subject: Re: solr data config questions
Hi,
You can add additional commentreplyjoin entity to story entity, i.e.
<entity name="story" ...
...
<entity name="commenttable" ...
...
<entity name="replytable" ...>
...
</entity>
</entity>
<entity name="commentreplyjoin" query="select concat(comment_id,
',', replier_id) as commentreply from commenttable left join
replytable on replytable.comment_id=commenttable.comment_id where
commenttable.story_id=${story.story_id}'">
<field name="commentreply" column="commentreply" />
</entity>
</entity>
Thus, you will have multivalued field commentreply that contains list
of related "comment_id, reply_id" ("comment_id," if you don't have any
related replies for this entry) pairs. You can retrieve all values of
that field and process on a client and build complex data structure.
HTH,
Alex
On Mon, Jun 28, 2010 at 8:19 PM, Peng, Wei <We...@xerox.com> wrote:
> Hi All,
>
>
>
> I am a new user of Solr.
>
> We are now trying to enable searching on Digg dataset.
>
> It has story_id as the primary key and comment_id are the comment id
> which commented story_id, so story_id and comment_id is one-to-many
> relationship.
>
> These comment_ids can be replied by some repliers, so comment_id and
> repliers are one-to-many relationship.
>
>
>
> The problem is that within a single returned document the search results
> shows an array of comment_ids and an array of repliers without knowing
> which repliers replied which comment.
>
> For example: now we got comment_id:[c1,c,2...,cn],
> repliers:[r1,r2,r3....rm]. Can we get something like
> comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
> {r1,r2} is corresponding to c1?
>
>
>
> Our current data-config is attached:
>
> <dataConfig>
>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> autoreconnect="true" netTimeoutForStreamingResults="1200"
> url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
> password=" "/>
>
> <document>
>
> <entity name="story" pk="story_id" query="select * from
> story"
>
> deltaImportQuery="select * from story where
> ID=='${dataimporter.delta.story_id}'"
>
> deltaQuery="select story_id from story where
> last_modified > '${dataimporter.last_index_time}'">
>
>
>
> <field column="link" name="link" />
>
> <field column="title" name="title" />
>
> <field column="description" name="story_content" />
>
> <field column="digg" name="positiveness" />
>
> <field column="comment" name="spreading_number" />
>
> <field column="user_id" name="author" />
>
> <field column="profile_view" name="user_popularity" />
>
> <field column="topic" name="topic" />
>
> <field column="timestamp" name="timestamp" />
>
>
>
> <entity name="dugg_list" pk="story_id"
>
> query="select * from dugg_list where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from dugg_list where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${dugg_list.story_id}">
>
> <field name="viewer" column="dugger" />
>
> </entity>
>
>
>
> <entity name="commenttable" pk="comment_id"
>
> query="select * from commenttable where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from commenttable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${commenttable.story_id}">
>
> <field name="comment_id" column="comment_id" />
>
> <field name="spreading_user" column="replier" />
>
> <field name="comment_positiveness" column="up" />
>
> <field name="comment_negativeness" column="down" />
>
> <field name="user_comment" column="content" />
>
> <field name="user_comment_timestamp"
> column="timestamp" />
>
>
>
>
>
> <entity name="replytable"
>
> query="select * from replytable where
> comment_id='${commenttable.comment_id}'"
>
> deltaQuery="select SID from replytable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select comment_id from
> commenttable where comment_id=${replytable.comment_id}">
>
> <field name="replier_id" column="replier_id" />
>
> <field name="reply_content" column="content" />
>
> <field name="reply_positiveness" column="up" />
>
> <field name="reply_negativeness" column="down" />
>
> <field name="reply_timestamp" column="timestamp" />
>
> </entity>
>
>
>
> </entity>
>
> </entity>
>
> </document>
>
> </dataConfig>
>
>
>
> Please help me on this.
>
> Many thanks
>
>
>
> Vivian
>
>
>
>
>
>
>
>
RE: solr data config questions
Posted by "Peng, Wei" <We...@xerox.com>.
I tried query="select cast(concat(replytable.comment_id,',', replytable.SID) as char)", it works now !
Thanks you, Alex :)
Vivian
-----Original Message-----
From: Alexey Serba [mailto:aserba@gmail.com]
Sent: Tuesday, June 29, 2010 4:38 PM
To: solr-user@lucene.apache.org
Subject: Re: solr data config questions
It's weird. I tried and it works for me.
1) Try to add convertType="true" to JdbcDataSource definition
See http://wiki.apache.org/solr/DataImportHandlerFaq#Blob_values_in_my_table_are_added_to_the_Solr_document_as_object_strings_like_B.401f23c5
2) Try to apply cast operation to whole result, i.e.
cast(concat(replytable.comment_id, ',', replytable.SID) as char) as
commentreply
HTH,
Alex
On Tue, Jun 29, 2010 at 10:08 PM, Peng, Wei <We...@xerox.com> wrote:
> I tried query="select concat(cast(replytable.comment_id as char), ',', cast(replytable.SID as char)) as commentreply from commenttable right join replytable on replytable.comment_id=commenttable.comment_id where commenttable.story_id='${story.story_id}'" too, but I still got strange characters
> "commentreply":["[B@66e23a","[B@8e5225","[B@1b308c1","[B@103f345"],
>
> I use the same query on mysql database, it returns right results.
>
> Can someone answer me this ?
>
> Many Thanks
>
> Vivian
>
> -----Original Message-----
> From: Alexey Serba [mailto:aserba@gmail.com]
> Sent: Monday, June 28, 2010 4:41 PM
> To: solr-user@lucene.apache.org
> Subject: Re: solr data config questions
>
> Hi,
>
> You can add additional commentreplyjoin entity to story entity, i.e.
>
> <entity name="story" ...
> ...
> <entity name="commenttable" ...
> ...
> <entity name="replytable" ...>
> ...
> </entity>
> </entity>
>
> <entity name="commentreplyjoin" query="select concat(comment_id,
> ',', replier_id) as commentreply from commenttable left join
> replytable on replytable.comment_id=commenttable.comment_id where
> commenttable.story_id=${story.story_id}'">
> <field name="commentreply" column="commentreply" />
> </entity>
> </entity>
>
> Thus, you will have multivalued field commentreply that contains list
> of related "comment_id, reply_id" ("comment_id," if you don't have any
> related replies for this entry) pairs. You can retrieve all values of
> that field and process on a client and build complex data structure.
>
> HTH,
> Alex
>
> On Mon, Jun 28, 2010 at 8:19 PM, Peng, Wei <We...@xerox.com> wrote:
>> Hi All,
>>
>>
>>
>> I am a new user of Solr.
>>
>> We are now trying to enable searching on Digg dataset.
>>
>> It has story_id as the primary key and comment_id are the comment id
>> which commented story_id, so story_id and comment_id is one-to-many
>> relationship.
>>
>> These comment_ids can be replied by some repliers, so comment_id and
>> repliers are one-to-many relationship.
>>
>>
>>
>> The problem is that within a single returned document the search results
>> shows an array of comment_ids and an array of repliers without knowing
>> which repliers replied which comment.
>>
>> For example: now we got comment_id:[c1,c,2...,cn],
>> repliers:[r1,r2,r3....rm]. Can we get something like
>> comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
>> {r1,r2} is corresponding to c1?
>>
>>
>>
>> Our current data-config is attached:
>>
>> <dataConfig>
>>
>> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
>> autoreconnect="true" netTimeoutForStreamingResults="1200"
>> url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
>> password=" "/>
>>
>> <document>
>>
>> <entity name="story" pk="story_id" query="select * from
>> story"
>>
>> deltaImportQuery="select * from story where
>> ID=='${dataimporter.delta.story_id}'"
>>
>> deltaQuery="select story_id from story where
>> last_modified > '${dataimporter.last_index_time}'">
>>
>>
>>
>> <field column="link" name="link" />
>>
>> <field column="title" name="title" />
>>
>> <field column="description" name="story_content" />
>>
>> <field column="digg" name="positiveness" />
>>
>> <field column="comment" name="spreading_number" />
>>
>> <field column="user_id" name="author" />
>>
>> <field column="profile_view" name="user_popularity" />
>>
>> <field column="topic" name="topic" />
>>
>> <field column="timestamp" name="timestamp" />
>>
>>
>>
>> <entity name="dugg_list" pk="story_id"
>>
>> query="select * from dugg_list where
>> story_id='${story.story_id}'"
>>
>> deltaQuery="select SID from dugg_list where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select story_id from story where
>> story_id=${dugg_list.story_id}">
>>
>> <field name="viewer" column="dugger" />
>>
>> </entity>
>>
>>
>>
>> <entity name="commenttable" pk="comment_id"
>>
>> query="select * from commenttable where
>> story_id='${story.story_id}'"
>>
>> deltaQuery="select SID from commenttable where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select story_id from story where
>> story_id=${commenttable.story_id}">
>>
>> <field name="comment_id" column="comment_id" />
>>
>> <field name="spreading_user" column="replier" />
>>
>> <field name="comment_positiveness" column="up" />
>>
>> <field name="comment_negativeness" column="down" />
>>
>> <field name="user_comment" column="content" />
>>
>> <field name="user_comment_timestamp"
>> column="timestamp" />
>>
>>
>>
>>
>>
>> <entity name="replytable"
>>
>> query="select * from replytable where
>> comment_id='${commenttable.comment_id}'"
>>
>> deltaQuery="select SID from replytable where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select comment_id from
>> commenttable where comment_id=${replytable.comment_id}">
>>
>> <field name="replier_id" column="replier_id" />
>>
>> <field name="reply_content" column="content" />
>>
>> <field name="reply_positiveness" column="up" />
>>
>> <field name="reply_negativeness" column="down" />
>>
>> <field name="reply_timestamp" column="timestamp" />
>>
>> </entity>
>>
>>
>>
>> </entity>
>>
>> </entity>
>>
>> </document>
>>
>> </dataConfig>
>>
>>
>>
>> Please help me on this.
>>
>> Many thanks
>>
>>
>>
>> Vivian
>>
>>
>>
>>
>>
>>
>>
>>
>
Re: solr data config questions
Posted by Alexey Serba <as...@gmail.com>.
It's weird. I tried and it works for me.
1) Try to add convertType="true" to JdbcDataSource definition
See http://wiki.apache.org/solr/DataImportHandlerFaq#Blob_values_in_my_table_are_added_to_the_Solr_document_as_object_strings_like_B.401f23c5
2) Try to apply cast operation to whole result, i.e.
cast(concat(replytable.comment_id, ',', replytable.SID) as char) as
commentreply
HTH,
Alex
On Tue, Jun 29, 2010 at 10:08 PM, Peng, Wei <We...@xerox.com> wrote:
> I tried query="select concat(cast(replytable.comment_id as char), ',', cast(replytable.SID as char)) as commentreply from commenttable right join replytable on replytable.comment_id=commenttable.comment_id where commenttable.story_id='${story.story_id}'" too, but I still got strange characters
> "commentreply":["[B@66e23a","[B@8e5225","[B@1b308c1","[B@103f345"],
>
> I use the same query on mysql database, it returns right results.
>
> Can someone answer me this ?
>
> Many Thanks
>
> Vivian
>
> -----Original Message-----
> From: Alexey Serba [mailto:aserba@gmail.com]
> Sent: Monday, June 28, 2010 4:41 PM
> To: solr-user@lucene.apache.org
> Subject: Re: solr data config questions
>
> Hi,
>
> You can add additional commentreplyjoin entity to story entity, i.e.
>
> <entity name="story" ...
> ...
> <entity name="commenttable" ...
> ...
> <entity name="replytable" ...>
> ...
> </entity>
> </entity>
>
> <entity name="commentreplyjoin" query="select concat(comment_id,
> ',', replier_id) as commentreply from commenttable left join
> replytable on replytable.comment_id=commenttable.comment_id where
> commenttable.story_id=${story.story_id}'">
> <field name="commentreply" column="commentreply" />
> </entity>
> </entity>
>
> Thus, you will have multivalued field commentreply that contains list
> of related "comment_id, reply_id" ("comment_id," if you don't have any
> related replies for this entry) pairs. You can retrieve all values of
> that field and process on a client and build complex data structure.
>
> HTH,
> Alex
>
> On Mon, Jun 28, 2010 at 8:19 PM, Peng, Wei <We...@xerox.com> wrote:
>> Hi All,
>>
>>
>>
>> I am a new user of Solr.
>>
>> We are now trying to enable searching on Digg dataset.
>>
>> It has story_id as the primary key and comment_id are the comment id
>> which commented story_id, so story_id and comment_id is one-to-many
>> relationship.
>>
>> These comment_ids can be replied by some repliers, so comment_id and
>> repliers are one-to-many relationship.
>>
>>
>>
>> The problem is that within a single returned document the search results
>> shows an array of comment_ids and an array of repliers without knowing
>> which repliers replied which comment.
>>
>> For example: now we got comment_id:[c1,c,2...,cn],
>> repliers:[r1,r2,r3....rm]. Can we get something like
>> comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
>> {r1,r2} is corresponding to c1?
>>
>>
>>
>> Our current data-config is attached:
>>
>> <dataConfig>
>>
>> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
>> autoreconnect="true" netTimeoutForStreamingResults="1200"
>> url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
>> password=" "/>
>>
>> <document>
>>
>> <entity name="story" pk="story_id" query="select * from
>> story"
>>
>> deltaImportQuery="select * from story where
>> ID=='${dataimporter.delta.story_id}'"
>>
>> deltaQuery="select story_id from story where
>> last_modified > '${dataimporter.last_index_time}'">
>>
>>
>>
>> <field column="link" name="link" />
>>
>> <field column="title" name="title" />
>>
>> <field column="description" name="story_content" />
>>
>> <field column="digg" name="positiveness" />
>>
>> <field column="comment" name="spreading_number" />
>>
>> <field column="user_id" name="author" />
>>
>> <field column="profile_view" name="user_popularity" />
>>
>> <field column="topic" name="topic" />
>>
>> <field column="timestamp" name="timestamp" />
>>
>>
>>
>> <entity name="dugg_list" pk="story_id"
>>
>> query="select * from dugg_list where
>> story_id='${story.story_id}'"
>>
>> deltaQuery="select SID from dugg_list where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select story_id from story where
>> story_id=${dugg_list.story_id}">
>>
>> <field name="viewer" column="dugger" />
>>
>> </entity>
>>
>>
>>
>> <entity name="commenttable" pk="comment_id"
>>
>> query="select * from commenttable where
>> story_id='${story.story_id}'"
>>
>> deltaQuery="select SID from commenttable where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select story_id from story where
>> story_id=${commenttable.story_id}">
>>
>> <field name="comment_id" column="comment_id" />
>>
>> <field name="spreading_user" column="replier" />
>>
>> <field name="comment_positiveness" column="up" />
>>
>> <field name="comment_negativeness" column="down" />
>>
>> <field name="user_comment" column="content" />
>>
>> <field name="user_comment_timestamp"
>> column="timestamp" />
>>
>>
>>
>>
>>
>> <entity name="replytable"
>>
>> query="select * from replytable where
>> comment_id='${commenttable.comment_id}'"
>>
>> deltaQuery="select SID from replytable where
>> last_modified > '${dataimporter.last_index_time}'"
>>
>> parentDeltaQuery="select comment_id from
>> commenttable where comment_id=${replytable.comment_id}">
>>
>> <field name="replier_id" column="replier_id" />
>>
>> <field name="reply_content" column="content" />
>>
>> <field name="reply_positiveness" column="up" />
>>
>> <field name="reply_negativeness" column="down" />
>>
>> <field name="reply_timestamp" column="timestamp" />
>>
>> </entity>
>>
>>
>>
>> </entity>
>>
>> </entity>
>>
>> </document>
>>
>> </dataConfig>
>>
>>
>>
>> Please help me on this.
>>
>> Many thanks
>>
>>
>>
>> Vivian
>>
>>
>>
>>
>>
>>
>>
>>
>
RE: solr data config questions
Posted by "Peng, Wei" <We...@xerox.com>.
I tried query="select concat(cast(replytable.comment_id as char), ',', cast(replytable.SID as char)) as commentreply from commenttable right join replytable on replytable.comment_id=commenttable.comment_id where commenttable.story_id='${story.story_id}'" too, but I still got strange characters
"commentreply":["[B@66e23a","[B@8e5225","[B@1b308c1","[B@103f345"],
I use the same query on mysql database, it returns right results.
Can someone answer me this ?
Many Thanks
Vivian
-----Original Message-----
From: Alexey Serba [mailto:aserba@gmail.com]
Sent: Monday, June 28, 2010 4:41 PM
To: solr-user@lucene.apache.org
Subject: Re: solr data config questions
Hi,
You can add additional commentreplyjoin entity to story entity, i.e.
<entity name="story" ...
...
<entity name="commenttable" ...
...
<entity name="replytable" ...>
...
</entity>
</entity>
<entity name="commentreplyjoin" query="select concat(comment_id,
',', replier_id) as commentreply from commenttable left join
replytable on replytable.comment_id=commenttable.comment_id where
commenttable.story_id=${story.story_id}'">
<field name="commentreply" column="commentreply" />
</entity>
</entity>
Thus, you will have multivalued field commentreply that contains list
of related "comment_id, reply_id" ("comment_id," if you don't have any
related replies for this entry) pairs. You can retrieve all values of
that field and process on a client and build complex data structure.
HTH,
Alex
On Mon, Jun 28, 2010 at 8:19 PM, Peng, Wei <We...@xerox.com> wrote:
> Hi All,
>
>
>
> I am a new user of Solr.
>
> We are now trying to enable searching on Digg dataset.
>
> It has story_id as the primary key and comment_id are the comment id
> which commented story_id, so story_id and comment_id is one-to-many
> relationship.
>
> These comment_ids can be replied by some repliers, so comment_id and
> repliers are one-to-many relationship.
>
>
>
> The problem is that within a single returned document the search results
> shows an array of comment_ids and an array of repliers without knowing
> which repliers replied which comment.
>
> For example: now we got comment_id:[c1,c,2...,cn],
> repliers:[r1,r2,r3....rm]. Can we get something like
> comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
> {r1,r2} is corresponding to c1?
>
>
>
> Our current data-config is attached:
>
> <dataConfig>
>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> autoreconnect="true" netTimeoutForStreamingResults="1200"
> url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
> password=" "/>
>
> <document>
>
> <entity name="story" pk="story_id" query="select * from
> story"
>
> deltaImportQuery="select * from story where
> ID=='${dataimporter.delta.story_id}'"
>
> deltaQuery="select story_id from story where
> last_modified > '${dataimporter.last_index_time}'">
>
>
>
> <field column="link" name="link" />
>
> <field column="title" name="title" />
>
> <field column="description" name="story_content" />
>
> <field column="digg" name="positiveness" />
>
> <field column="comment" name="spreading_number" />
>
> <field column="user_id" name="author" />
>
> <field column="profile_view" name="user_popularity" />
>
> <field column="topic" name="topic" />
>
> <field column="timestamp" name="timestamp" />
>
>
>
> <entity name="dugg_list" pk="story_id"
>
> query="select * from dugg_list where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from dugg_list where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${dugg_list.story_id}">
>
> <field name="viewer" column="dugger" />
>
> </entity>
>
>
>
> <entity name="commenttable" pk="comment_id"
>
> query="select * from commenttable where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from commenttable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${commenttable.story_id}">
>
> <field name="comment_id" column="comment_id" />
>
> <field name="spreading_user" column="replier" />
>
> <field name="comment_positiveness" column="up" />
>
> <field name="comment_negativeness" column="down" />
>
> <field name="user_comment" column="content" />
>
> <field name="user_comment_timestamp"
> column="timestamp" />
>
>
>
>
>
> <entity name="replytable"
>
> query="select * from replytable where
> comment_id='${commenttable.comment_id}'"
>
> deltaQuery="select SID from replytable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select comment_id from
> commenttable where comment_id=${replytable.comment_id}">
>
> <field name="replier_id" column="replier_id" />
>
> <field name="reply_content" column="content" />
>
> <field name="reply_positiveness" column="up" />
>
> <field name="reply_negativeness" column="down" />
>
> <field name="reply_timestamp" column="timestamp" />
>
> </entity>
>
>
>
> </entity>
>
> </entity>
>
> </document>
>
> </dataConfig>
>
>
>
> Please help me on this.
>
> Many thanks
>
>
>
> Vivian
>
>
>
>
>
>
>
>
Re: solr data config questions
Posted by Alexey Serba <as...@gmail.com>.
Hi,
You can add additional commentreplyjoin entity to story entity, i.e.
<entity name="story" ...
...
<entity name="commenttable" ...
...
<entity name="replytable" ...>
...
</entity>
</entity>
<entity name="commentreplyjoin" query="select concat(comment_id,
',', replier_id) as commentreply from commenttable left join
replytable on replytable.comment_id=commenttable.comment_id where
commenttable.story_id=${story.story_id}'">
<field name="commentreply" column="commentreply" />
</entity>
</entity>
Thus, you will have multivalued field commentreply that contains list
of related "comment_id, reply_id" ("comment_id," if you don't have any
related replies for this entry) pairs. You can retrieve all values of
that field and process on a client and build complex data structure.
HTH,
Alex
On Mon, Jun 28, 2010 at 8:19 PM, Peng, Wei <We...@xerox.com> wrote:
> Hi All,
>
>
>
> I am a new user of Solr.
>
> We are now trying to enable searching on Digg dataset.
>
> It has story_id as the primary key and comment_id are the comment id
> which commented story_id, so story_id and comment_id is one-to-many
> relationship.
>
> These comment_ids can be replied by some repliers, so comment_id and
> repliers are one-to-many relationship.
>
>
>
> The problem is that within a single returned document the search results
> shows an array of comment_ids and an array of repliers without knowing
> which repliers replied which comment.
>
> For example: now we got comment_id:[c1,c,2...,cn],
> repliers:[r1,r2,r3....rm]. Can we get something like
> comment_id:[c1,c,2...,cn], repliers:[{r1,r2},{},r3....{rm-1,rm}] so that
> {r1,r2} is corresponding to c1?
>
>
>
> Our current data-config is attached:
>
> <dataConfig>
>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> autoreconnect="true" netTimeoutForStreamingResults="1200"
> url="jdbc:mysql://localhost/diggdataset" batchSize="-1" user="root"
> password=" "/>
>
> <document>
>
> <entity name="story" pk="story_id" query="select * from
> story"
>
> deltaImportQuery="select * from story where
> ID=='${dataimporter.delta.story_id}'"
>
> deltaQuery="select story_id from story where
> last_modified > '${dataimporter.last_index_time}'">
>
>
>
> <field column="link" name="link" />
>
> <field column="title" name="title" />
>
> <field column="description" name="story_content" />
>
> <field column="digg" name="positiveness" />
>
> <field column="comment" name="spreading_number" />
>
> <field column="user_id" name="author" />
>
> <field column="profile_view" name="user_popularity" />
>
> <field column="topic" name="topic" />
>
> <field column="timestamp" name="timestamp" />
>
>
>
> <entity name="dugg_list" pk="story_id"
>
> query="select * from dugg_list where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from dugg_list where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${dugg_list.story_id}">
>
> <field name="viewer" column="dugger" />
>
> </entity>
>
>
>
> <entity name="commenttable" pk="comment_id"
>
> query="select * from commenttable where
> story_id='${story.story_id}'"
>
> deltaQuery="select SID from commenttable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select story_id from story where
> story_id=${commenttable.story_id}">
>
> <field name="comment_id" column="comment_id" />
>
> <field name="spreading_user" column="replier" />
>
> <field name="comment_positiveness" column="up" />
>
> <field name="comment_negativeness" column="down" />
>
> <field name="user_comment" column="content" />
>
> <field name="user_comment_timestamp"
> column="timestamp" />
>
>
>
>
>
> <entity name="replytable"
>
> query="select * from replytable where
> comment_id='${commenttable.comment_id}'"
>
> deltaQuery="select SID from replytable where
> last_modified > '${dataimporter.last_index_time}'"
>
> parentDeltaQuery="select comment_id from
> commenttable where comment_id=${replytable.comment_id}">
>
> <field name="replier_id" column="replier_id" />
>
> <field name="reply_content" column="content" />
>
> <field name="reply_positiveness" column="up" />
>
> <field name="reply_negativeness" column="down" />
>
> <field name="reply_timestamp" column="timestamp" />
>
> </entity>
>
>
>
> </entity>
>
> </entity>
>
> </document>
>
> </dataConfig>
>
>
>
> Please help me on this.
>
> Many thanks
>
>
>
> Vivian
>
>
>
>
>
>
>
>