How can I create a bool column for a view based on the result of a statement?

All we need is an easy explanation of the problem, so here it is.

I’m using PostgreSQL as my database.

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  group     INT           NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (group)
    REFERENCES groups(id),
  UNIQUE (group),
  UNIQUE (name)
);

I want to create a view that includes a boolean column stating whether or not a group has any users based on the has many / belongs to relationship of the two tables above. If it’s easier, I’d even be fine with an integer column stating how many users a group has in it. How can I do this in my view?

CREATE VIEW group_info AS
  SELECT DISTINCT
    g.name        AS name,
    ????          AS empty
  FROM groups AS g
  JOIN users AS u
    ON (g.id = u.group)
;

I’ve looked into using BOOL_OR and/or COUNT to help with testing the existence of a group ID in the users table, but I’m not seeing how to put it all together.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

"a boolean column stating whether or not a group has any users"

Use EXISTS:

CREATE VIEW group_info AS
SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty
FROM   groups g;

This returns 1 row per group, no matter whether there are users or not – not one row per user like you had, but probably didn’t want – so we don’t need DISTINCT.

It’s largely irrelevant whether you write SELECT 1 or SELECT * or SELECT 'foo' in the EXISTS subquery. Only the bare existence of at least one row matters.

With number of users

CREATE VIEW group_info AS
SELECT g.name, count(u.group) AS ct_users
FROM   groups     g
LEFT   JOIN users u ON u.group = g.id
GROUP  BY g.name;

LEFT JOIN is crucial to retain groups without users.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply