Adam Witney wrote:
I am new to DBIx::Class, but have so far been impressed with how easy
it is to use, so thanks to those that have developed and continue to
develop it.
I wanted to know, is there a way of adding a method to a class that
performs a separate database lookup. I have used the Cookbook example
for "Arbitrary SQL through a custom ResultSource", but this returns a
query that is plugged into a sub-select. Can i access the database
connection handle at all (eg $dbh) to build my own SQL query?
thanks for any help
adam
You can, but before I tell you how I'm going to ask what kind of query
you want. Give us SQL and we'll tell you if you can do it without
going via the raw DBH.
ok i have several places i think I might need this!
I have two tables containing data that originated from files; one table
contains the filename, format etc and the other table contains the data
itself. The data table contains about 100 million rows now and the data
needs only be retrieved in chunks (about 5k-10k rows) corresponding to
the whole file (one row of first table), although the required columns
may very between queries. I was thinking that creating a class and hence
an object per row would make this slower, so i thought i could write a
method that pulls back the whole data in one go for the main class/object.
Hmm, just out of interest, did you consider using the pg blob format for
the file data instead? (Which lets you work on it using read/write/seek
semantics)
Or has that fallen out of fashion these days?
If you're working with frames of a movie, or rows of a CSV file, or
other such record-based data then I totally understand you would want to
store them all in individual rows instead though.
If I understand your query, you want to do something like:
SELECT filedata.foo, filedata.bar
FROM filedata JOIN metadata ON filedata.metadata = metadata.id
WHERE metadata.filename = 'myFile.bin'
However you expect that to return 7k5 (+/- 2k5) rows and you'd like to
retrieve it in chunks?
You could do it with pages, like this?
while(
my @items = $schema->resultset('Filedata')->search(
{
'metadata.filename' => $filename,
},
{
join => 'metadata',
rows => 1000,
page => $page++
}
)
) {
do_stuff_with(@items)
}
But you're correct that it would be creating an object for every row.
However DBIC should be pulling down all 1000 of them from the DB in a
single query.
The second place i might need this is that i have some data in a second
database and so i would have to interact with this using dblink (I am
using PostgreSQL). This seems easier to just write the query in a method?
Doesn't Pg just represent the foreign DBI-Linked database as another
schema? So I think you could just write the extra classes into dbic and
it should query them, without realising their foreign?
.. I haven't tried it myself though.
-Toby
--
Strategic Data Pty Ltd
Ph: 03 9340 9000
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
