attribute names are repeated

Submitted by anazeem on Fri, 2010-10-15 08:59.Troubleshooting

following code always displays the first attribute name repeatedly instead of displaying all the attribute names.
<#assign attrnames=doc.getValues("ATTRIBUTE_NAME")>
<ul>
<#list attrnames as attrname>
<li>${attrname}</li>
</#list>
</ul>
for e.g. it shows
FEATURE1
FEATURE1
FEATURE1
..
..
instead of
FEATURE1
FEATURE2
FEATURE3
It used to work properly in V2.0.8 but not with latest version V4
Following query is used to fetch the data in the Select Data section and it fetches all the attributes instead of repeating the same.
select attrb.catentry_id, attrb.name as ATTRIBUTE_NAME, attrb.description as ATTRIBUTE_DESC, attrb.groupname, attrv.attribute_id, attrv.attrvalue_id, attrv.stringvalue as ATTRIBUTE_VALUE from attrvalue attrv, attribute attrb where
attrb.attribute_id=attrv.attribute_id and
(attrb.name in ('FEATURE1','FEATURE2') OR attrb.groupname='G') and
attrv.catentry_id=?

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Submitted by will on Fri, 2010-10-15 10:45.

Could you please check what's actually stored in the index?

You can go to "Status Report"=>"Index Content" to check it.

It's very likely the SQL needs some adjustment.

Submitted by anazeem on Fri, 2010-10-15 12:37.

Index content has multiple instances of each attribute but not sure how the same query worked well in 2.0.8. the same query when executed in database returns distinct attributes. don't think there is any issue with query. One more thing that I observed is the first attribute name repeats as many times as the number of attribute in the associated product.

Submitted by will on Fri, 2010-10-15 17:17.

I don't think we have much change on the SQL crawler part. But for your query, the number of rows varies depending on the number of attribute values. Can you do this instead?

Select the values:

 select attrv.attribute_id, attrv.attrvalue_id, 
  attrv.stringvalue as ATTRIBUTE_VALUE 
 from attrvalue attrv, attribute attrb 
 where attrb.attribute_id=attrv.attribute_id
 and (attrb.name in ('FEATURE1','FEATURE2') OR attrb.groupname='G')
 and attrv.catentry_id=?

And then get those attributes:

 select attrb.catentry_id, attrb.name as ATTRIBUTE_NAME,
  attrb.description as ATTRIBUTE_DESC, attrb.groupname
 from attribute attrb 
 where attrb.attribute_id=? //use the attribute_id from previous query
Submitted by anazeem on Mon, 2010-10-18 11:18.

even that query does not seem to help.
Our MAIN query returns multiple instance of same partnumber for each contract pricing. for e.g. partnumber ABC will occur 4 times if there are 4 different contract pricing for that product, to avoid this we passed contractId along with search term to dbsight to fetch the partnumber associated with the contractId. that would return only one partnumber and associated attributes.

I modified the main query to return only one partnumber so that the attribute sub query will receive only one catentry_id (primary key for partnumber), even that does not seem to help.

looks like a mystery

Submitted by will on Mon, 2010-10-18 13:08.

If the input is one catentry_id, and you use it to select the attribute, why there are several entries of the attribute? Maybe you can add a "distinct" if there are duplicates.

Have you checkd first with some SQL client?

Submitted by anazeem on Mon, 2010-10-18 13:43.

catentry_id has one to many relation with attributes so each product can have multiple attributes.

yes, it does show distinct attributes when I run the query in SQL client Squirrel, even in dbsight it shows distinct attributes when click on 'Automatically Generate Result columns'. its only in the index content that it shows multiple instance of same attribute for each catentry_id.

I tried with distinct(attribute_id) even that does not help

Submitted by will on Mon, 2010-10-18 19:30.

Does your previous SQL has multiple catentry_id? If so,

You can export the index configuration from the dashbard, and email it to dbsight at gmail dot com. It's easier to see what's going on than the forum post.

Submitted by anazeem on Tue, 2010-10-19 07:28.

I sent the index configuration to the gmail id, let me know if you need any other info. the subject would be 'attribute names are repeated'

Submitted by anazeem on Wed, 2010-10-20 13:51.

Thanks Chris! 4.0.3 fixed the one to many attribute issues. I can see distinct attributes now