0

I am looking to return a subset of reviews for example but discard the top x% and bottom x%

I could do it with an SQL query such as

SELECT * FROM
(
  SELECT review_date, review_score,  @r:= @r + 1 AS rownum
  FROM review_detail, (SELECT @r:=0) x
  ORDER BY review_score
) x
WHERE rownum > @r * .1
  AND rownum <= @r * .9
;

However I'm struggling to understand how I can order the collection and add a new field for the row number and then be able to order and sort it using the syntax within magento

when you get a collection is the row number updated so that if I sort by rating I can then address the row number?

I'm guessing I'd have to use Zend_Db_Expr to create the new field for row number sorted by review score then use array("from" => $fromValue, "to" => $toValue) in an addFilterToField Statement to return the collection

Any help would be greatly appreciated

EDIT : I've tried doing it by using limit and offset but this destroys the pagination on the screen but returns the correct data

 } elseif ($condition == Filter::EXCLUDE) {
            if ($value !== 'None') {
                $percentage = (float)$value;
                $current_size = $reviewCollection->getSize();
                $start_pos = round($current_size * ($percentage/100));
                $offset = $current_size - (2*$start_pos);
                $reviewCollection->getSelect()->limit($offset,$start_pos);
                $reviewCollection->getItems();
0

You could always just run the raw SQL:

$dbConnection = \Magento\Framework\App\ObjectManager::getInstance()
        ->get('Magento\Framework\App\ResourceConnection')
        ->getConnection();

$result = $dbConnection->fetchAll("
    SELECT * FROM
    (
      SELECT review_date, review_score,  @r:= @r + 1 AS rownum
      FROM review_detail, (SELECT @r:=0) x
      ORDER BY review_score
    ) x
    WHERE rownum > @r * .1
      AND rownum <= @r * .9
");

And then looping over the results you can build a new structure or use the data returned:

foreach ($results as $result) {
    //$result['review_date'];
    //$result['review_score'];
}
  • Hi Barry I'd love to but I need it to also preserve other filters that already exist on the collection, I'm using a toolbar and can filter by helpful etc and also sort, I really need to add it to the existing collection, unless I use that to build an array and then filter by the review_id on the existing collection. – Chris Grierson Oct 9 at 19:11
  • "unless I use that to build an array and then filter by the review_id on the existing collection", that would have been my suggestion but was unsure the size of numbers that would be relevant – Barry Oct 10 at 8:38
  • i've resigned myself to the fact that until I update my version of Ubunto and therefore the MariaDb so that I can use windows expressions, there is no other way unless someone else has any ideas? – Chris Grierson Oct 10 at 23:27
  • Solved it but had to basically run the raw sql then use that to generate an array and apply it as a filter to the original collection. – Chris Grierson Oct 16 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.