data tables – How to stop user loading more records on grid

In one legacy UI application on which I am working currently, has a Main List which is used to display all records stored in elastic in paginated way. Elastic may have more than 1 million records.

The problem arises when I added a functionality of loading next page of records when scroll touches bottom (no direct pagination widget).

In the introduced functionality of scroll load, user can scroll infinitely. for each “scroll at the bottom” event, data loads from 0 to pageNo*pageSize records. I set page size as small as 50 records. So, for example,

  • Page 1 will load 50
  • Page 2 – 100 records
  • Page 3 – 150 records
  • Page 4 – 200 records

and so on..

I want to acknowledge user after a certain page number like 20 pages(1000 records loaded on browser) that
Don’t go further otherwise application will become slow, use search with keywords instead.

Actually, I am doing so by putting simple auto hide alert which will start appearing from 20 pages onward and will display in interval of 5 pages like 20, 25,30 etc.

Is there any better way to acknowledge user that he is exploiting functionality because we are allowing him to do so?

I know this type of data loading sounds crazy but this is what we have to do. I need help on letting user know that application will be slow after a certain period of time.

Thanks in advance.

schema – Two records in the same table must be related somehow

I’ve inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two “structures” are combined into one larger “structure”, so there should be a way of linking them.

Should I use an “association table” to link the two IDs, or is there a better way for this sort of thing? Perhaps I’ve answered my own question, but I thought I’d ask if there’s another way…

Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.

Maybe this is a ridiculous open-ended question for this site, but surely I’m not the first person to run into this…

Thanks,
Sean

8 – Setting node ID sequence to a particular higher value for future created records

We have an organic groups based portal in D7 where many blood banks add records. We recently initiated Drupal 8 migration and are close to completion. However, we would want to add selective blood banks first to the new D8 portal, have it tested before we ask all the other blood banks to use the new portal.

Is it possible to set a higher NID value when new records are created by new users? Migration data for remaining customers can happen seamlessly as their NID values will be lower.

Thanks.

postgresql – Postgres group records by consecutive types

I would like to group objects by their consecutive start and end date.

+----------+----------------------------+
|  Fruit   | Time                       |
+----------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 
| Apple    | 2020-09-08 01:00:00.000000 | 
| Orange   | 2020-09-08 02:00:00.000000 | 
| Orange   | 2020-09-08 03:00:00.000000 | 
| Apple    | 2020-09-08 04:00:00.000000 | 
+----------+---------------+------------+

The results should look like this:

+----------+----------------------------+----------------------------+
|  Fruit   | Start Time                 | End Time                   |
+----------+----------------------------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 2020-09-08 01:00:00.000000 |
| Orange   | 2020-09-08 02:00:00.000000 | 2020-09-08 03:00:00.000000 |
| Apple    | 2020-09-08 04:00:00.000000 | 2020-09-08 04:00:00.000000 |
+----------+----------------------------+----------------------------+

postgresql – How to delete all records which are not referenced from other tables

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?

I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.

There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.

Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

python – Parse selected records from empty-line separated file

This is my first post here and I hope I will get some recommendations to improve my code. I have a parser which processes the file with the following structure:

SE|43171|ti|1|text|Distribution of metastases...
SE|43171|ti|1|entity|C0033522
SE|43171|ti|1|relation|C0686619|COEXISTS_WITH|C0279628

SE|43171|ab|2|text|The aim of this study...
SE|43171|ab|2|entity|C2744535
SE|43171|ab|2|relation|C0686619|PROCESS_OF|C0030705

SE|43171|ab|3|text|METHODS Between April 2014...
SE|43171|ab|3|entity|C1964257
SE|43171|ab|3|entity|C0033522
SE|43171|ab|3|relation|C0085198|INFER|C0279628
SE|43171|ab|3|relation|C0279628|PROCESS_OF|C0030705

SE|43171|ab|4|text|Lymph node stations...
SE|43171|ab|4|entity|C1518053
SE|43171|ab|4|entity|C1515946

Records (i.e., blocks) are separated by an empty line. Each line in a block starts with a SE tag; the text tag always occurs in the first line of each block (in the 4th field). The program extracts:

  1. All relation tags in a block, and
  2. Corresponding text (i.e., sentence ID (sent_id) and sentence text (sent_text)) from the first line of the block, if relation tag is present in a block. Please note that the relation tag is not necessarily present in each block.

Below is a mapping dictionary between tags and related fields in a file and a main program.

# Specify mappings to parse lines from input file
mappings = {
        "id": 1,
        "text": {
            "sent_id": 3,
            "sent_text": 5
        },
        "relation": {
            'subject': 5,
            'predicate': 6,
            'object': 7,
        }
    }

Finally a code:

def extraction(file_in):
    """This function extracts lines with 'text' and 'relation'
    tag in the 4th field."""
    extraction = {}
    file = open(file_in, encoding='utf-8')
    bla = {'text': ()}
    for line in file:
        results = {'relations': ()}
        if line.startswith('SE'):
            elements = line.strip().split('|')
            pmid = elements(1)
            
            if elements(4) == 'text':
                tmp = {}
                for key, idx in mappings('text').items():
                    tmp(key) = elements(idx)
                bla('text').append(tmp)
            
            if elements(4) == 'relation':
                tmp = {}
                for key, ind in mappings('relation').items():
                        tmp(key) = elements(ind)
                tmp.update(sent_id = bla('text')(0)('sent_id'))
                tmp.update(sent_text = bla('text')(0)('sent_text'))
                results('relations').append(tmp)
                extraction(pmid) = extraction.get(pmid, ()) + results('relations')
        else:
           bla = {'text': ()}
    file.close()
    return extraction

The output looks like:

import json
print(json.dumps(extraction('test.txt'), indent=4))

{
    "43171": (
        {
            "subject": "C0686619",
            "predicate": "COEXISTS_WITH",
            "object": "C0279628",
            "sent_id": "1",
            "sent_text": "Distribution of lymph node metastases..."
        },
        {
            "subject": "C0686619",
            "predicate": "PROCESS_OF",
            "object": "C0030705",
            "sent_id": "2",
            "sent_text": "The aim of this study..."
        },
        {
            "subject": "C0085198",
            "predicate": "INFER",
            "object": "C0279628",
            "sent_id": "3",
            "sent_text": "METHODS Between April 2014..."
        },
        {
            "subject": "C0279628",
            "predicate": "PROCESS_OF",
            "object": "C0030705",
            "sent_id": "3",
            "sent_text": "METHODS Between April 2014..."
        }
    )
}

Thanks for any recommendation.

postgresql – postgreql 11: how to get result of a query in set of records and then process them in a function

I want to write a function like below.Whats wrong in it?

CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS 
DECLARE 
myRec RECORD;
tab_Rec RECORD(); --This i want to as a table (i dont know the column details though). 
--Declaring it as array fails

BEGIN
SELECT INTO myREC FROM public.myOtherFunc(abc, sss);

--Returns a table i.e. multiple rows and columns
SELECT * INTO tab_Rec FROM public.myAddFunc(abc);

--I want to do this now
for x in 1 .. array_length(tab_Rec, 1)
loop
         ---some logic
end loop;


END;

mysql – How can I get separate records when group_concat is applied

I have this huge query which used to get the details of orders rang and convert the price according to the currency.

SELECT *, 
        YEAR(o.date_added) AS year, 
        QUARTER(o.date_added) AS quarter,       
        MONTHNAME(o.date_added) AS month, 
        MIN(o.date_added) AS date_start, 
        MAX(o.date_added) AS date_end, 
        DAYNAME(o.date_added) AS day_of_week, 
        HOUR(o.date_added) AS hour, 
        (SELECT cgd.name FROM `ecom_customer_group_description` cgd WHERE cgd.customer_group_id = o.customer_group_id AND cgd.language_id = '1') AS customer_group, 
        (SELECT c.value FROM `ecom_currency` c WHERE c.currency_id = o.currency_id) AS currency_rate, 
        COUNT(o.order_id) AS orders, 
        COUNT(DISTINCT CONCAT(o.lastname, ', ', o.firstname)) AS customers, 
        SUM((SELECT SUM(op.quantity) FROM `ecom_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id)) AS products, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'sub_total' GROUP BY ot.order_id)) AS sub_total, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'handling' GROUP BY ot.order_id)) AS handling, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id)) AS low_order_fee, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'shipping' GROUP BY ot.order_id)) AS shipping, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'reward' GROUP BY ot.order_id)) AS reward, 
        SUM((SELECT SUM(1/c.value  (-1 (Abs(ot.value)))) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id)) AS coupon, 
        SUM((SELECT SUM(ROUND(1/c.value * ot.value, 2)) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'tax' GROUP BY ot.order_id)) AS tax,   
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'credit' GROUP BY ot.order_id)) AS credit, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'voucher' GROUP BY ot.order_id)) AS voucher, 
        SUM((SELECT (1/c.value * o.total) FROM `ecom_currency` c WHERE c.currency_id = o.currency_id)) AS total, 
        SUM((SELECT SUM(1/c.value * (op.cost*op.quantity)) FROM `ecom_order_product` op, `ecom_currency` c WHERE op.order_id = o.order_id AND c.currency_id = o.currency_id GROUP BY op.order_id)) AS prod_costs, 
        SUM(o.commission) AS commission, 
        SUM(o.payment_cost) AS payment_cost, 
        SUM(o.shipping_cost) AS shipping_cost, 
        SUM(o.extra_cost) AS extra_cost,  GROUP_CONCAT('',o.order_id,'' ORDER BY o.order_id DESC SEPARATOR '
') AS order_ord_id, 
                    GROUP_CONCAT(o.order_id ORDER BY o.order_id DESC SEPARATOR '
') AS order_ord_idc,        
                    GROUP_CONCAT(DATE_FORMAT(o.date_added, '%d/%m/%Y') ORDER BY o.order_id DESC SEPARATOR '
') AS order_order_date, 
                    GROUP_CONCAT(IFNULL(o.invoice_prefix,'  '),IFNULL(o.invoice_no,'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_inv_no, 
                    GROUP_CONCAT(CONCAT(o.firstname,' ',o.lastname,IF (o.payment_company = '','',CONCAT(' / ',o.payment_company))) ORDER BY o.order_id DESC SEPARATOR '
') AS order_name, 
                    GROUP_CONCAT(o.email ORDER BY o.order_id DESC SEPARATOR '
') AS order_email, 
                    GROUP_CONCAT(IFNULL((SELECT cgd.name FROM `ecom_customer_group_description` cgd WHERE cgd.customer_group_id = o.customer_group_id AND cgd.language_id = '1'),' ') ORDER BY o.order_id SEPARATOR '
') AS order_group,  
                    GROUP_CONCAT(IF (o.shipping_method = '','  ',o.shipping_method) ORDER BY o.order_id DESC SEPARATOR '
') AS order_shipping_method, 
                    GROUP_CONCAT(IF (o.payment_method = '','  ',o.payment_method) ORDER BY o.order_id DESC SEPARATOR '
') AS order_payment_method, 
                    GROUP_CONCAT(IFNULL((SELECT os.name FROM `ecom_order_status` os WHERE os.order_status_id = o.order_status_id AND os.language_id = '1'),'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_status, 
                    GROUP_CONCAT(o.store_name ORDER BY o.order_id DESC SEPARATOR '
') AS order_store, 
                    GROUP_CONCAT(o.currency_code ORDER BY o.order_id DESC SEPARATOR '
') AS order_currency, 
                    GROUP_CONCAT(IFNULL((SELECT SUM(op.quantity) FROM `ecom_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id),'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_products, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(SUM(1/currency_rate*ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_sub_total, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(1/currency_rate*SUM(ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'shipping' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_shipping, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(1/currency_rate*SUM(ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_tax, 
                    GROUP_CONCAT(ROUND(o.currency_value*o.total, 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_value, 
                    GROUP_CONCAT(ROUND(o.currency_value*(IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 *(Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0)), 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_sales, 
                    GROUP_CONCAT(ROUND(o.currency_value*(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission), 2) ORDER BY o.order_id DESC SEPARATOR '
-') AS order_costs, 
                    GROUP_CONCAT(ROUND(o.currency_value*((IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 *(Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))-(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission)), 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_profit, 
                    GROUP_CONCAT(IFNULL(ROUND(100*(((IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 (Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))-(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission)) / (IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 (Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))), 2),0) ORDER BY o.order_id DESC SEPARATOR '%
') AS order_profit_margin_percent,  (SELECT COUNT(o.order_id) FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572') AS orders_total, 
        (SELECT COUNT(DISTINCT CONCAT(o.lastname, ', ', o.firstname)) FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572') AS customers_total, 
        (SELECT SUM(op.quantity) FROM `ecom_order_product` op, `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND op.order_id = o.order_id) AS products_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'sub_total') AS sub_total_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'handling') AS handling_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'low_order_fee') AS low_order_fee_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'shipping') AS shipping_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'reward') AS reward_total, 
        (SELECT SUM(1/c.value  (-1 (Abs(ot.value)))) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion')) AS coupon_total, 
        (SELECT SUM(ROUND(1/c.value * ot.value, 2)) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'tax') AS tax_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'credit') AS credit_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'voucher') AS voucher_total, 
        (SELECT SUM(1/c.value * o.total) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS total_total, 
        (SELECT SUM(1/c.value * (op.cost*op.quantity)) FROM `ecom_order_product` op, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id AND op.order_id = o.order_id) AS prod_costs_total, 
        (SELECT SUM(1/c.value * o.commission) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS commission_total, 
        (SELECT SUM(1/c.value * o.payment_cost) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS pay_costs_total, 
        (SELECT SUM(1/c.value * o.shipping_cost) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS ship_costs_total 
        
        FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' GROUP BY YEAR(o.date_added), MONTH(o.date_added) ORDER BY date_added DESC 

Is there any way I can get all the records in different rows?
(1): https://i.stack.imgur.com/poKup.png
(2): https://i.stack.imgur.com/jvAbd.png

postgresql performance – Postgres tracking count of recent items in count table or by selecting count of recent records from history table

Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has passed for that unique id.
Option 2 is not storing a count and fetching the count of records from a history table, by the records’ unique ids and within the past so many time units.
I would add an index on the id and timestamp fields for option 2.
The history table is in the low millions of records and doesn’t grow that fast.
Is the periodic vacuuming of the counts table likely to be much worse than selecting the count from the history table using indexed fields?
It seems like it would require less operations, no insert or update with Option 2.