How to bump most helpful reviews to the top?

Question

Picture this, I have a bunch of products, each product has a review section, if someone leaves a good review their review gets a like to inform that other users found it helpful.

I have a table named CustomerReview, and another table named likeReview, and inside this table (likeReview) I have a column:

isLike(values true, false or null)

I need to make this table bump the most helpful reviews to the top of the list.

How should I go about that?

I get an error with My SQL trying to make isLike do that, because there are reviews (CustomerReview) that are not associated with the likeReview table.

SELECT {c.pk} 
FROM{ CustomerReview as c LEFT JOIN LikeReview as l ON {c.pk} = {l.pk} } 
WHERE {c.product}=?product 
  AND {c.LANGUAGE}=?language 
ORDER BY   {l.ISLIKE } ;

my items.xml

        <relation code="Review2User" localized="false"
              generate="true" autocreate="true">
        <sourceElement qualifier="likeReview" type="LikeReview"
                       cardinality="many" >
        </sourceElement>
        <targetElement qualifier="customerReview" type="CustomerReview"
                       cardinality="one">
        </targetElement>
    </relation>

relation

        <typegroup name="LikeReview">
        <itemtype code="LikeReview" autocreate="true" generate="true">
            <deployment table="likeReview" typecode="15088"/>

            <attributes>
                <attribute type="Customer" qualifier="customer" >
                    <modifiers optional="false" unique="true"/>
                    <persistence type="property"/>
                </attribute>
                <attribute type="CustomerReview" qualifier="review" >
                    <modifiers optional="false" unique="true"/>
                    <persistence type="property"/>
                </attribute>
                <attribute qualifier="isLike" type="java.lang.Boolean">
                    <defaultvalue>Boolean.FALSE</defaultvalue>
                    <persistence type="property"/>
                </attribute>
            </attributes>
        </itemtype>
    </typegroup>

Could you elaborate bit more about how you maintain the relations between these item types. Better you add it's declaration (*items.xml) here, which will help me to understand your question correctly. — HybrisHelp, May 16 '19 at 04:59

score 1 · Answer 1 · answered May 16 '19 at 01:13

1

I think you need aggregation. Something like this:

SELECT {c.pk}
FROM { CustomerReview c LEFT JOIN
     LikeReview l
     ON {c.pk +"} = {l.pk} }
WHERE {c.product} = ?product AND
      {c.LANGUAGE +} = ?language
GROUP BY {c.pk}
ORDER BY SUM(CASE WHEN {l.ISLIKE } = "true" THEN 1 ELSE 0 END) DESC

answered May 16 '19 at 01:13

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, I think your code is necessary, but would it work when *because there are reviews (CustomerReview)that are not associated with the likeReview table.* – Scary Wombat May 16 '19 at 01:17
@ScaryWombat . . . Yes, the `LEFT JOIN` takes care of that. – Gordon Linoff May 16 '19 at 01:43
I had better re-open the question then ;-) – Scary Wombat May 16 '19 at 01:44
I tried your solution but I get the same error : cannot search unknown field 'TableField(name='isLike',langPK='null',type=CustomerReview)' – Felipe Marcelo May 16 '19 at 02:59
Note that hybris doesn't really use SQL, it uses "flexible search", which is similar to SQL, but has a different syntax and only supports a small subset of the features of modern SQL – May 16 '19 at 06:11
@FelipeMarcelo I think you messed up the aliases. If you use l as alias for the LikeReview table in combination with l.islike you will not have this error. If you see the same error with type likereview instead of customerreview, then it's another story. – Johannes von Zmuda May 16 '19 at 13:57
@a_horse_with_no_name ...I had this result order 1 : negative comment, 2 Most voted comment, 3 comment that had no vote, 4 the second most voted comment – Felipe Marcelo May 16 '19 at 15:11

score 0 · Accepted Answer · answered May 16 '19 at 17:09

0

Try to join using ON {c.pk} = {l.customerReview}

SELECT {c.pk}
FROM { CustomerReview c LEFT JOIN
     LikeReview l
     ON {c.pk} = {l.customerReview} }
WHERE {c.product} = ?product AND
      {c.LANGUAGE} = ?language
GROUP BY {c.pk}
ORDER BY SUM(CASE WHEN {l.ISLIKE} = "true" THEN 1 ELSE 0 END) DESC

answered May 16 '19 at 17:09

HybrisHelp

5,518
2
27
65

Thank you, it worked. I just had to change the "true" to "1" – Felipe Marcelo May 17 '19 at 17:53
Is the relationship I made correct? What could I improve? why did {c.pk} = {l.customerReview} work instead of {c.pk} = {l.pk}? – Felipe Marcelo May 17 '19 at 17:53
Yes I think relation you've declared is correct only. In case of one to many relations qualifier generates in cross table, have said that customerReview qualifier generate in LikeReviewModel which has PK of CustomerReviewModel – HybrisHelp May 18 '19 at 04:06
{c.pk} = {l.pk} will not because it's pk of respective table and has different vale which won't match each other – HybrisHelp May 18 '19 at 04:09
@FelipeMarcelo - I think you can remove `review` attribute from `LikeReview` ItemType, since you already have a relation declaration which generates `customerReview` attribute in your `LikeReviewModel` – HybrisHelp May 21 '19 at 04:18
So, thinking better, you're right.I'm going to remove and test, I'll notify you if it's still working well... Tks, brother! – Felipe Marcelo May 21 '19 at 12:30

How to bump most helpful reviews to the top?

2 Answers2