Обсуждение: SQl help to build a result with custom aliased bool column
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
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 > > > > >
Hey,
you could just use
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
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 > > > > >
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
> >
> >
> >
> >
> >
>
>
>
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)
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) > > >