Re: SQl help to build a result with custom aliased bool column - Mailing list pgsql-general

On Mon, 8 Apr 2019 15:32:36 +0530
Arup Rakshit <ar@zeit.io> wrote:

hi,

> I am still having some bugs. I am getting duplicate in the result set.
>
> psql (11.0, server 10.5)
> Type "help" for help.
>
> aruprakshit=# select * from features;
>  id | name
> ----+------
>   1 | f1
>   2 | f2
>   3 | f3
>   4 | f4
> (4 rows)
>
> aruprakshit=# select * from company;
>  id | name
> ----+------
>   1 | c1
>   2 | c2
> (2 rows)
>
> aruprakshit=# select * from company_features;
>  id | company_id | feature_id
> ----+------------+------------
>   1 |          1 |          1
>   2 |          1 |          2
>   3 |          2 |          3
>   4 |          1 |          3
> (4 rows)
>
> aruprakshit=# SELECT
> aruprakshit-# features.id,
> aruprakshit-# features.name,
> aruprakshit-# coalesce(company_features.company_id = 1, false) AS
> active aruprakshit-# FROM
> aruprakshit-# features
> aruprakshit-# LEFT JOIN company_features ON features.id =
> company_features.feature_id; id | name | active
> ----+------+--------
>   1 | f1   | t
>   2 | f2   | t
>   3 | f3   | f
>   3 | f3   | t
>   4 | f4   | f
> (5 rows)

>
> I should get in the results only 3, as total number of features are 3.

not only dups, but also you read too much (not an issue with so small
number of tuples, but...)

what you really need is all features and subset of commpany_features
with company_id = 1 for that specific example, not the whole
company_features table

something like

SELECT f.id, f.name, fc.id IS NOT NULL AS active
FROM features f
     LEFT OUTER JOIN company_features cf
       ON cf.company_id = 1 and cf.feature_id = f.id

would be enough,

or something like

SELECT f.id, f.name,
       EXISTS (SELECT 0 FROM company_features cf
                 WHERE cf.company_id = 1 AND cf.feature_id = f.id)
       AS active
FROM features f

or couple more ways to achieve what you want for a given company (id=1
here)

bear in mind that with a large number of companies and proper index on
company_features the optimizer could limit company_features as
necessary, while your examples read everything anyway and mangle output
to get proper result (with dups and bugs, but also not optimal)

furthermore i see some inconsistencies with naming, like tables
(relations) company, feature, companyfeature in your first mail and
features, company_features downward.
i wrote above examples as in your last query, but honestly i would not
really agree with such naming. for me relations (yes, relation like in
math background, more than table of objects) would be company, feature,
company_feature, you may prefer something other but try to make it
consistent

regards, mariusz

>
> Thanks,
>
> Arup Rakshit
> ar@zeit.io
>
>
>
> > On 08-Apr-2019, at 3:28 PM, Szymon Lipiński <mabewlun@gmail.com>
> > wrote:
> >
> > Hey,
> > you could just use
> >
> > SELECT
> >         features.id,
> >         features.name,
> >         company_features.company_id = 1 as active
> >
> > regards,
> > Szymon
> >
> > On Mon, 8 Apr 2019 at 09:55, Arup Rakshit <ar@zeit.io> wrote:
> > I have 2 tables Company and Feature. They are connected via a join
> > table called CompanyFeature. I want to build a result set where it
> > will have id, name and a custom boolean column. This boolean column
> > is there to say if the feature is present for the company or not.
> >
> > Company table:
> >
> >     | id | name |
> >     |----|------|
> >     | 1  | c1   |
> >     | 2  | c2   |
> >     | 3  | c3   |
> >
> > Feature table:
> >
> >     | id | name |
> >     |----|------|
> >     | 1  | f1   |
> >     | 2  | f2   |
> >     | 3  | f3   |
> >
> > Company Feature table:
> >
> >     | id | feature_id | company_id |
> >     |----|------------|------------|
> >     | 1  | 2          | 1          |
> >     | 2  | 1          | 1          |
> >     | 3  | 3          | 2          |
> >
> > The result should look like for company `c1`:
> >
> >     | id | name | active |
> >     |----|------|--------|
> >     | 1  | f1   | t      |
> >     | 2  | f2   | t      |
> >     | 3  | f3   | f      |
> >
> > I tried something like:
> >
> > SELECT
> >         features.id,
> >         features.name,
> >         CASE WHEN company_features.company_id = 1 THEN
> >                 TRUE
> >         ELSE
> >                 FALSE
> >         END AS active
> > FROM
> >         features
> >         LEFT JOIN company_features ON company_features.feature_id =
> > features.id
> >
> > It works. But is there any better way to achieve this?
> >
> >
> > Thanks,
> >
> > Arup Rakshit
> > ar@zeit.io
> >
> >
> >
> >
> >
>
>
>