Name

QBit::QueryData - Query constructor for the data.

GitHub

https://github.com/QBitFramework/QBit-QueryData

Install

• cpanm QBit::QueryData

• apt-get install libqbit-querydata-perl (http://perlhub.ru/)

Methods

• new - created object. Params:

  • data - data.

  • fields - default fields (optional, defualt all fields)

  • filter - default filter (optional, default all data)

  • definition - fields definition (optional, default 'string')

  Example:

  my $q = QBit::QueryData->new(
      data => [
          {
              id      => 1,
              caption => 'c1',
              data    => {
                  k1 => 1.1,
                  k2 => 'd1_2'
              },
              array => [1.1, 'a1_2'],
          },
          {
              id      => 2,
              caption => 'c2',
              data    => {
                  k1 => 2.1,
                  k2 => 'd2_2'
              },
              array => [2.1, 'a2_2'],
          },
      ],
      fields => [qw(id caption)],
      filter => ['OR', [
          {id => 1},
          ['caption'   => '=' => \'c2'],
          ['data.k1'   => '>', \2],
          ['array.[1]' => 'LIKE' => \'a1']
      ]],
      definition => {
          'id'        => {type => 'number'},
          'caption'   => {type => 'string'},
          'data.k1'   => {type => 'number'},
          'data.k2'   => {type => 'string'},
          'array.[0]' => {type => 'number'},
          'array.[1]' => {type => 'string'},
      },
  );

• fields - set fields for request

  Example:

  # set fields
  $q->fields([qw(caption)]);
  $q->fields({
      caption => '',
      key     => 'id',                 # create alias 'key' for field 'id'
      k1      => 'data.k1',            # alias on complex field
      sum_k1  => {SUM => ['data.k1']}, # Function SUM
  });
  
  # use default fields
  $q->fields([]);
  $q->fields({});
  
  # all fields
  $q->fields();

  Functions:

  All functions like in MySQL (except FIELD)

  • FIELD - This is not a function, but it also inherits from Function.

  • CONCAT

  • COUNT

  • MAX

  • MIN

  • SUM

  • DISTINCT

  Namespace for functions:

  QBit::QueryData::Function::YOUFUNC # you can create self functions (name in uppercase)

• get_fields - get fields

  Example:

  my $fields = $q->get_fields(); # {'caption' => '', 'key' => 'id'}

• filter - set filter for request

  Types:

  • number: "=" "<>" "!=" ">" ">=" "<" "<=" "IN" "NOT IN" "IS" "IS NOT"

  • string: "=" "<>" "!=" ">" ">=" "<" "<=" "IN" "NOT IN" "IS" "IS NOT" "LIKE" "NOT LIKE"

  For list: "=" "<>" "!=" "IN" "NOT IN"

  Example:

  $q->filter({id => 1, caption => 'c1'}); # or ['AND', [['id' => '=' => \1], ['caption' => '=' => \'c1']]]
  
  $q->filter(['caption' => 'LIKE' => \'c']);
  
  $q->filter(['data.k1' => '<' => \2]);
  
  $q->filter(['array.[1]' => '=' => \'a2_2']);
  
  $q->filter(); # all data

• definition - set fields definition

  Example:

  $q->definition({
      'id'        => {type => 'number'},
      'caption'   => {type => 'string'},
      'data.k1'   => {type => 'number'},
      'data.k2'   => {type => 'string'},
      'array.[0]' => {type => 'number'},
      'array.[1]' => {type => 'string'},
  });

• group_by - grouping by fields

  Example:

  $q->group_by(qw(caption data.k1 array.[1]));

• order_by - set order sorting

  Example:

  # Ascending
  $q->order_by(qw(id caption data.k1 array.[1])); # or (['id', 0], ['caption', 0], ['data.k1', 0], ['array.[1]', 0])
  
  # Descending
  $q->order_by(['id', 1]);

• limit - set offset and limit

  Example:

  $q->limit($offset, $limit);
  
  $q->limit(); # all data

• found_rows - data count

  Example:

  my $rows = $q->found_rows(); # 2

• distinct - set/reset only unique elements

  Example:

  #set
  $q->distinct(1); # or $q->distinct();
  
  #reset
  $q->distinct(0);

• insensitive - set/reset insensitive mode for LIKE

  Example:

  #set
  $q->insensitive(1); # or $q->insensitive();
  
  #reset
  $q->insensitive(0);

• get_all - get data by settings

  Example:

  my $data = $q->get_all();
  
  $data = $q->fields([qw(id)])->filter(['caption' => 'LIKE' => \'c'])->group_by('id')->order_by(['id', 1])->get_all();

• all_langs - support interface DB::Query

• calc_rows - support interface DB::Query

• for_update - support interface DB::Query