CODEWITHSUNDEEP
javascriptsqlpostgresqlsequelize.js
Zachary J Rollyson
Zachary J Rollyson

Reputation: 153

Join tables in SQL with duplicate column names into dot notation without having to list every column with an alias

I'm going to guess this question comes up fairly frequently and is the result of several missteps and design flaws on my part but I'm kind of stuck now and I don't know where to look because I'm too much of an SQL novice.

The basic gist of the matter is that I started building my application with Sequelize which has an include function that essentially joins tables as nested object. But now Sequelize isn't working for me and I need to do raw queries but I still have application code relying on the structure Sequelize would give. Basically I need to recreate the Sequelize include functionality but not so verbose.

So, to summarize the environment before I get into details I've got: PostgreSQL & JavaScript with Sequelize & Knex (using to help build queries programmatically).

So I have table foo

id | name | bar_id
---+------+-------
1  | Joe  | 1
2  | Jan  | 2

table bar

id | pet | vet_id
---+-----+-------
1  | cat | 1
2  | dog | 1

table vet

id | name
---+-----
1  | Dr. Elsey

The ideal result would look something like:

id | name | bar.id | bar.pet | bar.vet.id | bar.vet.name
---+------+--------+---------+------------+-------------
1  | Joe  | 1      | cat     | 1          | Dr. Elsey
2  | Jan  | 2      | dog     | 1          | Dr. Elsey

Sequelize achieves this by doing something like this (paraphrasing select):

select
  foo.id,
  foo.name,
  bar.id as "bar.id",
  bar.pet as "bar.pet",
  "bar->vet".id as "bar.vet.id",
  "bar->vet".name as "bar.vet.name"
from foo
  left outer join bar on foo.bar_id = bar.id
  left outer join vet as "bar->vet" on bar.vet_id = vet.id;

Is there any way to do this without enumerating all of these select aliases? Or is there some better sort of output I could try at?

Essentially I want to build objects like this:

{
  id: 1,
  name: 'Joe',
  bar: {
    id: 1,
    pet: 'cat',
    vet: {
      id: 1,
      name: 'Dr. Elsey'
    }
  }
};

Upvotes: 0

Views: 500

Answers (1)

khampson
khampson

Reputation: 15316

I wouldn't classify it as easier or necessarily better -- it's more complicated for sure -- but if you really wanted to avoid the aliasing, you could try some SQL meta programming using Postgres's built-in information_schema.columns table to generate dynamic SQL for you.

Something along the lines of this adapted to your exact needs:

select ' select json_agg(x) from (select '
      || array_to_string(array_agg(table_schema
      || '.' || table_name || '.'
      || column_name || ' as "' || table_schema || '.' || table_name || '.'
      || column_name || '"'), ', ') ||
   ' from public.foo join public.bar using(id)) as x'
from information_schema.columns 
where table_schema = 'public'
  and table_name in ('foo', 'bar');

The above query will return each row as a JSON blob with fully-schema-qualified columns. You could use Postgres's other built-in JSON functions to modify this to fit your needs.

Note: It's important to be mindful of the potential for SQL injection when constructing queries like this. That is, be very careful if you end up interpolating other variables from your script into this dynamically generated SQL. If you find yourself needing to do that, you may want to wrap the dynamic SQL inside of a pure SQL function you create that uses a binding variable and takes it as an argument, as described on the Bobby Tables site.

Upvotes: 1

Related Questions