Raw SQL
emmapersky
been working on a new app and I need to run a fairly complicated sql
query (fun) to return some data. The data does not conform directly to
any of the classes I have, and so using Class.find_by_sql doesn't make
any sense.
I've header rumour of a query method in datamapper, but can't find
anything about it, and a grep of the dm-core doesn't reveal any such
method.
Was wondering if anyone knows of such a method. Effectively I just
want an array of hashes containing the results, or something
equivalent....
any ideas?
Emma
Bernerd Schaefer
> anything about it, and a grep of the dm-core doesn't reveal any such
> method.
You can do:
results = repository(:default).adapter.query("SELECT...")
You'll get back an array of open structs representing the returned data.
Emma Persky
methods in DataMapper so you could do DataMapper.query('SELECT.....")
for default repository?
Emma
Oh, and thanks to whoever updated the wiki :) you saved me 5 minutes!
Bernerd Schaefer
> methods in DataMapper so you could do DataMapper.query('SELECT.....")
> for default repository?
Well, you don't technically need to specify :default. You can just do
repository.adapter.query().
The reason the method is on the adapter, and not directly on
repository, is because it is a SQL specific action. It makes sense for
a SQL adapter to accept a string to query, but that is not necessarily
the case for other kinds of databases.
It would be easy enough, of course, for you to add that yourself (or
turn it into a plugin).
module DataMapper
def query(sql)
repository.adapter.query(sql)
end
end
Dan Kubb (dkubb)
> ouch, that's seriously unpleasant syntax.should these be proxied by
> methods in DataMapper so you could do DataMapper.query('SELECT.....")
> for default repository?
SQL usage from within DM.
DM is an ORM designed to fetch and store objects in a data store
through a simple CRUD layer. It won't handle reporting very well,
since there's no way to simply construct a non-trivial query for
aggregate reporting in a way that will work with more than a handful
of storage backends. Even describing non-trivial aggregate report
queries between RDBMS' is a bit painful, but it gets insane when you
throw in CouchDB or web APIs.
I believe you should use the best tool for the job, and not every tool
can be used for every job. In an attempt to make DM handle reporting
for every storage backend I believe it would become too complex to
satisfy it's primary goal of providing a simple CRUD layer on top of
data stores.
If you need a nice clean API to an RDBMS for reporting purposes, and
you don't want to use straight SQL, I would suggest looking at
Sequel. It's closer to SQL syntax and it should be possible to
describe many queries with it. I've wondered if it is possible to
create a layer between DM and Sequel so that you could still describe
the models in DM, and then indicate you want to use Sequel to
construct a query. I haven't looked into it though so anyone
interested in DM and Sequel: please do investigate.
With all that said, we will likely make this a little less ugly in the
future. Something like this will probably be supported:
results = repository.query('SELECT...')
Dan
(dkubb)
Emma Persky
>
> Actually, this is ugly on purpose because we want to discourage direct
> SQL usage from within DM.
Heh. I actually quite like that idea of making a certain call ugly to
discourage it's use!
> DM is an ORM designed to fetch and store objects in a data store
> through a simple CRUD layer. It won't handle reporting very well,
> since there's no way to simply construct a non-trivial query for
> aggregate reporting in a way that will work with more than a handful
> of storage backends. Even describing non-trivial aggregate report
> queries between RDBMS' is a bit painful, but it gets insane when you
> throw in CouchDB or web APIs.
<snip other relevant stuff>
This is, however, an intractable problem, since for anything more than
trivial problems there is no "perfect" solution, simply best tools,
and this means making sacrifices.
In my current project the vast majority of DB access is blindingly
simple, the sort of problem that scaffolding can actually solve (at
least for the moment). I do, however, have a few queries that need in
DB reporting - I need to iterate over a set of objects, joining on to
a table and retrieving a count for joined objects for each row in the
first set. (actually, this is a simplification)
For now I have actually implemented this in an n+1 query style, with a
ruby loop. Unpleasant and unscalable, but works for me right now.
I like the idea of DM interfacing to Sequel. Pure Sequel is overkill
for this project, but I might look into this further...
Thanks
Emma
Angel
database (if the underlying DB supports views ;) ) and making a class
out of that. Think of it as a presenter pattern.
If it is a rather large and expensive operation, as your post
suggests, you may want to think about a materialized view with refresh
triggers when the underlying data changes.
-angel
On Dec 30, 6:58 am, "Emma Persky" <emma.per...@gmail.com> wrote:
Dan Kubb (dkubb)
> In my current project the vast majority of DB access is blindingly
> simple, the sort of problem that scaffolding can actually solve (at
> least for the moment). I do, however, have a few queries that need in
> DB reporting - I need to iterate over a set of objects, joining on to
> a table and retrieving a count for joined objects for each row in the
> first set. (actually, this is a simplification)
>
> For now I have actually implemented this in an n+1 query style, with a
> ruby loop. Unpleasant and unscalable, but works for me right now.
(), avg() functions scoped to a Model or a Collection. It can even
perform multiple aggregate functions in a single call with the
aggregate() method. It only works with RDBMS' right now, but I would
love to see someone add a layer that does it via "brute force" -- not
all storage engines have the concept of aggregate functions, so there
would be the need to iterate over every row and aggregate the results
that way.
Dan
(dkubb)