Anchor links in tables do not work with `true as search`

[lovasoa]

When a table has true as search, loading the page with a specific anchor fails.

Minimal repro:

index.sql

select 'button' as component;
select 'scroll to 256' as title, 'scroll.sql' as link;

SELECT 'table' AS component, true as search;
with recursive numbers(n) as (
    select 1 union all select n + 1 from numbers where n < 500
)
SELECT n, n as _sqlpage_id FROM numbers;

scroll.sql

select 'redirect' as component, '/x.sql#256' as link;

Discussed in #538

^{Originally posted by lozdown August 15, 2024}

Background

I populate a table component with rows from a database. Each row is identified using unique column id. Using markdown a column in each row provides a link to another sqlpage page with id as a parameter. This second page updates the database and returns back to the table. I want to return to the row used in the call identified by id. I intended to use an html anchor but I can't make it work.

There seems to be two problems

1. the example url http://127.0.0.1:8080/member-event.sql#id441963 does not move to the requested row in the table created by member-event.sql
2. the returning redirect component does not create the correct url

Table component definition (cut down) from member-event.sql

 select 
 	'table' as 'component', true as 'small',true as striped_columns,
 	TRUE as search,
 	'2024-01-17' as markdown
 	;
 
select m."First Name",m."Last Name",
 	case when "2024-01-17"  
 		then 
 			format('[](#%s)[✓](remove-member-event.sql?memberid=%s&eventid=%s)', a.contactid,a.contactid,(select distinct id from eventinfo where eventdate='2024-01-17') ) 
		else 
 			format('[](#%s)[x](update-member-event.sql?memberid=%s&eventid=%s)', a.contactid,a.contactid,(select distinct id from eventinfo where eventdate='2024-01-17') )  
		end as "2024-01-17" 
 	from members m left join attendances a on m."Contact ID"=a.contactid
;
 

The update-member-event.sql file definition is

INSERT INTO public.events_members(
	events_id, "members_Contact ID")
	VALUES ($eventid::int, $memberid::int)
RETURNING
    'redirect' AS component,
    'member-event.sql#$memberid' AS link
;

I was expecting the redirect to return a url something like member-event.sql#441963 but it actually returns verbatim (ie with no substitution of the parameter value) member-event.sql#$memberid

[lovasoa]

This seems to be a bug in the list.js library that we use to provide the search functionality

[lovasoa]

Here is a single page repro that illustrates the issue quite well:

select 'button' as component;
select 'scroll to 256' as title, '?_=' || random() || '&search=' || COALESCE($search, 0) || '#256' as link;
select 'toggle search' as title, '?search=' || (1 - COALESCE($search, 0)) as link;


SELECT 'table' AS component, $search = '1' as search;
with recursive numbers(n) as (
    select 1 union all select n + 1 from numbers where n < 500
)
SELECT n, n as _sqlpage_id FROM numbers;

[lovasoa]

@lozdown : the bug comes from a library we use. Disabling async indexing works around the bug, but makes page loads junky with large tables.

Can you try the latest commit (the easiest is to try it from docker with lovasoa/sqlpage:main) ? And report how it works, including with large tables, compared to the previous version.

[lozdown]

@lovasoa Much appreciate your work. I would like to try and report on the latest commit but I have no experience of docker.

[lovasoa]

Here is a linux build of the latest version. If you are not using linux, you can wait for the next version, the fix will be in it !

sqlpage-prerelease.zip

[lozdown]

Works perfectly and performance is excellent. Using a very simple query returning a simple table of 5 columns by 5000 rows takes approx 3 secs (of which ~140msec is database execution time) to load the page and approx 1sec to search for a row. That capability is well beyond my needs. What more can I say - Thank you