In my app, I want to create a new record if it doesn’t exist, and update
some fields if it does.
Looking at the debug log, and it appears that I’m doing something that’s
causing an extra SELECT. The basic synopsis:
sta = Station.find_by_station_id(station_id)
if sta.nil?
Station.create(params)
else
sta.p1 = “white port”
sta.p2 = “lemon juice”
sta.save!
end
The Station.create() isn’t a problem. But if it takes the ‘else’
clause, the SQL trace looks suspicious. (Hand edited for clarity…):
Station Load: SELECT * FROM stations WHERE (stations.station_id =
‘WPLJ’) LIMIT 1
SQL BEGIN
Station Load: SELECT stations.id FROM stations WHERE
(stations.station_id = BINARY ‘WPLJ’ AND stations.id <> 12095)
LIMIT 1
Station Update: UPDATE stations SET p1 = “white port”, p2 = “lemon
juice” WHERE id = 12095
COMMIT
It seems like just the UPDATE would suffice, so what is that second
SELECT doing? And what can I do to make it go away? 
TIA.
On Apr 14, 2010, at 3:30 PM, Fearless F. wrote:
Station.create(params)
(stations.station_id =
‘WPLJ’) LIMIT 1
SQL BEGIN
Station Load: SELECT stations.id FROM stations WHERE
(stations.station_id = BINARY ‘WPLJ’ AND stations.id <> 12095)
LIMIT 1
Any chance you have a validates_uniqueness_of :station_id in your
model? If so, the above select is checking for that.
Philip H. wrote:
Any chance you have a validates_uniqueness_of :station_id in your
model? If so, the above select is checking for that.
Give that man a cigar! Yep, that’s exactly what’s going on – thanks!
I believe it’s safe to remove the uniqueness constraint since my code is
checking for existence of a station before creating a new one anyway.
If anyone wants to warn me of any gotcha’s about doing this, now would
be a good time to tell me.
On Apr 14, 2010, at 3:54 PM, Fearless F. wrote:
be a good time to tell me.
Are you going to check for that condition every time you create that
model? In scripts, via the console, somewhere else in the code?
If you remove that constraint you’ll need to remember this every
time… and at some point you’ll forget. I always do 
I’d leave it alone if it were me.
-p
On Apr 14, 2010, at 4:06 PM, Fearless F. wrote:
-p
Point well taken. Right now, that’s the only code that modifies the
Station table, and I’m doing a massive (initial) load and update –
removing the constraint really does speed things up. But heeding your
advice, I’ll reinstate the constraint after this initial load
finishes.
(What? Of COURSE I’ll remember! 
You could also do my_model_instance.save(false) which would bypass
the validations entirely. As long as you’re sure your initial data
load is legit.
Philip H. wrote:
Are you going to check for that condition every time you create that
model? In scripts, via the console, somewhere else in the code?
If you remove that constraint you’ll need to remember this every
time… and at some point you’ll forget. I always do
I’d leave it alone if it were me.
-p
Point well taken. Right now, that’s the only code that modifies the
Station table, and I’m doing a massive (initial) load and update –
removing the constraint really does speed things up. But heeding your
advice, I’ll reinstate the constraint after this initial load finishes.
(What? Of COURSE I’ll remember!
Thanks.
-ff
