[Dbix-class] how to add a method that performs a database lookup?

[Toby Corkindale]

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