rockdai / sql-bricks

Transparent, Schemaless SQL Generation
http://csnw.github.io/sql-bricks
MIT License
213 stars 25 forks source link

Support for JOIN USING? #64

Closed sneakertack closed 9 years ago

sneakertack commented 9 years ago

I was trying to construct queries of the form SELECT * FROM t1 JOIN t2 USING (some_id), but there doesn't seem to be a way for the library to generate such queries. Is there an existing way to do so that I've overlooked?

If it doesn't exist but is desirable, then here are some possible syntax proposals:

// Add a using clause for select statements.
sql.select().from('t1').join('t2').using(['foo_id', 'bar_id']) // Supports multiple columns.
sql.select().from('t1').join('t2').using('foo_id') // String shorthand when only 1 column.

// Overload existing join clause.
sql.select().from('t1').join('t2', ['foo_id']) // Array argument => USING's column list.

On a related note, support for natural joins might also be considered.

sneakertack commented 9 years ago

Reasons for adding USING, instead of just using ON:

Reason 1 (weak): Less typing, no need to explicitly declare ON table1.some_id=table2.some_id if column name is the same.

Reason 2 (strong): Final data returned can end up differing due to duplicate column names. Example:

Given the input:

SELECT * FROM t1 LEFT OUTER JOIN t2 USING ('id')

Possible output:

id one two
1 cat green
2 dog red
3 giraffe

and then,

Given the input:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id=t2.id

Possible output:

id one two id
1 cat green 1
2 dog red 2
3 giraffe

When using some database connection libraries (e.g. node-postgres) that convert each row into an object, row 3 ends up converting into {id: 3, one: 'giraffe', two: null} in the former case and {one: 'giraffe', two: null, id: null} in the latter, due to duplicate assignment of id. Given that differences exist, library users would benefit from being able to choose between USING and ON, based on their needs.

prust commented 9 years ago

thanks for reporting this @sneakertack! I'm not sure how I missed it, but both USING and NATURAL are specified in SQL-92. Do you want to try your hand at adding one or both to sql-bricks?

sneakertack commented 9 years ago

Sure thing. Hope to deliver positive news in a few days.

sneakertack commented 9 years ago

Current State:

JOIN USING - Ready for merging in this pull request. NATURAL JOIN - Current progress is here. However, halted due to uncertainty over CSNW/sql-bricks#66. While thinking about how to add the new natural join methods I realized that the criteria checks in Join.toString() will likely need to change. What do you advise?