Why I got doubled number of item_categories rows?

I have 3 related tables

CREATE TABLE `item_categories` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),

CREATE TABLE `items` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT,
`item_category_id` tinyint unsigned NOT NULL,
PRIMARY KEY (`id`),

CREATE TABLE `item_clicked_counts` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT,
`item_id` smallint unsigned NOT NULL,
PRIMARY KEY (`id`),

and I need to get count of item_clicked_counts, grouped by item_categories table.

I made request :

$itemCategories = ItemCategory::select('name')->rightJoin((new Item)->getTable(), (new Item)->getTable().'.item_category_id', '=', (new ItemCategory)->getTable() . '.id')
  ->orderBy('name', 'asc')
  ->addSelect(['item_clicked_counts_count' => ItemClickedCount::selectRaw('count(*)')
    ->whereColumn('item_clicked_counts.item_id', 'items.id')
     ->whereRaw("items.active = true ")
     ->getByCreatedAt($dateStart->startOfDay(), '>=')
     ->getByCreatedAt($dateEnd->endOfDay(), '<=')
     ->getByPageContentId($pageContentId)
     ->getByLocale($locale)
     ->groupBy((new ItemCategory)->getTable() . '.name')
   ->distinct((new ItemCategory)->getTable() . '.name')
  ->get();

But I got doubled number of item_categories rows( maybe as I have 2 items for any of item_categories) with sql :

SELECT  `name`, (  SELECT count(*)
FROM `item_clicked_counts`
WHERE `item_clicked_counts`.`item_id` = `items`.`id`     AND items.active = true      AND item_clicked_counts.created_at >= '2023-04-19 00:00:00'     AND item_clicked_counts.created_at <= '2023-05-19 23:59:59'
GROUP BY `item_categories`.`name`)     AS `item_clicked_counts_count`
FROM `item_categories` right join `items` on `items`.`item_category_id` = `item_categories`.`id`
ORDER BY `name` asc

I checked in telescope that generated sql found invalid data ...

How that can be fixed ?

LaryAI

3 years ago

Level 58

The issue seems to be with the use of a subquery in the select statement. Instead, you can try using a left join and group by clause to get the desired result. Here's an example:

$itemCategories = ItemCategory::select('item_categories.name', DB::raw('count(item_clicked_counts.id) as item_clicked_counts_count'))
    ->leftJoin('items', 'item_categories.id', '=', 'items.item_category_id')
    ->leftJoin('item_clicked_counts', 'items.id', '=', 'item_clicked_counts.item_id')
    ->where('items.active', true)
    ->whereBetween('item_clicked_counts.created_at', [$dateStart->startOfDay(), $dateEnd->endOfDay()])
    ->where('item_clicked_counts.page_content_id', $pageContentId)
    ->where('item_clicked_counts.locale', $locale)
    ->groupBy('item_categories.name')
    ->orderBy('item_categories.name', 'asc')
    ->get();

This query uses left joins to join the three tables and then groups the result by the item category name. The count of item_clicked_counts is calculated using the count function and the where conditions are added to filter the result.

Please or to participate in this conversation.