DB query: Finding only the id values

My solution works, but I wonder if there is a better one.

I have a model (:cards), which has a foreign key :box_id. I am
interested in the id's of those cards which have a certain box_id.
Currently I assume that I can easily hold an array of all thos :cards in
memory (i.e. no cursor needed).

This is my current solution:

Card.where(box_id: params[:box_id]).map {|c| c.id }.each do |cid|
  # .... Do something with cid
end

This is compact, but I don't like the fact that first, all data from the
retrieved Card objects needs to be stored in memory at least temporarily
(there is even a 'text' field in Card!), but only the id is needed.

I thought as an alternative to use find_by_sql, but the API
documentation warns that this should be only used as a "last resort",
because it makes us dependent on the syntax for a particular database.
Although in my case, the SQL query would be so simple that I don't fear
I would run into compatibility problems when exchanging one database for
another, I wonder whether there is a simpler solution, using just
ActiveRecord functions.

Ronald

Assuming that you have the relationships setup accordingly (so card
belongs_to box and box has_many cards or something similar) then to
get the cards belonging to a certain box you can just use
@cards = @box.cards
then to get the id of each box in just use box.id

If the above does not make sense then I suggest you work right through
a good tutorial such as railstutorial.org, which is free to use
online, which will show you the basics of Rails.

Colin

As Colin pointed out,

(assume that ) box = Box.find(box_id)

box.cards # has the cards you want *if* you need all the attributes

box.cards.pluck(:id) # builds a query to fetch *only* the card ids

HTH,

Card.where(box_id: params[:box_id]).map {|c| c.id }.each do |cid|
# .... Do something with cid
end

This is compact, but I don't like the fact that first, all data from the
retrieved Card objects needs to be stored in memory at least temporarily
(there is even a 'text' field in Card!), but only the id is needed.

I wonder whether there is a simpler solution, using just
ActiveRecord functions.

As Colin pointed out,

(assume that ) box = Box.find(box_id)

box.cards # has the cards you want *if* you need all the attributes

box.cards.pluck(:id) # builds a query to fetch *only* the card ids

HTH,
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan

What about box.card_ids ? Doesn't that do much the same thing?

Walter

Yes, but 'pluck' is more all-purpose :slight_smile: -- it enables fetching arbitrary
(and if desired, multiple) fields.

Colin Law wrote in post #1149753:

Assuming that you have the relationships setup accordingly (so card
belongs_to box and box has_many cards or something similar)

Actually I have both (belongs_to in :cards and has_many :cards in box);
would it be sufficient to have only one?

then to
get the cards belonging to a certain box you can just use
@cards = @box.cards
then to get the id of each box in just use box.id

I see. Thus, applying your suggestion to my case, it would be:

Box.find_by(params[:box_id]).cards.map {|c| c.id }.each do |cid|
  # .... Do something with cid
end

But this solution still has the effect of having an array of all the
Cards, so I don't really see an improvement over my original solution.
Or did I miss something?

Hassan Schroeder wrote in post #1149759:

No. You can watch the queries generated for confirmation. Or look
at the source. Or both. :slight_smile:

The short version: box.cards doesn’t actually load the records until they are needed; that’s why things like box.cards.limit(10) can work. box.cards returns a Relation, which can return records (if you call something on it that requires them, like to_a or each) but can also be used to construct SQL queries.

This is a bit tricky to see in the console, since typing box.cards there calls inspect on the Relation, which loads all the records.

For more detail, see the source.

–Matt Jones

Colin is right here, just use relationships.

However, as you are considering writing raw SQL, keep in mind the danger of SQL injection. Beyond the reason stated to keep your SQL database-independant (which is strange advice IMO since it is very rare to move between data stores on a large project, and even if you do it is pretty easy to re-write SQL), **the most important thing here is that you don’t a security vulnerability for SQL injection. **

In your original example, Card.where(box_id: params[:box_id]) is actually safe, since where with a hash of parameters sanitizes the data. However, Card.where(“box_id: #{params[:box_id]”) is NOT SAFE – don’t ever do that.

Check out http://rails-sqli.org and take some time to learn how to run http://brakemanscanner.org against your code (it’s very easy)

But actually Colin is right, you’re over engineering, just use relationships and don’t worry about writing your own SQL. FWIW, I do sometimes write my own SQL, but only when I absolutely have to because ActiveRecord can’t capture what I need to do in SQL (which is very, very rare)

-Jason

In rails 3, I believe find_by(params[:box_id]) **is insecure and creates a SQL injection attack vector. **

I think in Rails 4 that is fixed and is secure.

Either way, why would you write such a complicated statement using map (this has go to be inefficient because you’re creating a lot of objects then plucking their ids with map – remember, object instantiation is expensive in active record)

did you try something like this?

box = Box.find(params[:box_id])

box.cards.each do |card|

#... do something with each card

end

It took me several years of working with Rails to understand this nuance, and I believe it is poorly documented in the AR guide. “reading the source” may be a good idea for some, but remember the Rails source isn’t easy for everyone to read (although a good idea!).

Since it is so important to how AR works, I think this facet of AR should be documented better in the AR guide (specifically, that the AR methods return ActiveRelation objects which don’t actually fetch anything until you want to look at them). It’s a brilliant implementation pattern, but counter-intuitive to newbies.

-Jason

Jason Fb wrote in post #1150058:

However, as you are considering writing raw SQL, keep in mind the danger
of SQL injection. Beyond the reason stated to keep your SQL
database-independant (which is strange advice IMO since it is very rare
to move between data stores on a large project,

Not so rare. For example, I do the development with SQLite, but the
production is on Heroku and uses Postgres, and customers might want to
use MySql.

and even if you do it is
pretty easy to re-write SQL), the most important thing here is that you
don't a security vulnerability for SQL injection.

Oh, you are absolutely right. I see the danger.

These are the times where I'm missing Perl's concept of "tainted"
strings...

Ronald

Jason Fb wrote in post #1150062:

It took me several years of working with Rails to understand this
nuance, and I believe it is poorly documented in the AR guide. "reading
the source" may be a good idea for some, but remember the Rails source
isn't easy for everyone to read (although a good idea!).

I don't mind so much *reading* the source then relying on the
information we get from it. Even if you see that something is
implemented in a particular way, you don't know whether it is just
incidentally (and in the next minor release will be implemented
differently), or whether it is an undocumented feature (which might or
might not make it in the API), or whether it is really part of the
"official" interface. If something is specified in the API, there is at
least good hope that we don't run into incompatibilities (although when
looking at the evolution of Rails, this principle seems to have been
violated occasionally).

Since it is so important to how AR works, I think this facet of AR
should be documented better in the AR guide (specifically, that the AR
methods return ActiveRelation objects which don't actually fetch
anything until you want to look at them). It's a brilliant
implementation pattern, but counter-intuitive to newbies.

It's just "lazy evaluation" at work, and as such not so much
"counter-intuitive". It's only that it is not obvious that the design
choice was done in this way...

Ronald

You say tainted, and I hear Soft Cell...

Walter

Perhaps “counter-intuitive” is the wrong word choice. It is something many people new to the framework get tripped up on. I think this is due to the fact that in console, if you type Person.with_blue_eyes (assuming with_blue_eyes is a scope), they see the SQL execute and the result immediately.

if you type Person.with_blue_eyes; puts “x”; you don’t see the SQL execute (in fact, it doesn’t). It’s the fact that console calls inspect on the last thing typed that makes it counter-intutive to newbies.

And of course, you have to know what lazy evaluation is and why AR does it, which is the secret sauce, also not something newbies can grok easily.

I guess all I really want is for the AR guide to have a section that discusses this nuance (it doesn’t) as I find this is something that is easy to trip up on and something I find myself explaining to people who are new to Rails all the time.

-Jason

Walter Davis wrote in post #1150083:

You say tainted, and I hear Soft Cell...

This too, of course :wink:

Ronald