Extract MAX value from custom_field

Pat64 · 2022-03-23 18:54:25

Hi all ;)

I want to convert a value stored in a custom field (number of views for the current page) to a “star rating”; the math formula is: current page views x 5 / Max value of views on the entire website.

So I need to extract this MAX() value. How to with a <txp:article_custom /> tag and maybe a FIELD() attribute?

Thank you in advance for your help.

etc · 2022-03-23 20:36:05

That shouldn’t be difficult:

<txp:article_custom fields="max(cfname)" limit="0">
    <txp:custom_field name="cfname" />
</txp:article_custom>

jakob · 2022-03-23 20:44:02

Not sure if I’ve understood you correctly but could you not simply sort your articles by the pageviews custom field ordered by descending and limit output to the first one:

<txp:article_custom section="mysection" sort="custom_1+0 desc" limit="1">
    <txp:variable name="pageviews_max"><txp:custom_field name="pageviews" /></txp:variable>
</txp:article_custom>
<txp:php>
    global $variable, $thisarticle;
    $variable['star_rating'] = round( (intval($thisarticle['custom_1']) * 5) / intval($variable['pageviews_max']), 1);
</txp:php>

replacing custom_1 and pageviews with the custom field name and number you are using.

jakob · 2022-03-23 20:47:14

etc wrote #332980:

That shouldn’t be difficult:

<txp:article_custom fields="max(cfname)" limit="0">...

Neat. The fields attribute is new to me. It looks like it’s undocumented in the docs as yet.

Pat64 · 2022-03-24 03:16:24

etc wrote #332980:

That shouldn’t be difficult:

<txp:article_custom fields="max(cfname)" limit="0">...

I tried this:

<txp:article_custom fields="max(custom_1)" limit="0">
<txp:custom_field name="pageviews" />
</txp:article_custom>

… I got… a list of corresponding custom field values but not the MAX() one…

Trying this:

<txp:article_custom fields="max(pageviews)" limit="0">
<txp:custom_field name="pageviews" />
</txp:article_custom>

I got only one value but not the MAX() one…

Last edited by Pat64 (2022-03-24 03:19:29)

etc · 2022-03-24 08:27:07

Pat64 wrote #332984:

I got only one value but not the MAX() one…

That’s probably due to the default varchar type of custom fields, so ‘5’ is greater than ‘12’. You can either try to convert pageviews cf to int or follow jacob’s advice, but ordering by pageviews+0 desc.

gaekwad · 2022-03-24 10:05:52

jakob wrote #332982:

It looks like it’s undocumented in the docs as yet.

github.com/textpattern/textpattern.github.io/issues/198

Pat64 · 2022-03-24 19:05:51

Fine.
Thank you, Oleg ;)

I’m using this with some good results (only one: the max value):

<txp:article_custom section='<txp:section />' fields="max(pageviews+0)" limit="1" sort="custom_2+0 desc">
<txp:custom_field name="pageviews" />
</txp:article_custom>

Note: pageviews is my custom field name and custom_2 its column into the textpattern table.

It seems obvious Oleg for all your advice given that you are one of the best users of Textpattern! A kind of magician.

jakob · 2022-03-24 19:59:12

Pat64 wrote #332994:

I’m using this with some good results (only one: the max value):

<txp:article_custom section='<txp:section />' fields="max(pageviews+0)" limit="1" sort="custom_2+0 desc">...

What roles does the fields attribute play there? If you leave it out, I’m guessing (without having tested it) that it should also output a single article in your section with the greatest value for custom_2.

Pat64 · 2022-03-24 20:43:28

I did some tests on a local server with few articles. The code above seems to work. Unfortunately, not within a website in production… Still need some searches to make that working…

etc · 2022-03-24 20:52:33

Pat64 wrote #332994:

It seems obvious Oleg for all your advice given that you are one of the best users of Textpattern!

That’s what usually happens after ~1300 commits, but thank you :-)

jakob wrote #332995:

What roles does the fields attribute play there? If you leave it out, I’m guessing (without having tested it) that it should also output a single article in your section with the greatest value for custom_2.

I concur, fields is useless and unused here (and it must be sort="custom_2+0", not pageviews). We could allow for expressions like max(cfname+0), but it feels ugly.

Pat64 · 2022-03-25 03:12:44

Here is my poor solution for the moment (it’s ugly):

A list of all values:

<txp:variable name="max_values"><txp:article_custom section='<txp:section />' limit="0" sort="custom_2"><txp:custom_field name="pageviews" />,</txp:article_custom></txp:variable>

… and the max one:

<txp:php>
/**
 * Rating value based on this
 * page view and max views
 */
global $variable;

$max = max(explode(',', $variable['max_values']));
echo ceil(custom_field(array('name'=>'pageviews')) * 5 / $max);

</txp:php>

etc wrote #332997:

I concur, fields is useless and unused here (and it must be sort="custom_2+0", not pageviews). We could allow for expressions like max(cfname+0), but it feels ugly.

Yes Oleg. Sorry, I didn’t try: only sort="custom_2+0" is needed.

Last edited by Pat64 (2023-06-30 12:03:51)

Textpattern CMS

Textpattern CMS support forum

#1 2022-03-23 18:54:25