How can I accomplish this dynamic averaging by casting from PostgreSQL to DQL?

Question

I have to add a filter condition for fetching a list of reviews with an andWhere(). Here is the PostgreSQL version:

SELECT *
FROM listing_review
WHERE(
    (
      edited_rating_meals_nutrition +
      edited_rating_treatment_effectiveness +
      rating_accommodations_amenities
    )
    /
    (
      COALESCE(
        NULLIF(
              (
                (edited_rating_meals_nutrition::BOOLEAN)::INTEGER +
                (edited_rating_effectiveness::BOOLEAN)::INTEGER +
                (rating_accommodations_amenities::BOOLEAN)::INTEGER
              )
            , 0
        ), 1
      )
    )
 )
 >= 3;

Basically, I need to average three ratings per row (review) and filter based on whether the average of them is equal to or greater to some integer (5 in this example). If they haven't been reviewed in either category, that category's value is zero, and I have to discard them from the average calculation (hence the double casting from boolean to an integer to get the number of ratings that are not zero). I also can't divide by zero, explaining the COALESCE.

I'm able to accomplish this the naive way just dividing by three like this:

if (isset($searchParams[self::PARAM_MINIMUM_RATING])) {
$qb
   ->andWhere('((r.editedRatingTreatmentEffectiveness + r.editedRatingAccommodationsAmenities + r.editedRatingMealsNutrition) / 3) >= :minimum_rating')
   ->setParameter('minimum_rating', $searchParams[self::PARAM_MINIMUM_RATING]);

How can I do this in DQL making the divisor dynamic (like my PostgreSQL query) instead of hardcoded to 3?

score 1 · Accepted Answer · answered Oct 31 '18 at 21:22

I solved my own problem with the help of this post.

Basically, DQL contains NULLIF() as well as COALESCE() but does NOT contain CAST(). I added the following classes:

<?php
namespace DoctrineFunctions;


use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class CastToBoolean extends FunctionNode
{
    public $stringPrimary;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS boolean)';
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

As well as this very similar casting function for casting to integer:

<?php

namespace DoctrineFunctions;


use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class CastToInteger extends FunctionNode
{
    public $stringPrimary;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS integer)';
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Then I register these methods in doctrine.yml:

orm:
    auto_generate_proxy_classes: ""
    default_entity_manager: default
    entity_managers:
        default:
            connection: default
            mappings:
                RBundle: ~
                ABundle: ~
            result_cache_driver:
                type: memcached
                host: ''
                port: 
            query_cache_driver:
                type: memcached
                host: ''
                port: 
            metadata_cache_driver:
                type: memcached
                host: ''
                port: 
            naming_strategy: doctrine.orm.naming_strategy.underscore
            filters:
                softdeleteable:
                    class: 
                    enabled: 
            dql:
                numeric_functions:
                    cast_to_int: DoctrineFunctions\CastToInteger
                string_functions:
                    cast_to_boolean: DoctrineFunctions\CastToBoolean

And finally, I reconstruct the SQL query as a DQL query with all necessary methods:

 if (isset($searchParams[self::PARAM_MINIMUM_RATING])) {
        $qb
            ->andWhere('((r.editedRatingTreatmentEffectiveness + r.editedRatingAccommodationsAmenities + r.editedRatingMealsNutrition) / (COALESCE(NULLIF(cast_to_int(cast_to_boolean(r.editedRatingTreatmentEffectiveness)) + cast_to_int(cast_to_boolean(r.editedRatingAccommodationsAmenities)) + cast_to_int(cast_to_boolean(r.editedRatingMealsNutrition)), 0), 1))) >= :minimum_rating')
            ->setParameter('minimum_rating', $searchParams[self::PARAM_MINIMUM_RATING]);
    }

How can I accomplish this dynamic averaging by casting from PostgreSQL to DQL?

1 Answers1