3

I was trying to implement bayesian average logic in doctrine and symfony.

I have basic native Mysql query like this:

    Create View `ratings` AS
SELECT
    restaurant_id,
    (SELECT count(restaurant_id) FROM ratings) / (SELECT count(DISTINCT restaurant_id) FROM ratings) AS avg_num_votes,
    (SELECT avg(rating) FROM ratings) AS avg_rating,
    count(restaurant_id) as this_num_votes,
    avg(rating) as this_rating
FROM
    ratings
GROUP BY 
    restaurant_id


SELECT 
    restaurant_id, 
    ((avg_num_votes * avg_rating) + (this_num_votes * this_rating)) / (avg_num_votes + this_num_votes) as real_rating 
FROM `ratings`

This query creates table view from where we are retrieving records.

From some documents I came to know that we can't create view in Doctrine. So another option is to create temporary table. How we can create temp table with different structure.

Referring : http://shout.setfive.com/2013/11/21/doctrine2-using-resultsetmapping-and-mysql-temporary-tables/

// Add the field info for each column/field       
  foreach( $classMetadata->fieldMappings as $id => $obj ){
    $rsm->addFieldResult('u', $obj["columnName"], $obj["fieldName"]); 
    // I want to crate new fields to store avg rating etc.
  }

How we can implement this in Doctrine and Symfony?

stefun
  • 1,261
  • 3
  • 24
  • 54
  • Doctrine cant handle views, please read this http://stackoverflow.com/questions/20419925/symfony2-doctrine2-mysql-view-tables But if you insist using views in doctrine, you can define it as normal table, but this is bad practice http://stackoverflow.com/questions/8377671/how-to-set-up-entity-doctrine-for-database-view-in-symfony-2 – Jun Rikson May 21 '14 at 07:28
  • So we cant right this type of queries or in some other way? – stefun May 21 '14 at 07:31
  • If you want to make Native SQL Query, try read this http://docs.doctrine-project.org/en/latest/reference/native-sql.html You can use Custom Query directly from Entity Manager, but this is bad practice unless its really necessary http://stackoverflow.com/questions/12862389/symfony2-doctrine-create-custom-sql-query – Jun Rikson May 21 '14 at 07:46
  • I'm planning to create temp table instead of view. Is that is good option? – stefun May 21 '14 at 10:12

0 Answers0