Rails activerecord query works in sqlite not in MySql

Question

I have a query problem with sqlite and MariaDB. In development I use sqlite but in server we use MariaDB. In development following query works

Author.includes(:books, employes: :universities).where("universities.id is ? and books.year is ?",
                   @author.employes.first.universities.first.id, @book.jahr).references(:employes, :books)

but in production this gives error. When I change is to =, it works in server but does not produce any result in local.

Author.includes(:books, employes: :universities).where("universities.id = ? and books.year = ?",
                   @author.employes.first.universities.first.id, @book.jahr).references(:employes, :books)

This works in server but no result is produced in local.

Edit What if I have something like this?

Author.includes(:books, employes: :universities).where("universities.id = ? and books.year = ? and employes.name like ? and id IN ?",
                       @author.employes.first.universities.first.id, @book.jahr, "%#{@employe.name}%", [1,2,3]).references(:employes, :books)

First: Basic architecture design failure using different database servers on development and production ... Second check the generated SQL queries. — Raymond Nijland
– Raymond Nijland, Commented Aug 2, 2017 at 10:08

Rajdeep Singh · Accepted Answer · 2017-08-02 14:53:14Z

2

Firstly as Raymond said, never use different databases in development and production environment.

But for now you can avoid the SQL syntax errors by going ActiveRecord way, try this

Author.includes(:books, employes: :universities).where(
  universities: { id: @author.employes.first.universities.first.id },
  books: { year: @book.jahr }
).references(:employes, :books)

Since there's no ActiveRecord alternative for like, you can try this (it will work since like is valid in both MySQL and SQLite)

Author.includes(:books, employes: :universities).where(
  authors: { id: [1,2,3] },
  universities: { id: @author.employes.first.universities.first.id },
  books: { year: @book.jahr }
).where('employes.name like ?', "%#{@employe.name}%").references(:employes, :books)

Hope that helps!

edited Aug 2, 2017 at 14:53

answered Aug 2, 2017 at 10:11

Rajdeep Singh

17.9k6 gold badges58 silver badges80 bronze badges

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

2 Comments

Cong Chen Over a year ago

If pass where a Hash, it doesn't need to use references

asdfkjasdfjk Over a year ago

Hi! it worked great. Could you show me what would be the ActiveRecord way of doing the query in the Edit in question above?

Collectives™ on Stack Overflow

Rails activerecord query works in sqlite not in MySql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related