X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FFAQ.pod;h=8fcc3133a84b099eaf185b9305573cdd8c7b452f;hb=9c0df5f32b68e23c670c89ce6cdbff60b4bd0ed0;hp=dd27bbf36608c114085d42999681527052a6e1c3;hpb=85f7862264752290ed6e3a3bf020f907a2cdd6fc;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index dd27bbf..8fcc313 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -232,6 +232,25 @@ and not: my $interval = "now() - interval '12 hours'"; ->search({last_attempt => { '<' => \$interval } }) +=item .. search with an SQL function on the left hand side? + +To use an SQL function on the left hand side of a comparison: + + ->search({}, { where => \'YEAR(date_of_birth)=1979' }); + +=begin hidden + +(When the bind arg ordering bug is fixed, the previous example can be +replaced with the following.) + + ->search({}, { where => \'YEAR(date_of_birth)=?', bind => [ 1979 ] }); + +=end hidden + +Or, if you have quoting off: + + ->search({ 'YEAR(date_of_birth' => 1979 }); + =item .. find more help on constructing searches? Behind the scenes, DBIx::Class uses L to help construct @@ -245,10 +264,58 @@ documentation. =over 4 -=item .. fetch as much data as possible in as few select calls as possible? (prefetch) +=item .. fetch as much data as possible in as few select calls as possible? See the prefetch examples in the L. +=item .. fetch a whole column of data instead of a row? + +Call C on a L, this returns a +L, see it's documentation and the +L for details. + +=item .. fetch a formatted column? + +In your table schema class, create a "private" column accessor with: + + __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' }); + +Then, in the same class, implement a subroutine called "my_column" that +fetches the real value and does the formatting you want. + +See the Cookbook for more details. + +=item .. fetch a single (or topmost) row? + +Sometimes you many only want a single record back from a search. A quick +way to get that single row is to first run your search as usual: + + ->search->(undef, { order_by => "id DESC" }) + +Then call L and ask it only to return 1 row: + + ->slice(0,1) + +These two calls can be combined into a single statement: + + ->search->(undef, { order_by => "id DESC" })->slice(0,1) + +Why slice instead of L or L? +If supported by the database, slice will use LIMIT/OFFSET to hint to the database that we +really only need one row. This can result in a significant speed improvement. + +=item .. refresh a row from storage? + +Use L. + + $row->discard_changes + +Discarding changes and refreshing from storage are two sides fo the same coin. When you +want to discard your local changes, just re-fetch the row from storage. When you want +to get a new, fresh copy of the row, just re-fetch the row from storage. +L does just that by re-fetching the row from storage +using the row's primary key. + =back =head2 Inserting and updating data @@ -271,7 +338,7 @@ primary key field from the sequence. To help PK::Auto find your inserted key, you can tell it the name of the sequence in the C supplied with C. - ->add_columns({ id => { sequence => 'mysequence' } }); + ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } }); =item .. insert many rows of data efficiently? @@ -290,6 +357,48 @@ scalar reference: ->update({ somecolumn => \'othercolumn' }) +But note that when using a scalar reference the column in the database +will be updated but when you read the value from the object with e.g. + + ->somecolumn() + +you still get back the scalar reference to the string, B the new +value in the database. To get that you must refresh the row from storage +using C. Or chain your function calls like this: + + ->update->discard_changes + + to update the database and refresh the object in one step. + +=item .. store JSON/YAML in a column and have it deflate/inflate automatically? + +You can use L to accomplish YAML/JSON storage transparently. + +If you want to use JSON, then in your table schema class, do the following: + + use JSON; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { jsonToObj(shift) }, + deflate => sub { objToJson(shift) }, + }); + +For YAML, in your table schema class, do the following: + + use YAML; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { YAML::Load(shift) }, + deflate => sub { YAML::Dump(shift) }, + }); + +This technique is an easy way to store supplemental unstructured data in a table. Be +careful not to overuse this capability, however. If you find yourself depending more +and more on some data within the inflated column, then it may be time to factor that +data out. + =back =head2 Misc @@ -323,4 +432,27 @@ is executed. You can create further resultset refinements by calling search again or relationship accessors. The SQL query is only run when you ask the resultset for an actual row object. +=item How do I deal with tables that lack a primary key? + +If your table lacks a primary key, DBIx::Class can't work out which row +it should operate on, for example to delete or update. However, a +UNIQUE constraint on one or more columns allows DBIx::Class to uniquely +identify the row, so you can tell L these +columns act as a primary key, even if they don't from the database's +point of view: + + $resultset->set_primary_key(@column); + +=back + +=head2 Notes for CDBI users + +=over 4 + +=item Is there a way to make an object auto-stringify itself as a +particular column or group of columns (a-la cdbi Stringfy column +group, or stringify_self method) ? + +See L + =back