current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

On Mysql it works correctly.

PG::Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ) ORDER BY programs.rating DESC, program_sc... ^ :

Query:

SELECT DISTINCT "programs".* FROM "programs" INNER JOIN "program_schedules" ON "program_schedules"."program_id" = "programs"."id" WHERE (programs.rating >= 5 AND program_schedules.start >= '2012-11-03 23:14:43.457659') AND (ptype = 'movie') ORDER BY programs.rating DESC, program_schedules.start DESC

Rails code:

@data = Program.joins(:program_schedules).where('programs.rating >= ? AND program_schedules.start >= ?',5, Time.now).order('programs.rating DESC, program_schedules.start DESC').uniq

I have tried with

Program.select("programs.*, program_schedules.*).joins(:program_schedules).where(...

but, in this way, when I'm going to read

@data.program_schedules

I get a nil value (When I know there are no nil values)

PostgreSQL 9.2 (Heroku), Ruby 1.9.2

Some info about my DB:

class Program < ActiveRecord::Base
   has_many :program_schedules
end


class ProgramSchedule < ActiveRecord::Base
  belongs_to :program
end

Schema.db

 create_table "program_schedules", :force => true do |t|
        t.integer  "program_id"
        t.datetime "start"
  end

  create_table "programs", :force => true do |t|
    t.string  "title",
    t.string  "ptype"
  end

EDIT: I don't need to order "programs_schedules" because I need all programs_schedules in my array related to that program.

share|improve this question
    
That's a complex enough query where I would just use find_by_sql –  n_i_c_k Nov 3 '12 at 23:39
    
As always: version of PostgreSQL? Should go without saying for programmers .. Table definitions are never a bad idea either. Would clarify where ptype is from. –  Erwin Brandstetter Nov 4 '12 at 0:07
    
ok,I've updated my question post –  sparkle Nov 4 '12 at 11:23

1 Answer 1

up vote 7 down vote accepted

You query is ambiguous in two ways:

ptype is not table-qualified and you did not disclose the table definitions. So the query is ambiguous.

More importantly, you want to:

ORDER BY programs.rating DESC, program_schedules.start DESC

At the same time, however, you instruct PostgreSQL to give you DISTINCT rows from programs. If there are multiple matching rows in program_schedules, how would Postgres know which one to pick for the ORDER BY clause? The first? Last? Earliest, latest, greenest? It's just undefined.

Generally, the ORDER BY clause cannot disagree with the DISTINCT clause, that's what the error message tells you.

Based on a few assumptions, filling in for missing information, your query could look like this:

SELECT p.*
FROM   programs p
JOIN   program_schedules ps ON ps.program_id = p.id
WHERE  p.rating >= 5
AND    ps.start >= '2012-11-03 23:14:43.457659'
AND    p. ptype = 'movie'   -- assuming ptype is from programs  (?)
GROUP  BY p.id              -- assuming it's the primary key
ORDER  BY p.rating DESC, min(ps.start) DESC;  -- assuming smallest start

Also assuming you have PostgreSQL 9.0 or later, which is required for this to work. (Primary key covers whole table in GROUP BY.)

As for:

On Mysql it works correctly.

No it doesn't. It "works", but in mysterious ways rather than "correctly". MySQL allows for all sorts of weird mistakes and goes out of its way (and the SQL standard) to avoid having to throw exceptions - which is a very unfortunate way to deal with errors. It regularly comes back to haunt you later. Demo on Youtube.

Question update

I need all programs_schedules in my array related to that program.

You might want to add:

SELECT p.*, array_agg(ps.start ORDER BY ps.start)
share|improve this answer
1  
+1! Thanks for the video link in particular. I'm keeping that around for next time I find myself saying the same thing. –  willglynn Nov 4 '12 at 2:40
    
Ok, thanks. I've updated my question post. Now I'm going to check your query –  sparkle Nov 4 '12 at 11:42
    
@Erwin Brandstetter: Thanks, it's works. If I need to get –  sparkle Nov 4 '12 at 14:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.