Hi,
I’m using in my project ActiveRecord::Base.connection.select_value and
ActiveRecord::Base.connection.select_rows several times to execute
complex queries. This basically works, but I need to check the params
in order to avoid sql-injection by myself. Even worse I’ve to handle
basic datatype conversions, e.g. choose 0/1 or ‘t’/‘f’ as appropiate
boolean for sqlite or MySQL.
Is there any way to execute a model-unrelated sql-query by passing an
array ([“select from model where =?”,“nice-
value”]) instead of a string?!
On Fri, Oct 30, 2009 at 3:57 AM, neomizer [email protected] wrote:
Is there any way to execute a model-unrelated sql-query by passing an
array ([“select from model where =?”,“nice-
value”]) instead of a string?!
Why can’t you use find_by_sql instead
of ActiveRecord::Base.connection.select_rows? You can start with one
Model
and query from another. For example, Apple.find_by_sql([“SELECT
apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM
apples,
bananas WHERE…”, something])
–
www.abetaday.com
On 30 Okt., 15:00, Gregory M. [email protected] wrote:
Is there any way to execute a model-unrelated sql-query by passing an
array ([“select from model where =?”,“nice-
value”]) instead of a string?!
Why can’t you use find_by_sql instead
of ActiveRecord::Base.connection.select_rows? You can start with one Model
and query from another. For example, Apple.find_by_sql([“SELECT
apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM apples,
bananas WHERE…”, something])
Because the values I want to retrieve are not part of any of my models
(e.g. aggregated stats). IIRC find_by_sql can only return instances of
the related model, therefore in the sql-statement I need to select
existing model-attributes, which is not possible in my case.
On Fri, Oct 30, 2009 at 12:10 PM, neomizer [email protected] wrote:
complex queries. This basically works, but I need to check the params
Model
and query from another. For example, Apple.find_by_sql([“SELECT
apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM
apples,
bananas WHERE…”, something])
Because the values I want to retrieve are not part of any of my models
(e.g. aggregated stats). IIRC find_by_sql can only return instances of
the related model, therefore in the sql-statement I need to select
existing model-attributes, which is not possible in my case.
You should be able to query any of the tables in your db *_production
whether it has a corresponding model or not. In the trite example I
cited
above, bananas could be a table in your db and not have a model
dedicated to
it. It’s not good practice. Just because you do Apple.find_by_sql
doesn’t
mean all your results have to be returned from the columns of an Apple
table.
–
www.abetaday.com
On 30 Okt., 17:34, Gregory M. [email protected] wrote:
in order to avoid sql-injection by myself. Even worse I’ve to handle
and query from another. For example, Apple.find_by_sql(["SELECT
whether it has a corresponding model or not. In the trite example I cited
above, bananas could be a table in your db and not have a model dedicated to
it. It’s not good practice. Just because you do Apple.find_by_sql doesn’t
mean all your results have to be returned from the columns of an Apple
table.
Interesting, I tried that before with something similar to the
following (the countries table has a column name, the orders table
doesn’t):
Order.find_by_sql(“select name from countries”)
=> [#, #]
which looks like two not fully instantiated Order objects to me. But
you’re totally right, I can actually call name on the resulting
objects:
Order.find_by_sql(“select name from countries”).first.name
=> “Germany”
Apart from being a bit confused about the behaviour, it solves my
problem. Thx Gregory!
neomizer wrote:
On 30 Okt., 15:00, Gregory M. [email protected] wrote:
Is there any way to execute a model-unrelated sql-query by passing an
array ([“select from model where =?”,“nice-
value”]) instead of a string?!
Why can’t you use find_by_sql instead
of ActiveRecord::Base.connection.select_rows? �You can start with one Model
and query from another. �For example, Apple.find_by_sql([“SELECT
apples.flavor as apple_flavor, bananas.flavor as banana_flavor FROM apples,
bananas WHERE…”, something])
Because the values I want to retrieve are not part of any of my models
(e.g. aggregated stats). IIRC find_by_sql can only return instances of
the related model, therefore in the sql-statement I need to select
existing model-attributes, which is not possible in my case.
Well, you can use the Calculations module for aggregate functions. But
as a matter of application design, it’s probably best to declare an
ActiveRecord model for any table your Rails app needs to know about.
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]