Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2022-06-14 01:36:42

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Extracting values from a textile formatted table in a custom field

I have a large number of articles, each with a custom field displaying product dimensions in a table using Textile formatting:

Product Code Width Height Depth
Cartesio W Bathtub with shelves AVAS0995 1820mm 535mm 1010mm
Cartesio W Bathtub AVAS0994 1820mm 535mm 810mm
|_.  |_. Product Code |_. Width |_. Height |_. Depth |
| Cartesio W Bathtub with shelves | AVAS0995 | 1820mm | 535mm | 1010mm |
| Cartesio W Bathtub | AVAS0994 | 1820mm | 535mm | 810mm |

The columns in the table vary between products eg. sometimes the depth is shown first, and there may be other columns.

I’m looking to sort/filter and/or search by a particular dimension eg. show all bathtubs sorted by width, or all basins < 400mm depth.

I’ve been considering using evaluate / etc_query etc. to:

  1. query the first row to work out the relevant column number for each dimension
  2. target the remaining rows for matches in the same column

Is this a reasonable approach, given I’m clueless at Xpath but prepared to learn?

In the past I’ve done this via complex client-side Javascript, but I’d prefer to build something simpler using Textpattern…

Offline

#2 2022-06-14 08:13:48

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Extracting values from a textile formatted table in a custom field

Hmmm. Can’t think of anything neat offhand. Oleg or Jools probably can :)

When you say “complex client side JavaScript,” do you mean custom code, or have you tried one of those table sort plugins? I’ve used one or two and they’re pretty cool. Most permit filtering, pagination, and sorting.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#3 2022-06-14 08:18:28

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Extracting values from a textile formatted table in a custom field

It may be an upheaval, btw, but if you could store the individual parameters in custom fields Product_code, width, depth, height, etc, you could build the table using a form that you could include in every product page. That gives you way more flexibility for filtering via article_custom.

Even if you don’t build the table in a form (which is tricky, but not impossible, if the columns vary, since you need to test each one before inclusion), you could bring the values into your textiled table perhaps from the fields instead of hard coding them in the body content.

Last edited by Bloke (2022-06-14 08:21:37)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#4 2022-06-14 19:39:29

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Re: Extracting values from a textile formatted table in a custom field

Thanks Bloke.

Most products have multiple variants; we considered custom fields for everything back when the site was first developed, but it would have resulted in a 10-fold increase in the number of articles required, with the added overhead for admin users in hooking it all together.

If I was to massage the table so that width/depth/height are always the last three columns (the most common scenario), I’d make things simpler for myself…

Which approach do you think is likely to offer the least resistance / most amenity?

  • Process queries live with some gnarly Xpath
  • Occasionally index all product dimensions to a text file (or cached form) in a format that makes live querying simpler
    • Process queries live with simpler Xpath / etc
  • Include dimension tables in the public html (hidden) and use a js library like Mixitup to handle sorting / filtering
  • Convert each row of the dimension table into a shortcode which magically makes dimensions easier to search?

There are around 1000 articles in the products section. Dimension searches will largely deal with a subset of these – querying up to 100 articles for a match.

Offline

#5 2022-06-16 09:55:14

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Extracting values from a textile formatted table in a custom field

I agree with Stef, your storage format is not ideal, but yes, it’s possible to handle it with XPath. For example, to retrieve all rows with depth < 400mm you can try

<!-- find the 'Depth' column index and store it in 'depth' variable -->
<txp:etc_query data='<txp:custom_field name="cf_name" escape="textile" />'
    query="count(//th[normalize-space()='Depth']/preceding-sibling::th)+1"
    name="depth" save="table"
/>

<!-- retrieve all rows with depth<400mm -->
<txp:etc_query data="table" markup="data"
    query="//td[{?depth}][number(substring-before(text(), 'mm'))<400]/.."
/>

You see the main weakness of this method : the performance. On each access the data needs to be textiled and then parsed by XPath. Using separate cf would be much better.

Offline

#6 2022-06-16 19:40:43

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Re: Extracting values from a textile formatted table in a custom field

Thank you etc. Your code snippets are always enlightening; I learn more from your forum posts than the docs!

I’ll run some tests on various data sets and query types to see how badly performance is effected; I’m not expecting miracles, but it’ll give me a baseline to work off.

I’m still in the feasibility stage, looking for the pros and cons of various approaches. While a separate article with dimension fields for each product variant is obviously the most performant solution, I’m concerned the extra admin overhead will prove too much. For the majority of products, variants differ by a single dimension only…

Would adding 30+ custom fields to each article be worth investigating? eg.

custom_field name=“variant01_width”
variant_id, w, 1000
custom_field name=“variant01_depth”
variant_id, d, 500
etc.
or maybe:
custom_field name=“variant01_wdh”
variant_id, 1000, 500, 720
custom_field name=“variant02_wdh”
variant_id2, 1000, 550, 720
etc.

Last edited by giz (2022-06-16 19:52:15)

Offline

#7 2022-06-16 21:16:10

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Extracting values from a textile formatted table in a custom field

giz wrote #333601:

Would adding 30+ custom fields to each article be worth investigating?

Dunno, it looks like a future multi-value custom fields txp version would work, but we are not there yet.

If the performance is decent in your tests, you can also try to replace query with (the opposite) replace in the second block:

<!-- delete all rows with depth>400mm -->
<txp:etc_query data="table" markup="data"
    replace="//td[{?depth}][number(substring-before(text(), 'mm'))>400]/.."
/>

This will keep the table structure (header, body), just removing the unneeded rows.

Offline

#8 2022-06-17 18:00:10

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Re: Extracting values from a textile formatted table in a custom field

Thanks etc.

👍

Offline

Board footer

Powered by FluxBB