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