Hi Andrea,
I think you helped me to get closer, but not quite there yet.
When I replace wtr.object_id='${article.id}'; with wtr.object_id=18
the cat_name field holds a value, which I checked via the schema browser of
Solr dashboard!
I then checked my main 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';
which returns 4 results. For each of these results I checked whether the
direct query on the database returns a cat_name and it does. So, no null
values there.
When I remove the quotes around the ID like so
<entity name="post_categories" query="select wt.name as
cat_name,wt.slug,wtr.term_taxonomy_id,wtt.term_id,wtt.taxonomy from
wp_term_relationships wtr
INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id
AND wtt.taxonomy='category'
INNER JOIN wp_terms wt ON wt.term_id=wtt.term_taxonomy_id
where wtr.object_id=${article.id};">
I get the errors:
Caused by: org.apache.solr.handler.dataimport.DataImportHandlerException:
Unable
to execute query: select wt.name as
cat_name,wt.slug,wtr.term_taxonomy_id,wtt.t
erm_id,wtt.taxonomy from wp_term_relationships wtr INNER JOIN
wp_term_taxonomy
wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id AND wtt.taxonomy='category'
INN
ER JOIN wp_terms wt ON wt.term_id=wtt.term_taxonomy_id where wtr.object_id=;
Pro
cessing Document # 1
at
org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAnd
Throw(DataImportHandlerException.java:71)
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<
init>(JdbcDataSource.java:253)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSou
rce.java:210)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSou
rce.java:38)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEn
tityProcessor.java:59)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEnti
tyProcessor.java:73)
at
org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(Ent
ityProcessorWrapper.java:243)
at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilde
r.java:465)
at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilde
r.java:491)
at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilde
r.java:404)
... 5 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
have a
n error in your SQL syntax; check the manual that corresponds to your MySQL
serv
er version for the right syntax to use near '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown
Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<
init>(JdbcDataSource.java:246)
... 13 more
--
View this message in context:
http://lucene.472066.n3.nabble.com/Empty-facets-on-Solr-with-MySQL-tp4109170p4109398.html
Sent from the Solr - User mailing list archive at Nabble.com.
