DBIx::Custom

Creating a model

I will explain how to use "model" with DBIx::Custom. When you create a model, you can automatically acquire and utilize the field information of the table, and you can define a join clause.

Create model --create_model

Use the create_model method to create a model.

#Create a model by specifying the table name
$dbi->create_model('book');

#Create a model with a table name and join clause
$dbi->create_model(
  table =>'book',
  join => [
    'left join company on book.company_id = company.id',
    'left join author on book.author_id = author.id',
  ],,
);

In the argument of create_model, you can specify the table name with table and the join clause with join . You can also specify multiple join clauses for join.

In this sample, join joins the company table and the author table.

Get the model

The created model can be obtained by specifying the table name with the model method.

my $book = $dbi->model('book');

When you create a model, the method insert, update of DBIx::Custom::Model You can call a>, Delete, select and so on.

#insert
$dbi->model('book')->insert({id => 1, title =>'Perl'});

#uppdate
$dbi->model('book')->update({title =>'Perl'}, where => {id => 1});

#uppdate_all
$dbi->model('book')->update_all({title =>'Perl'});

#delete
$dbi->model('book')->delete(where => {id => 1});

#delete_all
$dbi->model('book')->delete_all;

#select
my $rows = $dbi->model('book')->select(where => {id => 1})->all;

Combination of join clause and field name acquisition

You can get the field name automatically by using the model. For example, join the company table with a book table to get information.

For example, consider the following case: In the following example, the SQL is executed to get all the fields in the book table. The parameter is replaced with a placeholder.

# Get all columns
#select * from book where id =?
my $rows = $dbi->model('book')->select(where => {id => 1})->all;

Now, if you want to get an additional company name from here. In such a case, it is easy to write by defining a model. Get all the columns in the book and add the company name.

# Get all columns
#select book.id as `id`, book.name as` name`, book.company_id as `company_id`, company.name as` company.name`
# from book left join company on book.company_id = company.id
# where id =?
my $rows = $dbi->model('book')->select(
  [
    {__MY__ =>'*'},
    {company => ['name']}
  ],,
  where => {id => 1}
)->all;

The table name "__MY__" means your own table name and will get the name without the prefix of the table name. You can get all fields with "*".

The required join clause is automatically selected. The company table is joined, the author table is not.

If you look at the SQL that is actually executed, it will be easier to understand.

You can also specify the field name with the table prefix book.

# Get all columns
#select book.id as `book.id`, book.name as` book.name`, company.name as `company.name`
# from book left join company on book.company_id = company.id
# where id =?
my $rows = $dbi->model('book')->select(
  [
    {book => ['id','name']},
    {company => ['name']}
  ],,
  where => {id => 1}
)->all;

Once you've created a model, you can join the tables and get the fields in a short description.