I’ve found there are some times where jumping straight to the database
is
easier than going through ActiveRecord (for instance: odd calculations
being performed or large sets of data being returned that don’t need to
be
instantiated into AR models). I can’t find an easy or accepted way to
bind
variables in a custom SQL clause. sanitize_sql_array is protected, so
you
would have to call it with send(), which just feels dirty.
ActiveRecord::Base.connection.quote() doesn’t play well with dates.
Am I missing a function that let’s me use placeholders like the
ActiveRecord where() with my plain SQL clauses?
If not, has this been a specific design to make sanitizing SQL fragments
difficult? Perhaps to discourage it? Or is it due to the different
database
drivers?
Rails is usually straight forward and intuitive, I’ve just found with
SQL
fragments I have to pull teeth to kinda get it to work.
On Monday, May 5, 2014 8:08:17 PM UTC+1, Tyler DeWitt wrote:
I’ve found there are some times where jumping straight to the database is
easier than going through ActiveRecord (for instance: odd calculations
being performed or large sets of data being returned that don’t need to be
instantiated into AR models). I can’t find an easy or accepted way to bind
variables in a custom SQL clause. sanitize_sql_array is protected, so you
would have to call it with send(), which just feels dirty.
ActiveRecord::Base.connection.quote() doesn’t play well with dates.
Am I missing a function that let’s me use placeholders like the
ActiveRecord where() with my plain SQL clauses?
If not, has this been a specific design to make sanitizing SQL fragments
difficult? Perhaps to discourage it? Or is it due to the different database
drivers?
Rails is usually straight forward and intuitive, I’ve just found with SQL
fragments I have to pull teeth to kinda get it to work.
sanitize_sql was made protected a while back (I think rails 2) if my
memory
is correct. I think the position was to make it awkward to use in
controllers etc. since that was what people were doing
Fred
On Monday, May 5, 2014 3:08:17 PM UTC-4, Tyler DeWitt wrote:
ActiveRecord where() with my plain SQL clauses?
If not, has this been a specific design to make sanitizing SQL fragments
difficult? Perhaps to discourage it? Or is it due to the different database
drivers?
Rails is usually straight forward and intuitive, I’ve just found with SQL
fragments I have to pull teeth to kinda get it to work.
Jumping straight to the database is discouraged in Rails and it’s
something
I’ve only rarely done or seen a need to do. The main reason is that one
of
the goals of ActiveRecord is to make your application database agnostic.
You can use your application with MySQL, PostgreSQL, etc. and not have
to
worry about knowing the specific ins and outs of each database, it just
works. I would argue that has gotten a little muddy as 4.1 has a few
features that are specific to PostgreSQL. At any rate, writing SQL
fragments is discouraged because you potentially now make things
specific
to one database management system (or even worse, a specific version of
a
database management system) rather than relying on ActiveRecord to
handle
the specifics. You’ve started to stray from the conventions and,
therefore, start to lose the efficiencies and make it more difficult to
support the application (particularly if another developer inherits the
application).
On Wednesday, May 7, 2014 4:42:15 AM UTC-4, Frederick C. wrote:
Jumping straight to the database is discouraged in Rails and it’s
therefore, start to lose the efficiencies and make it more difficult to
Active Record can only express a subset of queries so SQL fragments are
inevitable, unless you never need a NOT, OR, LIKE etc. condition (although
rails 4.1 does handle not for you)
Fred
I’m not sure I follow you, but I will admit this isn’t my strongest area
of
knowledge. Most OR or LIKE conditions I can handle in a where
statement.
For example:
User.where(“name like :name OR department=:dept”, name: “A%”, dept: 101)
would produce all users whose name begins with A or are in department
101.
You may have examples that can’t be used in where, the only one I’ve
run
into is NOT, which, as you said, was added in Rails 4.1. I have had
some
other database specific (postgreSQL) situations where I’ve used direct
SQL
statements, but it’s rare.
I can think of another area which would bite you regularly, which are
booleans. Some databases use 1 and 0, some T and F, some Y and N.
Rails
abstracts this away. For example, the following would work regardless of
database used:
Order.where(‘approved=?’, true)
On Wednesday, May 7, 2014 1:02:36 AM UTC+1, mike2r wrote:
not have to worry about knowing the specific ins and outs of each database,
it just works. I would argue that has gotten a little muddy as 4.1 has a
few features that are specific to PostgreSQL. At any rate, writing SQL
fragments is discouraged because you potentially now make things specific
to one database management system (or even worse, a specific version of a
database management system) rather than relying on ActiveRecord to handle
the specifics. You’ve started to stray from the conventions and,
therefore, start to lose the efficiencies and make it more difficult to
support the application (particularly if another developer inherits the
application).
I think database agnosticism is a bit of a pipe dream at the application
level, unless all you are ever doing is updating records one at a time
or
running very simple queries. Differences in query execution or odd side
corners that you hadn’t realised you rely on (eg typecasting behaviour:
in
mysql “select 0 = ‘z’” returns 1, on other databases it returns 0) will
bite you.
Active Record can only express a subset of queries so SQL fragments are
inevitable, unless you never need a NOT, OR, LIKE etc. condition
(although
rails 4.1 does handle not for you)
Fred
On Wednesday, May 7, 2014 10:10:25 PM UTC+1, mike2r wrote:
inevitable, unless you never need a NOT, OR, LIKE etc. condition (although
rails 4.1 does handle not for you)
I’m not sure I follow you, but I will admit this isn’t my strongest area
of knowledge. Most OR or LIKE conditions I can handle in a where
statement. For example:
User.where(“name like :name OR department=:dept”, name: “A%”, dept: 101)
We’re talking semantics then - I would call what’s in your call to where
a
sql fragment.
Fred