Thread: SQl help to build a result with custom aliased bool column

SQl help to build a result with custom aliased bool column

From
Arup Rakshit
Date:
I have 2 tables Company and Feature. They are connected via a join table called CompanyFeature. I want to build a
resultset where it will have id, name and a custom boolean column. This boolean column is there to say if the feature
ispresent 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






Re: SQl help to build a result with custom aliased bool column

From
Arup Rakshit
Date:
I knew that will be more compact way. Thanks for showing it. One thing I still would like to handle is that, to make
surethe column contains only True/False. But right now sometimes it shows NULL. How can I fix this? 

id|name|active|
--|----|------|
 1|f1  |true  |
 2|f2  |true  |
 3|f3  |false |
 4|f4  |      |


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
resultset where it will have id, name and a custom boolean column. This boolean column is there to say if the feature
ispresent 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
>
>
>
>
>




Re: SQl help to build a result with custom aliased bool column

From
Szymon Lipiński
Date:
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





Re: SQl help to build a result with custom aliased bool column

From
Arup Rakshit
Date:
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.

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
resultset where it will have id, name and a custom boolean column. This boolean column is there to say if the feature
ispresent 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
>
>
>
>
>




Re: SQl help to build a result with custom aliased bool column

From
mariusz
Date:
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
> >
> >
> >
> >
> >
>
>
>




Re: SQl help to build a result with custom aliased bool column

From
Arup Rakshit
Date:
Hi,

Thanks for showing different ways to achieve the goal. So what should be the optimal way to solve this. I have an
compositeindex using company_id and feature_id columns for project_features table. 

I do ruby on rails development, where table names are plural always by convention. The tables I created above in
differentschema to ask question with sample data and test the query output. So they are little inconsistent, yes you
areright. 

Thanks,

Arup Rakshit
ar@zeit.io



> On 08-Apr-2019, at 4:36 PM, mariusz <marius@mtvk.pl> wrote:
>
> 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)




Re: SQl help to build a result with custom aliased bool column

From
mariusz
Date:
On Mon, 8 Apr 2019 19:21:37 +0530
Arup Rakshit <ar@zeit.io> wrote:

> Hi,
> 
> Thanks for showing different ways to achieve the goal. So what should
> be the optimal way to solve this. I have an composite index using
> company_id and feature_id columns for project_features table.

there are even more ways for that simple task. i can imagine some fancy
ways including lateral joins, cte returning subset of company_features
to produce positive results and be reused to produce set difference for
negative results, etc. but too fancy isn't good. the simpler the better.

those already mentioned should be enough. since you need all features
and all company_features for a given company id, there won't be any
much better.

it is enough to limit company_features to company_id which we already
do in join condition, and for big tables optimizer could use your index.

we can probably assume there won't be so much companies and so much
features to make really big table of three ids tuples to make optimizer
even consider using an index, but it may be good habit to think how we
could help optimizer to filter out unnecessary data sooner than later.

regards, mariusz

> I do ruby on rails development, where table names are plural always
> by convention. The tables I created above in different schema to ask
> question with sample data and test the query output. So they are
> little inconsistent, yes you are right.
> 
> Thanks,
> 
> Arup Rakshit
> ar@zeit.io
> 
> 
> 
> > On 08-Apr-2019, at 4:36 PM, mariusz <marius@mtvk.pl> wrote:
> > 
> > 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)
> 
> 
>