| DBIx::Class::Manual::FAQ - Frequently Asked Questions |
DBIx::Class::Manual::FAQ - Frequently Asked Questions (in theory)
This document is intended as an anti-map of the documentation. If you know what you want to do, but not how to do it in the DBIx::Class manpage, then look here. It does not contain much code or examples, it just gives explanations and pointers to the correct pieces of documentation to read.
How Do I:
Next, spend some time defining which data you need to store, and how it relates to the other data you have. For some help on normalisation, go to http://b62.tripod.com/doc/dbbase.htm or http://209.197.234.36/db/simple.html.
Now, decide whether you want to have the database itself be the definitive source of information about the data layout, or your DBIx::Class schema. If it's the former, look up the documentation for your database, eg. http://sqlite.org/lang_createtable.html, on how to create tables, and start creating them. For a nice universal interface to your database, you can try the DBI::Shell manpage. If you decided on the latter choice, read the FAQ on setting up your classes manually, and the one on creating tables from your schema.
has_many relationship on the one side, and a
belongs_to relationship on the many side. Currently these need to
be set up individually on each side. See the DBIx::Class::Relationship manpage
for details.
belongs_to relationship for the field containing the
foreign key. See belongs_to in the DBIx::Class::Relationship manpage.
belongs_to relationship, as above. If the column is
NULL then the inflation to the foreign object will not happen. This
has a side effect of not always fetching all the relevant data, if you
use a nullable foreign-key relationship in a JOIN, then you probably
want to set the join_type to left.
has_many relationships. If your database already does this (and
that is probably better), turn it off by supplying cascade_delete => 0
in the relationship attributes. See the DBIx::Class::Relationship::Base manpage.
$schema object, as mentioned above in ``.. connect to my
database''. Find the ResultSet
that you want to search in, and call search on it. See
search in the DBIx::Class::ResultSet manpage.
->search({'mydatefield' => 'now()'})
to search, will probably not do what you expect. It will quote the text ``now()'', instead of trying to call the function. To provide literal, unquoted text you need to pass in a scalar reference, like so:
->search({'mydatefield' => \'now()'})
order_by
attribute. See order_by in the DBIx::Class::ResultSet manpage.
as?order_by, as you did to select.
To get ``fieldname AS alias'' in your SQL, you'll need to supply a
literal chunk of SQL in your select attribute, such as:
->search({}, { select => [ \'now() AS currenttime'] })
Then you can use the alias in your order_by attribute.
group_by
attribute, see group_by in the DBIx::Class::ResultSet manpage.
as?group_by, as you did to select.
To get ``fieldname AS alias'' in your SQL, you'll need to supply a
literal chunk of SQL in your select attribute, such as:
->search({}, { select => [ \'now() AS currenttime'] })
Then you can use the alias in your group_by attribute.
search is a hashref of accessor names and
values to filter them by, for example:
->search({'created_time' => { '>=', '2006-06-01 00:00:00' } })
Note that to use a function here you need to make the whole value into a scalar reference:
->search({'created_time' => \'>= yesterday()' })
join attribute in
your search, for example when searching in the Books table for all the
books by the author ``Fred Bloggs'':
->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' })
The type of join created in your SQL depends on the type of relationship between the two tables, see the DBIx::Class::Relationship manpage for the join used by each relationship.
view in your
database, and using that as your source. A view is a stored SQL
query, which can be accessed similarly to a table, see your database
documentation for details.
my $interval = "< now() - interval '12 hours'";
->search({last_attempt => \$interval})
and not:
my $interval = "now() - interval '12 hours'";
->search({last_attempt => { '<' => \$interval } })
get_column on a the DBIx::Class::ResultSet manpage, this returns a
the DBIx::Class::ResultSetColumn manpage, see it's documentation and the
Cookbook for details.
__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.
->search->(undef, { order_by => "id DESC" })
Then call slice in the DBIx::Class::ResultSet manpage 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 first in the DBIx::Class::ResultSet manpage or single in the DBIx::Class::ResultSet manpage? 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.
column_info supplied with add_columns.
->add_columns({ id => { sequence => 'mysequence' } });
->update({ somecolumn => \'othercolumn' })
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.
Starting with version 0.07, you can use search_rs in the DBIx::Class::ResultSet manpage to work around this issue.
search in scalar context, no query
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.
| DBIx::Class::Manual::FAQ - Frequently Asked Questions |