SQl help to build a result with custom aliased bool column

Started by Arup Rakshitabout 7 years ago7 messagesgeneral

ar@zeit.io

about 7 years ago

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

Arup Rakshit

ar@zeit.io

about 7 years ago

In reply to: Arup Rakshit (#1)

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

I knew that will be more compact way. Thanks for showing it. One thing I still would like to handle is that, to make sure the 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

Show quoted text

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

Import Notes

Reply to msg id not found: CAFjNrYtsiGU4UXys0_Mu4-X+KOWqado9CrriwLRswsTexuN8fw@mail.gmail.com

Szymon Guz

mabewlun@gmail.com

about 7 years ago

In reply to: Arup Rakshit (#1)

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

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:

Show quoted text

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

Arup Rakshit

ar@zeit.io

about 7 years ago

In reply to: Szymon Guz (#3)

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

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

Show quoted text

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

mariusz

marius@mtvk.pl

about 7 years ago

In reply to: Arup Rakshit (#4)

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

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

Show quoted text

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

Arup Rakshit

ar@zeit.io

about 7 years ago

In reply to: mariusz (#5)

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

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.

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

Show quoted text

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)

mariusz

marius@mtvk.pl

about 7 years ago

In reply to: Arup Rakshit (#6)

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

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

Show quoted text

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)