0

I'm trying to get a collection with all the product, and add the view count but only for the last 3 month. here is me request to get all the product

$this->getSelect()->reset()->from(
                ['e' => $this->getProductEntityTableName()]
            )->joinLeft(
                ['report_table_views' => $this->getTable('report_event')],
                'e.entity_id = report_table_views.object_id',
                ['views' => 'COUNT(report_table_views.event_id)']
            )->group(
                'e.entity_id'
            );


if ($from != '' && $to != '') {
            $this->getSelect()->where('report_table_views.logged_at >= ?', $from)->where('report_table_views.logged_at <= ?', $to);
        }

Result

product 1 - 10 view
product 2 - 5 view

expected result

 product 1 - 10 view
 product 2 - 5 view
 product 3 - 13 view
 product 4 - 7 view

product 3 and 4 get cut out because of the where condition. I would like the where condition to only apply to the COUNT(report_table_views.event_id)

0

If found the solution, I needed to add the condition AND on the join:

if ($from != '' && $to != '') {
    $this->getSelect()->reset()->from(
        ['e' => $this->getProductEntityTableName()]
    )->joinLeft(
        ['report_table_views' => $this->getTable('report_event')],
        'e.entity_id = report_table_views.object_id AND report_table_views.logged_at >= "'.$from.'"  AND  report_table_views.logged_at <= "'.$to.'"  ',
        ['views' => 'COUNT(report_table_views.event_id)']
    )->group(
        'e.entity_id'
    );

}

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.