Activerecord solution to postgres-mysql problems

Question

I have the following scopes in my Job model and it seems there's a problem with some SQL discrepancies in my statements. Our dev db was mysql and apparently heroku has postgres and it keeps complaining about as file_count for some reason.

I plan to just convert these scopes into class methods or at least change the SQL statements into active record ones so it could be db independent. Is this possible at all and how would I start on this one?

I'm planning to keep is_active because I'm pretty sure it works as it's a simple scope statement, but with_unclassified_files_available_count needs a refactor and I think an AR refactor would be a good idea(if you think this isn't a good idea, please tell me so, I'm open to suggestions)

Here's the code:

scope :is_active, where(:active => true)
scope :with_unclassified_files_available_count, where("audio_files.category_id IS NULL")
                                              .joins(AUDIO_FILES)
                                              .select("jobs.*, COUNT(*) AS file_count")
                                              .group("jobs.id")
                                              .order("batch_identifier DESC")
scope :has_files_available, with_unclassified_files_available_count.having("count(*) > 0")
scope :available_to_work_on, is_active.has_files_available

Additional info:

Job has many audio_files and AudioFile belongs to job.

neezer · Accepted Answer · 2012-08-14 22:54:08Z

5

.select("jobs.*, COUNT(*) AS file_count")
.group("jobs.id")

No database, except MySQL, will accept this construction. Every column you mention in the SELECT section that is not in an aggregate function (like COUNT, MIN, MAX) MUST be in the GROUP BY. You only have jobs.id in the GROUP BY.

You have to change your query and mention all columns in the GROUP BY.

MySQL also has problems with this construction, in many cases it produces strange/wrong results. MySQL has implemented the SQL-mode ONLY_FULL_GROUP_BY to turn off this behaviour, MySQL will now also reject queries like this.

Ps. Don't use * in your queries, nobody has any idea what the results might be.

edited Aug 14, 2012 at 22:54

neezer

20.7k33 gold badges131 silver badges223 bronze badges

answered Feb 9, 2011 at 8:22

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Collectives™ on Stack Overflow

Activerecord solution to postgres-mysql problems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related