Rails Where Clause PG::SyntaxError: ERROR: subquery must return only one column

Question

I want to take advantage of rails scoping with a custom sql query. You can click this post for I'm trying to do - especially in ActiveAdmin.

How to use a find_by_sql query for an ActiveAdmin index page?

Basically I'm trying to do something like this

class YourModel < ActiveRecord::Base
  scope :my_scope, where('some custom SQL')
  scope :my_other_scope, where('some other custom SQL')
end

This is my query I tried out first in Rails C

LesleyGrade.where('select distinct STC_TERM_GPA,
    TERM,
    last,
    first
    from lesley_grades
order by first, term ASC')

Essentially it's a subquery in a where clause. I only want to return a distinct STC_TERM_GPA for per person per row.

These are my attributes in a lesley_grades table, which might help in understand the information I'm trying to retrieve.

id
user_id
lesley_id
last
first
active
site
cohort
section
sections_title
faculty
completed_term_cred
term
sec_start_date
sec_end_date
grade
stc_cred
active_program
most_recent_program
intent_filed
stc_term_gpa
sta_cum_gpa
start_term
prog_status
last_change_date
created_at
updated_at

So I wanted to see if this would work in Rails C first and this is what I got.

  LesleyGrade Load (51.1ms)  SELECT "lesley_grades".* FROM "lesley_grades"  WHERE (select distinct STC_TERM_GPA,
    TERM,
    last,
    first
    from lesley_grades
order by first, term ASC)
PG::SyntaxError: ERROR:  subquery must return only one column
LINE 1: ...ECT "lesley_grades".* FROM "lesley_grades"  WHERE (select di...
                                                             ^
: SELECT "lesley_grades".* FROM "lesley_grades"  WHERE (select distinct STC_TERM_GPA,
    TERM,
    last,
    first
    from lesley_grades
order by first, term ASC)
=> #<LesleyGrade::ActiveRecord_Relation:0x3fcb44d60944>

I'm not sure how to fix

PG::SyntaxError: ERROR:  subquery must return only one column

You don't need the where. Also, why are you selecting four columns if you only want one? — steve klein
– steve klein, Commented May 6, 2015 at 1:01
I'm using rails scopes and it looks likes I can pass a where clause (look at the SO post linked - but if not I"ll update it in my post). And I want those four columns because the UI calls for it. — gary1410
– gary1410, Commented May 6, 2015 at 1:10

joshua.paling · Accepted Answer · 2015-05-06 02:15:39Z

1

Not exactly sure what you're trying to acheive, but something like this should at least resolve the postgres error you're seeing:

LesleyGrade.where('STC_TERM_GPA IN 
    (SELECT STC_TERM_GPA FROM 
        (SELECT DISTINCT STC_TERM_GPA, TERM, last, first
        FROM lesley_grades
        order by first, term ASC) AS results
    )'
)

There's a few levels there:

The deepest level says "get me all rows which have a distinct / unique combination of the columns STC_TERM_GPA, TERM, last, and first

The next layer says "Of those results, get rid of the other columns and just give me the STC_TERM_GPA column.

The outer-most layer says "Give me all rows where the STC_TERM_GPA value is in the set of STC_TERM_GPA we just selected.

EDIT: it sounds like you don't what a WHERE clause at all. You want something like:

LesleyGrade.select('STC_TERM_GPA, TERM, last, first').group('STC_TERM_GPA').order('first, term ASC')

That's untested. But to select multiple columns, but restrict to distinct values of one column, you'll want to use SQL's GROUP BY clause, which is available in Rails' ActiveRecord through the group method.

BTW, select distinct STC_TERM_GPA, TERM, last, first from lesley_grades order by first, term ASC will give you results which are unique on the COMBINATION of STC_TERM_GPA, TERM, last, first, not on JUST STC_TERM_GPA. I've assumed that while you want all those columns, you only want DISTINCT on the STC_TERM_GPA.

edited May 6, 2015 at 2:15

answered May 6, 2015 at 1:33

joshua.paling

14k4 gold badges47 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gary1410 Over a year ago

thanks for the help - it's strange cause I'm only suppose to get back 437 rows back when I use the this query select distinct STC_TERM_GPA, TERM, last, first from lesley_grades order by first, term ASC which is what I want. However when I use your query I'm getting back all results - which is 1440. I only want the distinct 437 records where each user will have a distinct TERM_GPA.

gary1410 Over a year ago

You are correct about "wanting DISTINCT on the STC_TERM_GPA." however, there are cases in which the GPA per user per term might be the same. I ended up doing something like this

LesleyGrade.select('STC_TERM_GPA, max(TERM), max(last), max(first)').group('STC_TERM_GPA').order('max(first), max(term) ASC')

because I got this error `PG::GroupingError: ERROR: column "lesley_grades.term" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT STC_TERM_GPA, TERM, last, first FROM "lesley_grades" not sure if that really fixes the issue - apologies, my sql is terrible

Collectives™ on Stack Overflow

Rails Where Clause PG::SyntaxError: ERROR: subquery must return only one column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related