symfony2 / doctrine: how to use ResultSetMapping with count

Question

I have an archive table (id, title, author) and a download_history table (id, id_archive, date) to keep track of how many download for each archive. Now I want to build a report to view the most downloaded archives on a weekly bases. Pretty simple to do in SQL, but things are more complicated with doctrine, this is what I've done so far:

public function getTopDownloadsLastXDays($limit=100, $days=7)
{
    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('DownloadHistory', 'd');
    $rsm->addMetaResult('d', 'archive', 'archive');
    $rsm->addScalarResult('tot', 'tot');
    $rsm->addJoinedEntityResult('Archive', 'a', 'd', 'archive');
    $rsm->addFieldResult('a', 'author', 'author');
    $rsm->addFieldResult('a', 'title', 'title');

    $sql = "SELECT
              DISTINCT(dh.archive_id),
              COUNT(*) as tot,
              a.author,
              a.title
            FROM
              download_history dh
            INNER JOIN
              archive a
            ON
              dh.archive_id = a.id
            WHERE
              dh.date >= DATE(NOW()) - INTERVAL :days DAY
            GROUP BY
              dh.archive_id
            LIMIT :limit";

    $em = $this->getEntityManager();
    $query = $em->createNativeQuery($sql, $rsm);
    $query->setParameters(array(
        ':days'     =>  $days,
        ':limit'    =>  $limit,
    ));

    $results = $query->getResult();
    return $results;
}

Entities exist and work fine. I followed the third example here: http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html#examples. This is my result:

array(1) { 
    [-1]=> array(1) { 
        ["tot"]=> string(1) "1" 
    } 
}

any idea? thanks

yes if I execute it on the command line it works as expected. — user3174311
– user3174311, Commented May 15, 2015 at 9:47

Tom Cleaveland · Accepted Answer · 2018-03-30 20:39:19Z

0

You aliased the table as "dh", but you specified the alias as "d" when building the ResultSetMapping.

answered Mar 30, 2018 at 20:39

Tom Cleaveland

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

symfony2 / doctrine: how to use ResultSetMapping with count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related