How to sort etc with Data from supabase

[nosizejosh]

Hello, @linmasahiro I have data from supabase using their interface and not axios. what I want to achieve ,

1. Get paginated data and be able to change page size and get more or less data from supabase
2. Get sort and search working with the data on the client side to reduce server hits what I have been able to achieve so far is to get the initial data showing. I have tried lots of the examples but cant get sorting to work. table just shows loading when I click the table headers.

My code below, please show how I can achieve this with your package

// async function fetchTickets() {
// let { data: tickets, error } = await client
//     .from('tickets')
//     .select('*')
// console.log("🚀 ~ file: TicketerTicketsTable.vue ~ line 8 ~ data", tickets)
// return tickets
// }

// const fetchTickets = async (offst, limit) => {
//     offst = offst + 1;
//     let { data: tickets, error } = await client
//         .from('tickets')
//         .select('*')
//     // console.log("🚀 ~ file: TicketerTicketsTable.vue ~ line 33 ~ fetchTickets ~ tickets", tickets)

//     return tickets;
// }

// fetchTickets()

// Init Your table settings
const table = reactive({
    isLoading: false,
    columns: [
        {
            label: "ID",
            field: "id",
            width: "5%",
            sortable: true,
            isKey: true,
        },
        {
            label: "Summary",
            field: "summary",
            width: "20%",
            sortable: true,
        },
        {
            label: "Status",
            field: "status",
            width: "10%",
            sortable: true,
        },
        {
            label: "Priority",
            field: "priority",
            width: "10%",
            sortable: true,
        },        
        {
            label: "Date from",
            field: "date_from",
            width: "10%",
            sortable: true,
        },       
    ],
    rows: [],
    totalRecordCount: computed(() => {
        return table.rows.length;
    }),
    // totalRecordCount: 3,
    sortable: {
        order: "id",
        sort: "asc",
    },
});

let { data: tickets, error } = await client
    .from('tickets')
    .select('*')

table.rows = tickets
// table.totalRecordCount = 

/**
   * Search Event
   */
const doSort = (offset, limit, order, sort) => {
    console.log("do sort called")
    // table.isLoading = true;
    table.isReSearch = offset == undefined ? true : false;
    // if (offset >= 10 || limit >= 20) {
    //     limit = 20;
    // }
    // if (sort == "asc") {
    //     table.rows = fetchTickets(offset, limit);
    // } else {
    //     table.rows = fetchTickets(offset, limit);
    // }
    table.rows = tickets;
    // table.totalRecordCount = 20;
    table.sortable.order = order;
    table.sortable.sort = sort;
};
// First get data
doSort(0, 10, 'id', 'asc')

// template code
 <table-lite :has-checkbox="true" :is-loading="table.isLoading" :columns="table.columns" :rows="table.rows"
            :total="table.totalRecordCount" :sortable="table.sortable" @do-search="doSort"
            @is-finished="table.isLoading = false" @return-checked-rows="updateCheckedRows" @row-clicked="rowClicked">
        </table-lite>

[linmasahiro]

Hi, @nosizejosh I have not Supabase environment now, so I followed supabase's document to created an example, try it.

<template>
  <table-lite
    :has-checkbox="true"
    :is-loading="table.isLoading"
    :columns="table.columns"
    :rows="table.rows"
    :total="table.totalRecordCount"
    :sortable="table.sortable"
    @do-search="doSort"
    @is-finished="table.isLoading = false"
  >
  </table-lite>
</template>

<script>
import { defineComponent, reactive } from "vue";
import TableLite from "vue3-table-lite";

export default defineComponent({
  name: "App",
  components: { TableLite },
  setup() {
    // Init Your table settings
    const table = reactive({
      isLoading: false,
      columns: [
        {
          label: "ID",
          field: "id",
          width: "5%",
          sortable: true,
          isKey: true,
        },
        {
          label: "Summary",
          field: "summary",
          width: "20%",
          sortable: true,
        },
        {
          label: "Status",
          field: "status",
          width: "10%",
          sortable: true,
        },
        {
          label: "Priority",
          field: "priority",
          width: "10%",
          sortable: true,
        },
        {
          label: "Date from",
          field: "date_from",
          width: "10%",
          sortable: true,
        },
      ],
      rows: [],
      totalRecordCount: 0,
      sortable: {
        order: "id",
        sort: "asc",
      },
    });

    /**
     * Search Event
     */
    const doSearch = async (offset, limit, order, sort) => {
      table.isLoading = true;
      let { data: tickets, error } = await client.from('tickets').select('*').order(order, { ascending: (sort == 'asc') ? true : false }).range(offset, limit);
      let { data: tmp, count, error2 } = await client.from('tickets').select('*', { count: 'exact' });
      if (error || error2) {
        // export your error message
        return; 
      }
      table.rows = tickets;
      table.totalRecordCount = count;
      table.sortable.order = order;
      table.sortable.sort = sort;
      table.isLoading = false;
    };

    // First get data
    doSearch(0, 10, "id", "asc");

    return {
      table,
      doSearch,
    };
  },
});
</script>

[nosizejosh]

@linmasahiro thanks, it works but a few questions.

1. why did you take out

   `// let { data: tickets, error } = await client
   //     .from('tickets')
   //     .select('*')
   // table.rows = tickets`

   after the reactive table declaration?

2. without that, sorting on all table headers work but they can take up to 20secs and I currently have only 8 rows in the entire table and db

3. why does your solution have 2 connections to the db and what does the tmp do since its never used?

   `let { data: tickets, error } = await client.from('tickets').select('*').order(order, { ascending: (sort == 'asc') ? true : false }).range(offset, limit);
   let { data: tmp, count, error2 } = await client.from('tickets').select('*', { count: 'exact' });`

4. Does this solution mean that every time the user clicks any of the table headers the application calls an api call to retrieve data?

5. For just 8 rows, each sorting action shows loading and takes at least 5 secs or more to finish loading, is there a way to make that faster as I can imagine in a real application this will be too slow with a lot of data.

6. what does this code mean, since all table headers can sort

   sortable: {
       order: "id",
       sort: "asc",
     },

Thank you!

[linmasahiro]

1. why did you take out after the reactive table declaration?

Because I am using doSearch() to get records.

2. without that, sorting on all table headers work but they can take up to 20secs and I currently have only 8 rows in the entire table and db

Actually I am not use supabase, so I don't know why, but If you'r records are not real-time, choice static-mode.

3. why does your solution have 2 connections to the db and what does the tmp do since its never used?

I am not use supabase, I am follwed supabase's document to get record count. Supabase querying with count option Maybe let { data: tickets, count, error } = await client.from('tickets').select('*', { count: 'exact' }).order(order, { ascending: (sort == 'asc') ? true : false }).range(offset, limit); is work too I don't know, you can try it and remove connection no2.

4. Does this solution mean that every time the user clicks any of the table headers the application calls an api call to retrieve data?

Yes, because you chose default-mode, if you want to call api once and not get anymore per sort, choose static-mode please.

5. For just 8 rows, each sorting action shows loading and takes at least 5 secs or more to finish loading, is there a way to make that faster as I can imagine in a real application this will be too slow with a lot of data.

I am not use supabase, so I don't know why, please check supabase's document.

6. what does this code mean, since all table headers can sort

This mean's first sort condition now. If you not hope sortable feature with the column, set sortable: false with the column.

[nosizejosh]

@linmasahiro Thanks so much taking the time to answer.
There is considerable performance improvement with the one call.

For my use case, I think the best solution will be to implement a flow where server is called once the page loads and that data is stored locally and the table can do manipulations ( sort, search, filter) on that data locally.
The page will also be responsible for checking for new data on the server and updating the locally stored data so that is shows in the able automatically. To implement this, here is my solution

const table = reactive({
    isLoading: false,
    columns: [
        {
            label: "ID",
            field: "id",
            width: "5%",
            sortable: true,
            isKey: true,
        },       
        {
            label: "Summary",
            field: "summary",
            width: "20%",
            sortable: true,
        },
    ],
    rows: [],
    totalRecordCount: 0,
    sortable: {
        order: "id",
        sort: "asc",
    },
});

const fetchTickets = async (offset, limit, order, sort) => {
    table.isLoading = true;
    let { data: tickets, count, error } = await client.from('tickets').select('*', { count: 'exact' }).order(order, { ascending: (sort == 'asc') ? true : false }).range(offset, limit);
    if (error) {
        // export your error message
        return;
    }
    table.rows = tickets;
    table.totalRecordCount = count;
    table.sortable.order = order;
    table.sortable.sort = sort;
    table.isLoading = false;
};

fetchTickets(0, 10, 'id', 'asc')

<table-lite :is-static-mode="true" :has-checkbox="true" :is-loading="table.isLoading" :columns="table.columns"
            :rows="table.rows" :total="table.totalRecordCount" :sortable="table.sortable"
            @is-finished="table.isLoading = false" @return-checked-rows="updateCheckedRows" @row-clicked="rowClicked"> </table-lite>

let me know what you think

[linmasahiro]

@nosizejosh

let me know what you think

I saw you using static-mode, not require like isLoading or order if use static-mode. About static-mode I think... maybe more simple like this.

    let { data: tickets, count, error } = await client.from('tickets').select('*', { count: 'exact' }).order('id', { ascending: true });
    if (error) {
        // export your error message
        return;
    }

    const table = reactive({
      columns: [
          {
              label: "ID",
              field: "id",
              width: "5%",
              sortable: true,
              isKey: true,
          },       
          {
              label: "Summary",
              field: "summary",
              width: "20%",
              sortable: true,
          },
      ],
      rows: tickets,
      totalRecordCount: computed(() => {
        return table.rows.length;
      }),
      sortable: {
          order: "id",
          sort: "asc",
      },
  });

 <table-lite
    :is-static-mode="true"
    :has-checkbox="true"
    :columns="table.columns"
    :rows="table.rows"
    :total="table.totalRecordCount"
    :sortable="table.sortable"
    @return-checked-rows="updateCheckedRows"
    @row-clicked="rowClicked"
  >
  </table-lite>

[nosizejosh]

Hey, @linmasahiro can a table have multiple modes? static and slot at the same time? for my case I need to pass two templates at id and summary columns and using display is not working. I have looked at this and this example, its not very clear. I cant seem to figure out how to add the two templates to each respective column and whether to have both static and slot mode for the same table.

<table-lite :is-static-mode="true" :is-slot-mode="true" :has-checkbox="true" :is-loading="table.isLoading"
            :columns="table.columns" :rows="table.rows" :total="table.totalRecordCount" :sortable="table.sortable"
            @is-finished="table.isLoading = false" @return-checked-rows="updateCheckedRows" @row-clicked="rowClicked">
            <template v-slot:name="data">
                <Test>
                    {{ data.value.name }}
                </Test>
            </template>
        </table-lite>

from your example what does data refer to? how do I customize this to have custom templates in ID and summary columns?

[linmasahiro]

Hi, @nosizejosh

1. have multiple modes? static and slot at the same time?

Yes, you can use static-mode and slot-mode at same time. I am wrote a very simple example for you, and you can check how to use slot in document too.

<template>
  <table-lite
    :is-static-mode="true"
    :is-slot-mode="true"
    :columns="table.columns"
    :rows="table.rows"
    :total="table.totalRecordCount"
    :sortable="table.sortable"
  >
    <template v-slot:summary="data">
      <span>{{ data.value.summary }}</span>
    </template>
  </table-lite>
</template>

<script>
import { defineComponent, reactive, computed } from "vue";
import TableLite from "vue3-table-lite";

export default defineComponent({
  name: "App",
  components: { TableLite },
  setup() {
    // Fake data
    const data = reactive([]);
    for (let i = 0; i < 126; i++) {
      data.push({
        id: i,
        summary: "TEST" + i
      });
    }

    // Table config
    const table = reactive({
      columns: [
        {
          label: "ID",
          field: "id",
          width: "3%",
          sortable: true,
          isKey: true,
        },
        {
          label: "Summary",
          field: "summary",
          width: "10%",
          sortable: true,
        }
      ],
      rows: data,
      totalRecordCount: computed(() => {
        return table.rows.length;
      }),
      sortable: {
        order: "id",
        sort: "asc",
      },
    });

    return {
      table,
    };
  },
});
</script>

2. your example what does data refer to?

The data is record per row.

[nosizejosh]

Hi, @nosizejosh

1. have multiple modes? static and slot at the same time?

Yes, you can use static-mode and slot-mode at same time. I am wrote a very simple example for you, and you can check how to use slot in document too.

<template>
  <table-lite
    :is-static-mode="true"
    :is-slot-mode="true"
    :columns="table.columns"
    :rows="table.rows"
    :total="table.totalRecordCount"
    :sortable="table.sortable"
  >
    <template v-slot:summary="data">
      <span>{{ data.value.summary }}</span>
    </template>
  </table-lite>
</template>

<script>
import { defineComponent, reactive, computed } from "vue";
import TableLite from "vue3-table-lite";

export default defineComponent({
  name: "App",
  components: { TableLite },
  setup() {
    // Fake data
    const data = reactive([]);
    for (let i = 0; i < 126; i++) {
      data.push({
        id: i,
        summary: "TEST" + i
      });
    }

    // Table config
    const table = reactive({
      columns: [
        {
          label: "ID",
          field: "id",
          width: "3%",
          sortable: true,
          isKey: true,
        },
        {
          label: "Summary",
          field: "summary",
          width: "10%",
          sortable: true,
        }
      ],
      rows: data,
      totalRecordCount: computed(() => {
        return table.rows.length;
      }),
      sortable: {
        order: "id",
        sort: "asc",
      },
    });

    return {
      table,
    };
  },
});
</script>

2. your example what does data refer to?

The data is record per row.

This works great. Thanks!