Question 226256 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Group By with Having Count, not working as expected

SELECT * FROM destinations;

                  id                  |         updated_at         |         created_at         |                     name                     |                       image                        
--------------------------------------+----------------------------+----------------------------+----------------------------------------------+----------------------------------------------------
 cf534cd9-56dd-43d5-87bb-713538ec748d | 2023-08-08 23:44:42.822226 | 2023-08-08 23:44:42.822226 | Afghanistan                                  | https://x.cloudfront.net/flags/af.svg
 af4e3ac4-e9a6-4c17-8077-664d896f3a7f | 2023-08-08 23:44:42.822226 | 2023-08-08 23:44:42.822226 | Aland Islands                                | https://x.cloudfront.net/flags/ax.svg
 b377b81e-3a0c-4768-84c4-bba67e3bae1b | 2023-08-08 23:44:42.822226 | 2023-08-08 23:44:42.822226 | Albania                                      | https://x.cloudfront.net/flags/al.svg



SELECT * FROM dmc_destinations;

            dmc_profile_id            |            destination_id            
--------------------------------------+--------------------------------------
 b9ff20c1-1d7a-4a41-a3ac-64345f38814f | b377b81e-3a0c-4768-84c4-bba67e3bae1b
 b9ff20c1-1d7a-4a41-a3ac-64345f38814f | ce6a9cea-98e7-41f6-b6a6-cc150fc356e



SELECT "company"."id" AS "company_id"
FROM "companies" "company"
INNER JOIN "dmc_profiles" "dmcProfile" ON "dmcProfile"."company_id" = "company"."id"
INNER JOIN "dmc_destinations" "dmcProfile_destinations" ON "dmcProfile_destinations"."dmc_profile_id" = "dmcProfile"."id"
INNER JOIN "destinations" "destinations" ON "destinations"."id" = "dmcProfile_destinations"."destination_id"
WHERE "company"."type" = 'dmc' AND "destinations"."name" IN ('Algeria', 'Albania')
GROUP BY "company"."id", "dmcProfile"."id", "destinations"."id"
HAVING COUNT(DISTINCT "destinations"."id") = 2;

 company_id 
------------
(0 rows)

SELECT "company"."id" AS "company_id"
FROM "companies" "company"
INNER JOIN "dmc_profiles" "dmcProfile" ON "dmcProfile"."company_id" = "company"."id"
INNER JOIN "dmc_destinations" "dmcProfile_destinations" ON "dmcProfile_destinations"."dmc_profile_id" = "dmcProfile"."id"
INNER JOIN "destinations" "destinations" ON "destinations"."id" = "dmcProfile_destinations"."destination_id"
WHERE "company"."type" = 'dmc' AND "destinations"."name" IN ('Algeria', 'Albania')
GROUP BY "company"."id", "dmcProfile"."id", "destinations"."id"
HAVING COUNT(DISTINCT "destinations"."id") = 1;

              company_id              
--------------------------------------
 c5223a91-d72d-4ee3-865d-46847a7be90a
 c5223a91-d72d-4ee3-865d-46847a7be90a
(2 rows)

I have a company that has profile, and i have destination points that’s related to the profile. So company entity, profile entity with company fk, dmc_destinations as a join table for profile_id and destination_id, destinations as a fixed destination data like name, flagurl, etc.

This is basically filtering, I want to retrieve companies that has both "X" and "Y" countries (not any of them) as a destination point, how do I achieve that? What am I missing here?

Edit: my bad I only give you half the context, this is what typeorm actually generate me:

SELECT "company"."id" AS "company_id",
       "company"."updated_at" AS "company_updated_at",
       "company"."created_at" AS "company_created_at",
       "company"."name" AS "company_name",
       "company"."logo_uri" AS "company_logo_uri",
       "company"."address" AS "company_address",
       "company"."zip_code" AS "company_zip_code",
       "company"."type" AS "company_type",
       "company"."phone" AS "company_phone",
       "company"."website" AS "company_website",
       "company"."email" AS "company_email",
       "company"."owner_id" AS "company_owner_id",
       "company"."host_agency_id" AS "company_host_agency_id",
       "company"."city_id" AS "company_city_id",
       "company"."country_id" AS "company_country_id",
       "company"."state_id" AS "company_state_id",
       "company"."collection_id" AS "company_collection_id",
       "dmcProfile"."id" AS "dmcProfile_id",
       "dmcProfile"."updated_at" AS "dmcProfile_updated_at",
       "dmcProfile"."created_at" AS "dmcProfile_created_at",
       "dmcProfile"."description" AS "dmcProfile_description",
       "dmcProfile"."address" AS "dmcProfile_address",
       "dmcProfile"."minimum_spend" AS "dmcProfile_minimum_spend",
       "dmcProfile"."years_in_operation" AS "dmcProfile_years_in_operation",
       "dmcProfile"."average_response_time" AS "dmcProfile_average_response_time",
       "dmcProfile"."facebook" AS "dmcProfile_facebook",
       "dmcProfile"."instagram" AS "dmcProfile_instagram",
       "dmcProfile"."twitter" AS "dmcProfile_twitter",
       "dmcProfile"."youtube" AS "dmcProfile_youtube",
       "dmcProfile"."linkedin" AS "dmcProfile_linkedin",
       "dmcProfile"."company_id" AS "dmcProfile_company_id",
       "destinations"."id" AS "destinations_id",
       "destinations"."updated_at" AS "destinations_updated_at",
       "destinations"."created_at" AS "destinations_created_at",
       "destinations"."name" AS "destinations_name",
       "destinations"."image" AS "destinations_image"
FROM "companies" "company"
INNER JOIN "dmc_profiles" "dmcProfile" ON "dmcProfile"."company_id"="company"."id"
INNER JOIN "dmc_destinations" "dmcProfile_destinations" ON "dmcProfile_destinations"."dmc_profile_id"="dmcProfile"."id"
INNER JOIN "destinations" "destinations" ON "destinations"."id"="dmcProfile_destinations"."destination_id"
WHERE "destinations"."name" IN (:...destinations)
GROUP BY "company"."id",
         "dmcProfile"."id",
         "destinations"."id"
HAVING COUNT(DISTINCT "destinations"."name") = :destinationsCount

from this code:

const qb = this.companyRepository.createQueryBuilder('company');
    qb.where('company.type = :type', { type: CompanyTypes.DMC });
    qb.innerJoinAndSelect('company.dmcProfile', 'dmcProfile');

if (destinations) {
      qb.innerJoinAndSelect('dmcProfile.destinations', 'destinations')
        .where('destinations.name IN (:...destinations)', {
          destinations,
        })
        .groupBy('company.id')
        .having('COUNT(DISTINCT destinations.name) = :destinationsCount', {
          destinationsCount: 2,
        });

      console.log(qb.getQuery());
    }

so I can not really get rid of other group by components.

Tags:

2

Answers


  1. 0

    I’d suggest that if you remove the unnecessary columns from your GROUP BY, then you should see the results.

    SELECT "company"."id" AS "company_id"
FROM "companies" "company"
INNER JOIN "dmc_profiles" "dmcProfile" ON "dmcProfile"."company_id" = "company"."id"
INNER JOIN "dmc_destinations" "dmcProfile_destinations" ON "dmcProfile_destinations"."dmc_profile_id" = "dmcProfile"."id"
INNER JOIN "destinations" "destinations" ON "destinations"."id" = "dmcProfile_destinations"."destination_id"
WHERE "company"."type" = 'dmc' AND "destinations"."name" IN ('Algeria', 'Albania')
GROUP BY "company"."id"
HAVING COUNT(DISTINCT "destinations"."id") = 2;

    When you are only outputting the company.id value (and you presumably only want to see the unique company.id values, based on whether they match the criteria of the "HAVING" clause) then there is no need to have any additional fields as part of your GROUP BY (unless you had a very specific reason to do so). By include additional fields in the GROUP BY clause you are ultimately splitting out the rowset that will be evaluated against the "HAVING" clause – which will undoubtedly mean you don’t see the results you expect to see

    Login or Signup to reply.
  2. 0

    The following is an approach that can be used to identify rows that satisfy some set of criteria derived across rows from joined relations:

    SELECT
  company.id AS company_id
FROM
  companies company
  INNER JOIN dmc_profiles ON dmc_profiles.company_id = company.id
  INNER JOIN dmc_destinations ON dmc_destinations.dmc_profile_id = dmc_profiles.id
  INNER JOIN destinations ON destinations.id = dmc_destinations.destination_id
WHERE
  company.type = 'dmc'
GROUP BY
  company.id
HAVING
  COUNT(*) FILTER (WHERE destinations.name = 'Algeria') > 0
  AND COUNT(*) FILTER (WHERE detinations.name = 'Albania') > 0;

    This is not necessarily the best approach, but it has the advantage that criteria are independent. Each condition can be added or removed without requiring changes to other conditions; e.g., adding AND COUNT(*) FILTER (WHERE destinations.name = 'Angola') > 0 to the HAVING clause is sufficient to add Angola to the set of required destinations, there was no need to also modify a related comparison to a counter aggregated across the criteria.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search