No, doing pagination and scoring in the front end is slow since it requires sending too much data to the visitor. (Ordering wouldn't be, but pagination depends on order and thus ordering also needs to be done in the database.)vevy wrote: ↑Fri Dec 25, 2020 3:58 pmIsn't it better to leave those to the frontend?Andrew Lee wrote: ↑Wed Dec 23, 2020 9:03 pm The SQL query actually includes a lot of other stuff, including pagination, ordering, scoring etc. which are built up along various stages, cumulating into a single SQL query.
Site usability testing results
Re: Site usability testing results
My YouTube channel | Release date of my 13th playlist: August 24, 2020
Re: Site usability testing results
Of course, but I didn't mean it that way. Maybe I used the wrong term.
- SQL query provides the list of apps matching the search term.
- The part responsible for preparing the page itself (the PHP server?) handles what goes where; e.g. pagination, etc.
- Andrew Lee
- Posts: 3070
- Joined: Sat Feb 04, 2006 9:19 am
- Contact:
Re: Site usability testing results
In this particular case, I don't think doing it at the front end will buy much (unlike sorting a tabular list of values, for example).
The way it is currently done is to start with a generic select. Then it is slowly built along the pipeline to add constraints (search in title/desc/keywords, public/private etc.), ordering (date/popularity), and pagination (using offset/limit). So a single SQL returns the page of entries required (between 5 to 100, depending on user setting), which is passed to the templating engine to format into HTML, then pushed to the frontend. Very little processing occurs at the frontend.
The only bit that makes sense to do at the frontend is for a search listing, where all the entries are pushed to the frontend, and the frontend does the sorting, pagination and highlighting. Though technically possible, this requires making a massive exception for search results and requires a huge break away from the current architecture.
Doing it at the backend is far easier, since this entails only manually sorting and paginating the full search results (the size should already be greatly constrained by the query keywords). Still, this is quite a bit more work than the current hack, so I will just procrastinate for as long as I can
The way it is currently done is to start with a generic select. Then it is slowly built along the pipeline to add constraints (search in title/desc/keywords, public/private etc.), ordering (date/popularity), and pagination (using offset/limit). So a single SQL returns the page of entries required (between 5 to 100, depending on user setting), which is passed to the templating engine to format into HTML, then pushed to the frontend. Very little processing occurs at the frontend.
The only bit that makes sense to do at the frontend is for a search listing, where all the entries are pushed to the frontend, and the frontend does the sorting, pagination and highlighting. Though technically possible, this requires making a massive exception for search results and requires a huge break away from the current architecture.
Doing it at the backend is far easier, since this entails only manually sorting and paginating the full search results (the size should already be greatly constrained by the query keywords). Still, this is quite a bit more work than the current hack, so I will just procrastinate for as long as I can
Re: Site usability testing results
That part can't help?Andrew Lee wrote: ↑Sat Dec 26, 2020 9:36 pm which is passed to the templating engine to format into HTML,
Also, how about adding a virtual scoring column to the query where; e.g., a match in title adds 10 and a match in description adds 3, etc. Then use that column for sorting at the end of the query steps?
sigh!Still, this is quite a bit more work than the current hack, so I will just procrastinate for as long as I can
- Andrew Lee
- Posts: 3070
- Joined: Sat Feb 04, 2006 9:19 am
- Contact:
Re: Site usability testing results
How so? The templating engine is at the backend. The code for TPFC came before all the frontend madness in ReactJS and AngularJS that we have today (not that I know much about either of them ).
How can that be done?
Currently the SQL is in the form:
Code: Select all
select ... from ... where ... and
(
match(software.title) against ($keywords in boolean mode) or
match(software.description) against ($keywords in boolean mode) or
match(software.keywords) against ($keywords in boolean mode)
)
order by ...
limit $offset, $pagesize
How do I create a virtual score for each row?
Re: Site usability testing results
You can try something like:
The number (10,3,5) is to give weight to certain fields.
Now you have with a score, where you can continue ordering etc.
Code: Select all
WITH scoretable (
st_score,
st_id,
st_title,
st_description,
st_keywords,
etc
)
AS (
SELECT 10,
id,
title,
description,
keywords,
etc
FROM mytable
WHERE title LIKE "%searchterm%"
UNION ALL
SELECT 3,
id,
title,
description,
keywords,
etc
FROM mytable
WHERE description LIKE "%searchterm%"
UNION ALL
SELECT 5,
id,
title,
description,
keywords,
etc
FROM mytable
WHERE keywords LIKE "%searchterm%"
)
SELECT SUM(st_score),
st_id,
st_title,
st_description,
st_keywords,
etc
FROM scoretable
GROUP BY st_id
Now you have with a score, where you can continue ordering etc.
- Andrew Lee
- Posts: 3070
- Joined: Sat Feb 04, 2006 9:19 am
- Contact:
Re: Site usability testing results
Thanks for the suggestion.
I understand your concept, but it won't be an easy fit either. The current "select" is already a huge, convoluted nightmare I am not sure I want to deal with 4x of that (with minor changes in each variation)!
But I will keep this idea in mind when I am next forced to refactor the code.
I understand your concept, but it won't be an easy fit either. The current "select" is already a huge, convoluted nightmare I am not sure I want to deal with 4x of that (with minor changes in each variation)!
But I will keep this idea in mind when I am next forced to refactor the code.
Re: Site usability testing results
I am sure a scoring query can be optimized significantly, though.