Rails sql issue with a simple where statement

Question

I am trying to get a very simple statement working.

Node.where("nodeid = ?", nstart).select('id')

Results in

Parameters: {"utf8"=>"✓", "authenticity_token"=>"WpSpzLzaFUx8QgFbwEfygQUkkqvbUgZl8Hh0UxJvT8E=", "edge"=>{"kind"=>"IsA", "start_id"=>"blabla", "end_id"=>"bliblib", "property1"=>"bloblbo"}, "commit"=>"Create Edge"}
   (0.0ms)  begin transaction
  Node Load (0.1ms)  SELECT  "nodes".* FROM "nodes"  WHERE "nodes"."id" = ? LIMIT 1  [["id", 0]]
  CACHE (0.0ms)  SELECT  "nodes".* FROM "nodes"  WHERE "nodes"."id" = ? LIMIT 1  [["id", 0]]
   (0.0ms)  rollback transaction

It should be just a ´select nodes.id from nodes where nodeid = blabla´ The limit doesn't matter.

However if I add .first.

Node.where("nodeid = ?", nstart).select('id').first

I get

Parameters: {"utf8"=>"✓", "authenticity_token"=>"WpSpzLzaFUx8QgFbwEfygQUkkqvbUgZl8Hh0UxJvT8E=", "edge"=>{"kind"=>"IsA", "start_id"=>"blabla", "end_id"=>"bliblib", "property1"=>"bloblbo"}, "commit"=>"Create Edge"}
  Node Load (0.1ms)  SELECT  "nodes"."id" FROM "nodes"  WHERE (nodeid = 'blabla')  ORDER BY "nodes"."id" ASC LIMIT 1
  Node Load (0.1ms)  SELECT  "nodes"."id" FROM "nodes"  WHERE (nodeid = 'bliblib')  ORDER BY "nodes"."id" ASC LIMIT 1
   (0.0ms)  begin transaction
  Node Load (0.1ms)  SELECT  "nodes".* FROM "nodes"  WHERE "nodes"."id" = ? LIMIT 1  [["id", 0]]
  CACHE (0.0ms)  SELECT  "nodes".* FROM "nodes"  WHERE "nodes"."id" = ? LIMIT 1  [["id", 0]]
   (0.1ms)  rollback transaction

The first select now is what it should be but the follow up is again like before and seems to determine the eventual return value (because it doesn't work either). I just want the id when I only know the nodeid which basically is the name of the node.

What is going on in Rails here?

I really doubt the first output and I think you gave wrong output, I don't see any relation with the code Node.where("nodeid = ?", nstart).select('id') — The Lazy Log
– The Lazy Log, Commented Sep 6, 2014 at 13:43
Yes but I triple checked. It must be some caching problem. I tried changing the values each time and restarting the server. Now I get the correct stmt with the first solution but it returns a Node object. I seem to need the first too. — samst
– samst, Commented Sep 6, 2014 at 15:02

Mohammad AbuShady · Accepted Answer · 2014-09-06 15:06:43Z

1

If you just want the id value you could do this

Node.where("nodeid = ?", nstart).limit(1).pluck(:id).first

This would return 1 integer with the value

EDIT:

ok scratch that, i guess you don't really need to use limit so a simple first would just do

Node.where("nodeid = ?", nstart).first[:id]

or

Node.where("nodeid = ?", nstart).first.id

edited Sep 6, 2014 at 15:06

answered Sep 6, 2014 at 13:20

Mohammad AbuShady

43.3k11 gold badges81 silver badges89 bronze badges

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

7 Comments

D-side Over a year ago

limit(1) and first seem to do the same things. But since limit(1) still should return a collection of values (if not called with 1), whereas first only one: limit(1) shouldn't be there.

Mohammad AbuShady Over a year ago

well I just double checked, I guess first does add limit by it self

samst Over a year ago

It does not work though. .first['id'] does the same as my first without any parameters. ´SELECT "nodes".* FROM "nodes" WHERE (nodeid = 'Rails') ORDER BY "nodes"."id" ASC LIMIT 1´ It does not even pick the id but * and I still don't understand what the follow up transaction is. Edge.new should not cause a transaction on the node table at all.

Mohammad AbuShady Over a year ago

well I don't understand, I write the same query on my app and it does a select id query, not select *, what you have in your question should run correctly

samst Over a year ago

I don't understand it either. I managed to get this far. Node Load (0.1ms) SELECT "nodes"."id" FROM "nodes" WHERE (nodeid = 'x1') LIMIT 1 #<Node:0x007fc79faeb640> But the return is some object instead of the actual id integer value. Any Ideas? It uses no first or limit just select('id'), with first I always get the weird stuff where it doesn't select on the id in the stmt at all.

|

janki · Accepted Answer · 2014-09-06 23:22:12Z

0

You're making a query:

Node.where("nodeid = ?", nstart).select('id')

will return an array of objects (or empty array if there's no suitable records) *not array actually. Activerecord::Relation

If you use 'first' like Mohammad AbuShady told:

Node.where("nodeid = ?", nstart).select('id').first

you get an object (or nil if there's no record)

If you want an integer id value, you can get it from the object

Node.where("nodeid = ?", nstart).first.id

select('id') tells active record not to retrieve all the fields in the table, just 'id'

answered Sep 6, 2014 at 23:22

janki

1014 bronze badges

Collectives™ on Stack Overflow

Rails sql issue with a simple where statement

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related