Link entities in Solr Data config with multiple datasource

[PeterKerk]

I started here: https://wiki.apache.org/solr/DataImportHandler#multipleds

I have a WordPress database with articles. I keep statistics (like views) on
those articles (and a range of other objects not in WordPress) in a separate
MS SQL Server database.

Statistics in the MS SQL database for articles are stored with value 110 for
column `objecttype`. The `objectid` column in the sqldb matches the `id`
column from the WordPress database. 

What I want is when I get the details for an article, I want an additional
field `viewcounter` that is populated with the number of views from the SQL
DB in a certain timeframe (so a custom query on the MS SQL database).

How can I relate these two entities in such a way that it populates entity
`article` with data from entity `stats`?


I now have this data config:

        <dataConfig>
                <dataSource type=&quot;JdbcDataSource&quot; 
name=&quot;ds-wordpress&quot;
driver=&quot;com.mysql.jdbc.Driver&quot;
url=&quot;jdbc:mysql://localhost:3306/wordpressdb&quot;
user=&quot;&lt;username>" password="<password>" />
                <dataSource name=&quot;ds-sql&quot;
driver=&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;
url=&quot;jdbc:sqlserver://localhost:1433;databaseName=sqldb&quot;
user=&quot;&lt;username>" password="<password>" />

                <document name="articles">
                
                        <entity dataSource="ds-sql" pk="ID" name="stats" 
query="SELECT * FROM
stats WHERE objecttype=110">
                                <field name="id" column="ID" />
                                <field name="objectid" column="ID" />
                                <field name="objecttype" column="ID" />
                        </entity>
                
                        <entity dataSource="ds-wordpress" pk="ID" 
name="article" query="SELECT
p.*, ( SELECT guid FROM wp_posts WHERE id = m.meta_value ) AS imgurl 
        FROM wp_posts p
        LEFT JOIN  wp_postmeta m ON(p.id = m.post_id AND m.meta_key = 
'_thumbnail_id' )
        WHERE p.post_type =  'post'
        AND p.post_status =  'publish';">
                                <field name="id" column="ID" />
                                <field name="post_title" column="post_title" />
                                <field name="imgurl" column="imgurl" />
                                
                                <field name="viewcounter" column="viewcounter" 
/>
                        </entity>
                </document>
        </dataConfig>





--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html