Forum

Search both Item Name and Item SKU to find item in Invoice

Posted in CategoryIdeas
  • Van Stokes    ( User ) 1 year ago

    Need to search both Item Name and Item SKU when entering items into an Invocie. Also, need an option to display the SKU number. This is pretty important in most inventory based operations.


    For speed:


    1. Index the name

    2. Index the SKU

    3. Use a UNION to avoid a full table scan



     


    DDL:

    ALTER TABLE `rqu_items`
    ADD  INDEX `rqu_items_name` (`company_id`, `name`),
    ADD  UNIQUE INDEX `rqu_items_sku_unique` (`company_id`, `sku`);

    Example DML:

    SELECT
      rqu_items.*
    FROM rqu_items
    WHERE rqu_items.company_id = {companyid}
    AND rqu_items.name LIKE '{BeginsWith%}'
    AND rqu_items.deleted_at IS NULL
    UNION
    SELECT
      rqu_items.*
    FROM rqu_items
    WHERE rqu_items.company_id = {companyid}
    AND rqu_items.sku LIKE '{BeginsWith%}'
    AND rqu_items.deleted_at IS NULL


     

  • Denis Duliçi    ( Admin ) 1 year ago

    Hello Van,


    Your contribution via GitHub is appreciated https://github.com/akaunting/akaunting


    Regards

Please login or register to leave a response.