etc_query: all things Textpattern

etc · 2015-09-12 09:33:41

Nothing to be sorry about, that helped me to fix few glitches.

etc · 2015-09-12 09:46:27

Version 1.3.1 is out, just few minor fixes and 4.6 compatibility.

NicolasGraph · 2015-09-12 12:41:35

etc wrote #294749:

Version 1.3.1 is out, just few minor fixes and 4.6 compatibility.

Thanks!

Any advice about the use of regex to allow me to create excerpt from the boby tag properly? My code is:

<txp:etc_query data='<txp:body />' specials="" query="substring(//p[1], 0, 140)" limit="1" wraptag="p" />

…but, of course, it does not cut properly. Cutting after the last entire word before the character 140 would be better.

Edit: it’s maybe even possible to add …?

Last edited by NicolasGraph (2015-09-12 12:43:26)

etc · 2015-09-12 16:50:43

You can register any php (string) function for use with etc_query. I’m not sure this is optimal, but it should work:

<txp:etc_query data='<txp:body />' specials="" functions="preg_replace,trim" limit="1" wraptag="p"
    query="preg_replace('/\w*:::/', '&hellip;', preg_replace('/^(.{140}).+/s', '$1:::', trim(string(p[1]))))" />

Edit: but be careful if you use it inside another etc_query, you might need to protect the {140} part.

Last edited by etc (2015-09-12 21:20:19)

NicolasGraph · 2015-09-12 17:04:58

etc wrote #294752:

You can register any php (string) function for use with etc_query. I’m not sure this is optimal, but it should work.

Thank you Oleg…

NicolasGraph · 2015-09-23 14:01:35

Hi,
I would like to know if there is any chance to make the following code work…
I’m looking for three comma separated image id’s in the article_image field but I can’t make REGEXP work.
Is that possible? Do I need to use the functions attribute? …

<txp:etc_query data="Image REGEXP '^((\d+,)\s*){2}\d+$'" populate="article">
	<txp:article_image />
</txp:etc_query>

Thanks…

etc · 2015-09-23 17:40:41

No, but since you are in db mode here, you must use MySQL regexp notations, which are weird. Try (no warranty)

<txp:etc_query markup="db" populate="article"
	data="Image REGEXP '^(([[:digit:]]+,)[[:space:]]*){2}[[:digit:]]+$'"
>
	<txp:article_image />
</txp:etc_query>

NicolasGraph · 2015-09-24 20:58:44

etc wrote #295096:

No, but since you are in db mode here, you must use MySQL regexp notations, which are weird. Try (no warranty)

<txp:etc_query markup="db" populate="article"...

Thanks, I’ll give it a try and learn more about MySQL regexp notations after holydays.

NicolasGraph · 2015-10-17 14:22:36

NicolasGraph wrote #295093:

Hi,
I would like to know if there is any chance to make the following code work…
I’m looking for three comma separated image id’s in the article_image field but I can’t make REGEXP work.
Is that possible? Do I need to use the functions attribute? …

<txp:etc_query data="Image REGEXP '^((\d+,)\s*){2}\d+$'" populate="article">...

Thanks…

I can’t get it work but it is not really important. Thank you for your reply.

etc · 2015-10-17 15:46:21

That works for me, but may be server-dependent.

lazlo · 2015-11-05 22:16:21

Hi Oleg (SOLVED)

I am implementing your wonderful A-Z filter / paginated A-Z article list but I am having trouble limiting the database call to specific textpattern section.

Is there a way to adjust this call to delimit textpattern sections?
I have a section called “Books” to be specific.

<txp:etc_query wraptag="nav" break=" | " name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 ORDER BY alpha"
>
	<a href="?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

I have tried various iterations on the call by adding AND Section = ‘books’ but with no success.

I didn’t realize there were two calls that needed to be changed so the following is the solution.

NEW Code



<!-- alpha list -->
<txp:etc_query wraptag="nav" break=" | " name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' ORDER BY alpha"
>
	<a href="?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="_GET" data='<txp:variable name="alphalist" />'
	query="//a[text() = '{?alpha|A}']"
	replace="&=<span class='active'>{?alpha|A}</span>"
>
	{//nav}
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' ORDER BY Title" />
<txp:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

<txp:if_variable name="ids">
	<!-- Pagination bar -->
	<txp:etc_query name="pages" data="{?ids|1|substr_count($|,).+1./2.ceil}" />
	<txp:etc_pagination pgcounter="page" link="Page {*}" wraptag="nav" break=" | "
		pages='<txp:variable name="pages" />'  />
	<!-- articles output -->
	<txp:article_custom id='<txp:variable name="ids" />' limit="2"
		offset='<txp:etc_offset pgcounter="page" pageby="2" />' />
</txp:if_variable>

Last edited by lazlo (2015-11-05 23:16:55)

etc · 2015-11-06 09:43:19

Hi Leslie, glad you have solved it. For multiple sections, you can add

Section IN('books', 'news')

to the appropriate WHERE clauses. And if you have expired/future articles, you may need also

 (NOW() <= Expires OR Expires = '0000-00-00') AND Posted <= NOW()

Last edited by etc (2015-11-06 10:41:07)

lazlo · 2015-11-21 02:33:41

Hey Oleg

So far the A-Z works wonderfully and because it worked so well I was asked if it could parse for current category.
Category2 in my case has ‘‘fiction’, ‘drama’

I got as far as this.

<!-- alpha list display A-Z-->
<txp:variable name="current_category" value="<txp:category />" />
<txp:variable name="current_category" />

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 = 'drama' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/<txp:category />/?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="_GET" data='<txp:variable name="alphalist" />'
	query="//a[text() = '{?alpha|A}']"
	replace="&=<span class='active'>{?alpha|A}</span>"
>
	{//ul}
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = 'drama' ORDER BY Title" />
<txp:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

<txp:hide>
<txp:if_variable name="ids">
	<!-- Pagination bar -->
	<txp:etc_query name="pages" data="{?ids|1|substr_count($|,).+1./2.ceil}" />
	<txp:etc_pagination pgcounter="page" link="Page {*}" wraptag="nav" break=" | "
		pages='<txp:variable name="pages" />'  />

        <!-- articles output -->
	<txp:article_custom id='<txp:variable name="ids" />' limit="2"
		offset='<txp:etc_offset pgcounter="page" pageby="2" />' />

</txp:if_variable>
</txp:hide>

<!-- End alpha list -->

First I couldn’t get the the <txp:variable name=“current_category” /> to work. So I tried just for the specific case ‘drama’ which still does not work the way I want.
Current code above does this:

URL…textpattern455/books/
(with code directly above shows “A-Z” and Category drama [which makes sense] but what I want is this to show all books)

URL…textpattern455/books/?alpha=B
(with code directly above shows “B” drama but what I want is this to show all “B” books)

URL…textpattern455/books/drama
(shows entire list of drama but what I want is “A-Z” drama )

URL…textpattern455/books/drama/?alpha=B
(shows entire list of drama but what I want is “B” drama)

My original code prior (listed in message above this one) was wrapped in this:
<txp:if_article_list>
<txp:if_section name=“books”>
<txp:if_category name=” “>
<txp:output_form form=“alpha_list_display” /> (links to the code in the previous message above)
</txp:if_category>
</txp:if_section>
</txp:article_list>

Now it is just this:
<txp:if_article_list>
<txp:if_section name=“books”>
<txp:output_form form=“alpha_list_display” /> (links to the code in this message)
</txp:if_section>
</txp:article_list>

I am wondering if I have to have a separate “category_alpha_list_display” and I would have to call it at this level?

<txp:if_article_list>
<txp:if_section name=“books”>
<txp:if_category name=” “>
<txp:output_form form=“alpha_list_display” />
<txp:else>
<txp:output_form form=“category_alpha_list_display” />
</txp:if_category>
</txp:if_section>
</txp:article_list>

So can I get a query that works for this “URL…textpattern455/books/drama/?alpha=B “ and does it need to be separate from the original query that worked for “URL…textpattern455/books/”

Regards
Leslie

Last edited by lazlo (2015-11-21 02:35:34)

etc · 2015-11-21 14:12:17

Hi Leslie, let’s see. Textpattern has no native section/category url mode, so I presume you are using some rewrite rule or plugin? Some of them (like gbp_permanent_links or smd_tags) change internal Textpattern variables and potentially can perturb others work, but let’s assume they play well here. So, <txp:category /> on books/drama page should output “drama” (please check). If so, use single quotes in your <txp:variable /> definition:

<txp:variable name="current_category" value='<txp:category />' />
<txp:variable name="current_category" />

Now, replace Category2 = 'drama' with

Category2 LIKE '<txp:variable name="current_category" />%'

in both db queries. That’s a bit fragile, since it will also match dramatic+art on books/drama page, but ok for testing. Please report back whether it fixes some points or not, and we will see the rest.

lazlo · 2015-11-21 22:29:46

Hey Oleg

I can’t get a <txp:variable /> to work in the code. The best I have managed is this output, this case <txp:variable name=“currentcategory” /> is “fiction” [Category2 LIKE “fiction%”]:

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE "fiction%" ORDER BY alpha"

Which is produced from this code:

<txp:variable name="currentcategory" value='<txp:category />' />
<txp:variable name="currentcategory" />
<txp:variable name="ids" />

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE "<txp:variable name='currentcategory' />%" ORDER BY alpha"

I think I have tried every variation on single and double quotes, and this is the only version I have come up with that actually makes an etc_query call. They either threw back syntax errors or failed silently with no etc_query call happening at all.

If I don’t use double quotes and use single quotes like this

Category2 LIKE '<txp:variable name='currentcategory' />%'

I get this error:

Tag error: <txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE '<txp:variable name='currentcategory' />%' ORDER BY alpha"

and this version fails silently without etc_query even being called.

Category2 LIKE '<txp:variable name="currentcategory" />%'

Solving this variable call sure would be nice as I could hard code the category but defeats the purpose of a dynamic CMS.

Textpattern CMS

Textpattern CMS support forum

#241 2015-09-12 09:33:41

Re: etc_query: all things Textpattern

#242 2015-09-12 09:46:27

Re: etc_query: all things Textpattern

#243 2015-09-12 12:41:35

Re: etc_query: all things Textpattern

etc wrote #294749:

#244 2015-09-12 16:50:43

Re: etc_query: all things Textpattern

#245 2015-09-12 17:04:58

Re: etc_query: all things Textpattern

etc wrote #294752:

#246 2015-09-23 14:01:35

Re: etc_query: all things Textpattern

#247 2015-09-23 17:40:41

Re: etc_query: all things Textpattern

#248 2015-09-24 20:58:44

Re: etc_query: all things Textpattern

etc wrote #295096:

#249 2015-10-17 14:22:36

Re: etc_query: all things Textpattern

NicolasGraph wrote #295093:

#250 2015-10-17 15:46:21

Re: etc_query: all things Textpattern

#251 2015-11-05 22:16:21

Re: etc_query: all things Textpattern

#252 2015-11-06 09:43:19

Re: etc_query: all things Textpattern

#253 2015-11-21 02:33:41

Re: etc_query: all things Textpattern

#254 2015-11-21 14:12:17

Re: etc_query: all things Textpattern

#255 2015-11-21 22:29:46

Re: etc_query: all things Textpattern

Board footer