When designing for Solr (or most search engines), think in terms of documents,
not tables.
What do your search results look like? You will want one document for each
search result. The document will have stored fields for each thing displayed
and indexed fields for each thing searched.
If you are starting with a relational database, think about a view that will
have one row per document. Denormalize as much as you need in order to get that.
For implementation, it might be a view, or an index time query, but the concept
of a view may help you design.
wunder
On Jun 6, 2013, at 9:24 AM, Stavros Delisavas wrote:
> It's surprising to me that all tables have to have a relationship in order to
> be used in solr. What if I have two indipendent projects running on the same
> webserver? I would not be able to use Solr for both of them, really? That
> would be very dissappointing...
>
> Anyway, luckily there is an indirect relationship between the two tables but
> there is an "N to N" relationship with a thrid table in between. The full
> join in MySQL would be something like this:
>
> SELECT (cast.id??), title.id, title.title, name.id, name.name
> FROM name, title, cast
> WHERE title.id = cast.movie_id
> AND cast.person_id = name.id
>
> But this will definatly lead to multiple entries of name.name and title.title
> because they are connected with an N-to-N relationship. So the resulting
> table would not have unique keys either!! Nor title.id or name.id. There is
> another id available cast.id which could be used as a unique id, but its a
> completly useless and irrelevant id which has no connection/relation to
> anything else at all. So there is no real use for it to include it, unless
> Solr really needs a unique id.
>
> I am still a noob with Solr. Can you please help me to adapt the given Join
> to the xml-syntax for my data-config.xml?
> That would be very great!
>
>
> Am 06.06.2013 17:58, schrieb bbarani:
>> The below error clearly says that you have declared a unique id but that
>> unique id is missing for some documents.
>>
>> org.apache.solr.common.SolrException: [doc=null] missing required field:
>> nameid
>>
>> This is mainly because you are just trying to import 2 tables in to a
>> document without any relationship between the data of 2 tables.
>>
>> table 1 has the nameid (unique key) but table 2 has to be joined with table
>> 1 to form a relationship between the 2 tables. You can't just dump the value
>> since table 2 might have more values than table1 (but table1 has the unique
>> id).
>>
>> I am not sure of your table structure, I am assuming that there is a key
>> (ex: nameid in title table) that can be used to join name and title table.
>>
>> Try something like this..
>>
>> <document>
>> <entity name="name" query="SELECT id, name FROM name LIMIT 10">
>> <field column="id" name="nameid" />
>> <field column="name" name="name" />
>> </entity>
>> * <entity name="title" query="SELECT id, title FROM title where
>> nameid=${name.id}">
>> * <field column="id" name="titleid" />
>> <field column="title" name="title" />
>> </entity>
>> </document>
>> </dataConfig>
>>
