New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Pluck not working for explicit column names #1619

Closed
marudor opened this Issue Aug 10, 2016 · 5 comments

Comments

Projects
None yet
3 participants
@marudor

marudor commented Aug 10, 2016

Example:

knex
.pluck('foo.id')
.from('foo')
.innerJoin('bar', 'bar.foo_id', 'foo.id')

If foo and bar both have an idfield pluck will resolve to an array of false instead of the actual result.

@elhigu elhigu added the bug label Aug 12, 2016

@tgriesser tgriesser closed this in 3a5250d Sep 13, 2016

tgriesser added a commit that referenced this issue Sep 14, 2016

Merge branch 'master' into refactor
* master:
  release 0.12.0
  Remove docs, in favor of https://github.com/knex/documentation (#1666)
  Revert to generic pool (#1665)
  Fix #1619
  Fix use of const in test suite for node 0.12
  Moving bin/cli outside of src to allow install from master
  Deprecate Knex.Promise
  Simplifying internal client structure
  add With Clause (#1599)
  Simplify transaction classes
  Simplify formatter use
  Deprecate VERSION, update changelog
  Fix PG string escaping behavior (#1661)
@rolandstarke

This comment has been minimized.

rolandstarke commented May 31, 2018

With this fix I get the SQL error:

ER_NON_UNIQ_ERROR: Column 'id' in field list is ambiguous

sadly .pluck(knex.raw('id'); #817 (comment) dosn't work anymore too.

@elhigu

This comment has been minimized.

Collaborator

elhigu commented May 31, 2018

Im surprised if it has ever worked. .select(’id’).pluck(’id’) might

@rolandstarke

This comment has been minimized.

rolandstarke commented May 31, 2018

.select(’id’).pluck(’id’) results in SELECT id, id

a simple workaround is to not use pluck and instead exctract the column yourself:

.select({alias: knex.raw('1 + 1')}).map((row) => row.alias);

@elhigu

This comment has been minimized.

Collaborator

elhigu commented May 31, 2018

yeah, true. I now tried that out how does it actually work. Looks like pluck() indeed has been intended to combine .select() on db server side and then plucking that selected id from result 👍

It seems to work just as expected, but yeah it really doesn't support knex.raw. What is the actual use case why would you need raw there? Doing some computation with column value and plucking that result? Probably it could be supported quite easily...

@rolandstarke

This comment has been minimized.

rolandstarke commented May 31, 2018

yes you are right, I wanted the values of a computed column.

const language = 'en';
const questionIds = await knex
    .select({ id: knex.raw('DISTINCT COALESCE(dublicate_of, id)') })
    .from('question').where('language', language)
    .map((row) => row.id);

the table is like

id question language dublicate_of
1 How are you? en null
2 How are u? en 1
3 Wie gehts? de 1

The query should give me all canonical question ids available in my language.

I first tried .pluck('id') but it did not use the alias, but selected a new column.
With map I am fine now, its short and no new api to learn. So actually I am fine :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment