Name
QBit::Application::Model::DBManager - Class for smart working with DB.
GitHub
https://github.com/QBitFramework/QBit-Application-Model-DBManager
Install
cpanm QBit::Application::Model::DBManager
apt-get install libqbit-application-model-dbmanager-perl (http://perlhub.ru/)
For more information. please, see code.
Package methods
remove_model_fields
Removes the model fields. Use this method if you want to set an entirely different set of fields for the model.
No arguments
Return value: $model_fields (type: ref of a hash)
Example:
my $model_fields = $app->users->remove_model_fields();
# set new fields.
$app->users->model_fields(...);model_fields
Set model fields. Save into package stash with key __MODEL_FIELDS__
Arguments:
%fields - Fields (type: hash)
Example:
package Sellers;
use base qw(QBit::Application::Model::DBManager);
__PACKAGE__->model_accessors(
db => 'Application::Model::DB', # your DB model, see QBit::Application::Model::DB
items => 'Application::Model::Items', # your model (base from QBit::Application::Model::DBManager)
);
__PACKAGE__->model_fields(
id => {
pk => TRUE, # primary key for this model
db => 'sellers', # this field is from the table
default => TRUE, # this field returns if fields were not requested
},
caption => {
db => 'sellers', # this field is from the table
default => TRUE, # this field returns if fields were not requested
i18n => TRUE, # this field depends on current locale, (in DB this field i18n too)
check_rights => 'sellers_view_field__caption',
# your right for "caption", see check_rights from QBit::Application
# Try not to use this key.
},
id_with_caption => {
depends_on => [qw(id caption)], # this field depends on "id" and "caption"
get => sub {
my $fields = shift; # object QBit::Application::Model::DBManager::_Utils::Fields
# access to model: $fields->model
my $row = shift; # hash from db: {id => 1, caption => 'Happy Milkman'}
return $row->{'id'} . ': ' . $row->{'caption'};
}
},
id_with_caption_db => {
db => 'sellers',
db_expr => {CONCAT => ['id', \': ', 'caption']}, # see QBit::Application::Model::DB::Query
},
name => {
# relation "one to one". Use it if you want use join
db => 'users', # this field is from the table, but the tables are different
},
items => {
# relation "one to one", "one to many" or "many to many"
depends_on => [qw(id)],
get => sub {
my $fields = shift; # object QBit::Application::Model::DBManager::_Utils::Fields
my $row = shift; # hash from db: {id => 1}
# $fields->{'__ITEMS__'} created in pre_process_fields
return $fields->{'__ITEMS__'}{$row->{'id'}} // [];
}
}
);
# returns query (class: QBit::Application::Model::DB::Query)
sub query {
my ($self, %opts) = @_;
my $filter = $self->db->filter($opts{'filter'});
unless ($self->check_rights('sellers_view_all')) {
my $cur_user = $self->cur_user();
$filter->and({user_id => $cur_user->{'id'}};
}
my $query = $self->db->query->select(
table => $self->db->sellers,
fields => $opts{'fields'}->get_db_fields('sellers'), # returns db expression for fields with "db" = 'sellers'
filter => $filter
);
my $users_fields = $opts{'fields'}->get_db_fields('users');
# join users only if needed (field "name" was requested)
$query->join(
table => $self->db->users,
fields => $users_fields,
) if %$users_fields;
return $query;
}
# used for dictionaries
sub pre_process_fields {
my $self = shift; # model
my $fields = shift; # object QBit::Application::Model::DBManager::_Utils::Fields
my $result = shift; # data from db
if ($fields->need('items')) {
# gets items only if needed (field "items" was requested)
my $items = $self->items->get_all(
fields => [qw(id seller_id caption)],
filter => {seller_id => [map {$_->{'id'}} @$result]}, # key "id" exists because fields "items" depends on "id"
);
# create dictionaries {<SELLER_ID> => <ITEM>}
$fields->{'__ITEMS__'} = {map {$_->{'seller_id'} => $_} @$items};
}
}
TRUE;
# in your code
my $sellers = $app->sellers->get_all(fields => [qw(id id_with_caption_db name items)]);
#$sellers = [
# {
# id => 1,
# id_with_caption_db => '1: Happy Milkman',
# name => 'Petr Ivanovich',
# items => [
# {
# id => 1,
# seller_id => 1,
# caption => 'milk'
# },
# {
# id => 2,
# seller_id => 1,
# caption => 'cheese'
# },
# ],
# }
#]model_filter
Set model filters. Save into package stash with key __DB_FILTER__
Types: namespace (QBit::Application::Model::DBManager::Filter)
boolean
dictionary
multistate
number
subfilter
text
Arguments:
%opts - Options (type: hash)
db_accessor - name db accessor
fields - filter fields
Example:
__PACKAGE__->model_filter(
db_accessor => 'db', # your db accessor
fields => {
id => {type => 'number'},
caption => {type => 'text'},
active => {type => 'boolean'},
product => {
type => 'dictionary',
values => sub {
[
{id => 1, label => gettext('Milk')},
{id => 2, label => gettext('Cheese')},
];
},
}
multistate => {type => 'multistate'},
# you can filtered by field from other model
user => {
type => 'subfilter',
model_accessor => 'users', # accessor related model
field => 'user_id', # field from this model
fk_field => 'id', # field from model "users"
},
},
);
# in your code
my $items = $app->model->get_all(
filter => [
'OR',
[
['id', '=', 1],
['caption', 'LIKE', 'Nike'],
['active', '=', 1],
['product', '=', [1, 2]],
['multistate', '=', 'approved and working'],
['user', 'MATCH', ['login', '=', 'ChuckNorris']] # login is a filter in model "users"
]
]
);get_model_fields
Returns a model fields.
No arguments.
Return value: $model_fields (type: ref of a hash)
Example:
my $model_fields = $app->model->get_model_fields(); # getter for method "model_fields"get_all
Returns model items.
Arguments:
%opts - Options (type: hash)
fields
returns "id" and "caption" my $data = $app->model->get_all(fields => [qw(id caption)]); # returns fields with key "default" my $data = $app->model->get_all(); # return all fields my $data = $app->model->get_all(fields => ['*']);filter - see QBit::Application::Model::DB::Query. Unlike filters from the database, model filters can not use field names and scalars are used without reference.
# mysql: name = caption # db: ['name', '=', 'caption'] # model: no way # mysql: id = 12 # db: ['id', '=', \12] # model: ['id', '=', 12] my $data = $app->model->get_all(filter => {id => 1});distinct - unique rows from table
my $data = $app->model->get_all(fields => [qw(caption)], distinct => TRUE);for_update - get lock
# get my $data = $app->model->get_all(fields => [qw(id)], filter => ["caption", "LIKE", "milk"]}, for_update => TRUE); # update $app->db->table->edit($app->db->filter({id => [map {$_->{'id'}} @$data]}), {caption => 'Milk'});order_by - set order
my $data = $app->model->get_all( fields => [qw(caption)], order_by => [ 'caption', # asc [ 'price', # field 1 # order: 0 - asc, 1 - desc ] ] );limit
my $data = $app->model->get_all(limit => 100);offset
my $data = $app->model->get_all(limit => 100, offset => 1000);calc_rows
my $data = $app->model->get_all(limit => 100, calc_rows => TRUE); my $all_data = $app->model->found_rows(); # 1_000_000all_locales
my $data = $app->model->get_all(fields => [qw(id caption)], all_locales => TRUE); #$data = [ # { # id => 1, # caption => { # ru => 'Веселый молочник', # en => 'Happy Milkman', # }, # }, # ... #]
Return value: Data (type: ref of a array)
Example:
my $data = $app->model->get_all(
fields => [qw(id caption)],
filter => ['OR', [
['id', '=', 10],
['caption', '=', 'milk']
]],
limit => 100,
offset => 10_000,
order_by => ['caption']
);found_rows
Returns count of a rows.
No arguments.
Return value: $found_rows (type: scalar or undef)
Example:
my $data = $app->model->get_all(limit => 3, calc_rows => TRUE);
my $found_rows = $app->model->found_rows();last_fields
Returns a last fields was requested.
No arguments.
Return value: $last_fields (type: scalar or undef)
Example:
my $data = $app->model->get_all(fields => [qw(id caption)]);
my $last_fields = $app->model->last_fields();
# $last_fields = {
# id => '',
# caption => '',
# };get
Returns row by primary key.
Arguments:
$pk - primary key (type: scalar or hash)
%opts - options (type: hash; see get_all)
Return value: Row (type: ref of a hash or undef)
Example:
my $item = $app->model->get(1, fields => [qw(id caption)]);
# or
my $item = $app->model->get({id => 1}, fields => [qw(id caption)]);get_pk_fields
Returns primary keys.
No arguments.
Return value: fields (type: ref of a array)
Example:
my $pk = $app->model->get_pk_fields(); # ['id']pre_process_fields
used for dictionaries.
No arguments.
Return value: undef
Example:
# see method: model_fields
$app->model->pre_process_fields();