2

I'm using activerecord-postgis-adapter and squeel in a geo oriented application. Fetching data using various queries based on find and where is working fine and squeel allows me to use PostgreSQL/PostGIS functions to query based on spatial functions and datatypes.

However, I can't figure out how to access similar functions when performing INSERTs and UPDATEs. Say I want to do something in the line of:

UPDATE object SET raster = ST_AsRaster(ST_Buffer(ST_Point(1,5),10),150, 150, '2BUI') 
where id = 12345;

How could I perform such a query? Squeel doesn't even seem to have support for other queries than SELECTs.

In a perfect world I would even want to have Rgeo's datatypes automatically converted when used in UPDATE queries, just like with where queries.

I am aware that I can fall back to

ActiveRecord::Base.connection.execute

, but would rather avoid that unless someone tells me it's the only way.

-ra

Kevin Kunderman
  • 2,036
  • 1
  • 19
  • 30
averas
  • 545
  • 1
  • 6
  • 15

2 Answers2

1

you could create a connection to the database per model you're working with. one method you can run is the select_value method, there are others:

sql = "UPDATE object SET raster = ST_AsRaster(ST_Buffer(ST_Point(1,5),10),150, 150, '2BUI') where id = 12345;"
conn = ModelName.connection
conn.select_value(sql)

should run your command

see this post by Daniel Azuma for more about this one: http://blog.daniel-azuma.com/archives/216

you could conceivably put this type of code in migrations. In fact its probably a best practice.

boulder_ruby
  • 38,457
  • 9
  • 79
  • 100
0

update_all accepts an array param, so that you could e.g. do:

SomeClass
  .where(id: 123)
  .limit(1)
  .update_all(
    ["some_field = some_pg_func(?, ?)", "some", "values"]
  )

https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Relation.html#method-i-update_all

Jasper
  • 1,971
  • 19
  • 34