Encapsulating websql (VII) encapsulating paging and query

Time:2022-5-7

Although there are many ways of paging now, unlike before, you can only turn by page, but the basic principle is the same.

The paging of websql is relatively simple (regardless of performance), because limit can be used.

SQL

select * from table where xxx order by xxxx limit 0,10

encapsulation

/**
 *Paging to obtain data, you can query
 *@ param {mysqlhelp} help access instance of database
 *@ param {object} meta table, field
 *@ param {object} query query criteria
 *@ param {object} pager data
 *@ returns add record ID
 ** meta structure:
 ** * tablename: '', table name
 ** * cols: {colname: 'type'}, fields to be displayed
 ** query structure (query criteria):
 ** * {colname: [401, 11]} field name, query method, query keyword
 ** pager structure:
 ** * PageSize: 20 // how many records are displayed on one page
 ** * orderby: {ID: false} // sort field: field name, false indicates reverse order.
 *Total // the total number of records matching the query criteria is 100 *
 ** * PageIndex: 1 // displays the page number of records, starting from 1
 */
export default function getPager (help, meta, query, pager) {
  console. Log ('Start paging: ')
  const myPromise = new Promise((resolve, reject) => {
    //Query criteria and query parameters
    const { whereQuery, whereValue } = help._getWhereQuery(query)
    //Set sorting and paging
    const { orderBy, limit } = help._getPager(pager)

    //Set displayed fields
    const showCol = Object.keys(meta.cols)
    if (showCol.length === 0) { showCol.push('*') }

    //Splicing query statements
    const sql = `SELECT ${showCol.join(',')} FROM ${meta.tableName} ${whereQuery} ${orderBy} ${limit}`
    console.log('select-sql:', sql, whereValue)

    help.query(sql, whereValue)
      .then((res) => {
        //Added successfully
        resolve(res.rows)
      })
      .catch((err) => {
        //Something went wrong
        console. Log ('paging failed to get records: ', ERR)
        reject(err)
      })
  })
  return myPromise
}

The query method was introduced in the previous article. Here is the paging processing

paging

/**
   *Internal function to set paging information and sorting fields according to paging information
   *@ param {object} pager paging and sorting
   *@ returns order by and limit
   */
  _getPager (pager) {
    let _ Limit = '' // paging, which can not be set
    let _ Order = '' // sorting, optional
    //Set paging order by and limit
    if (typeof pager !== 'undefined') {
      if (typeof pager.pagerIndex !== 'undefined') {
        //Paging with limit
        const _pageSize = pager.pagerSize || 10
        const index = _pageSize * (pager.pagerIndex - 1)
        _limit = ` limit ${index}, ${_pageSize} `
      }
      if (typeof pager.orderBy === 'object') {
        //Set sorting fields and methods
        const arr = []
        for (const key in pager.orderBy) {
          const col = key
          const isAsc = pager.orderBy[key] ? ' asc ' : ' desc '
          arr.push(col + ' ' + isAsc)
        }
        _order = ' order by ' + arr.join(',')
      }
    }

    return {
      orderBy: _order,
      limit: _limit
    }
  }
  • order by
    Sorting fields is relatively simple. You can set multiple fields. The field name is key. True indicates ascending order and false indicates descending order.
    In this way, multiple fields can be put together.

  • limit
    It needs to be calculated according to the number of records on one page and the number of pages,_pageSize * (pager.pagerIndex - 1)This is the position, and then take_ PageSize records.

This simple paging can be done.

When is the total number of records counted

General paging components need to set a total number of records, which is convenient to control the number of pages and avoid turning them out.

Count is required to count the total records. If the amount of data is small, the consumption of this statistics can be ignored, but if the amount of data is very large, the resources occupied by count can not be ignored.

So what? Can we think about it? Do we really need to count the total records every time we turn the page?

If you don’t think so, let’s analyze it

[ ✔ ] After query
[ ✔ ] After adding
[ ✔ ] After deletion
[✘] page number page turning

If the query criteria change, the number of records will change; The number of records will also change after deletion; The total number of records will also change after adding, so you need to count. Although the rest may change, we can ignore it and think it will not change, so we don’t count.

This saves a lot of count operations.

Implementation mode

//Introduce help
import { getCount, getPager } from '../../../packages/websql.js'

/**
 *Implement record acquisition service
 *@ param {object} userinfo information of the current login
 *@ param {object} help access instance of database
 *Meta of @ param {objec} info service
 *@ param {object} pagerinfo paging and query information
 *@ returns returns the ID of the newly added record
 */
export default function getPager (userInfo, help, info, pagerInfo) {
  return new Promise((resolve, reject) => {
    console. Log ('\ n start getpager service \ n') //, info
    const query = pagerInfo.query
    const pager = info.pager
    pager.pagerIndex = pagerInfo.pager.pagerIndex
  
    //Return object
    const re = {
      list: [],
      pager: info.pager
    }

    if (pagerInfo.useCount) {
      //Total records to be counted
      getCount(help, info, query).then((count) => {
        //Set total records
        re.pager.pagerTotal = count
        //Get paging record set
        getPager(help, info, query, pager).then((list) => {
          re.list = list
          resolve(re)
        }).catch((err) => {
          Reject (ERR) // 'error getting paging data!'
        })
      }).catch((err) => {
        Reject (ERR) // 'error getting total records!'
      })
    } else {
      //Get recordset directly
      getPager(help, info, query, pager).then((list) => {
        re.list = list
        resolve(re)
      }).catch((err) => {
        Reject (ERR) // 'error getting paging data!'
      })
    }
  })
}

Await is not used here. Although there is a little more code, it is OK.

Recommended Today

5. Comprehensive case of RDD operation

1、 Word frequency statistics 1. Prepare documents 1. Download novels or long press releases 2. Upload to HDFS                    2. Read file and create RDD            3. Participle            4. Exclude case lower(), map()            Punctuation mark re split(pattern,str),flatMap(),        […]