Textpattern CMS

#13 2014-10-09 22:02:00

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284653:

Thank you etc

Here is my existing textpattern code snippet and because I am not a PHP/MYSQL denizen could you explain your code a bit more in plain english or textpatternish.

If you knew that the author was always going to be in the same custom field, I think you could do without a plugin. The problem is your needs are a bit complex.

<txp:if_custom_field name="Author_1">
<txp:article_custom form="book_title" custom_1='<txp:custom_field name="Author_1" />' section="Books" sort="custom_4 desc" limit="50" />

<txp:else />

<txp:article_custom form="book_title" custom_1='<txp:custom_field name="Editor_1" />' section="Books" sort="custom_4 desc" limit="50" />
</txp:if_custom_field>

---

Michael K. Pate | Third Superpower

#14 2014-10-09 22:21:07

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

If you knew that the author was always going to be in the same custom field, I think you could do without a plugin. The problem is your needs are a bit complex.

Yes that is the problem Michael. Existing Author/Contributor could be an Editor or Contributor to other books in various roles (sometime 2 in 1 book, which is a pain).

In my upcoming revamp I will hopefully accomplish some simplification by not having Author_1, Author_2, etc… fields but just Author [ Author One | Author Two | Author Three | Author Four ] and using some magic parsing to do the same thing.

#15 2014-10-09 23:39:47

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

There actually is an all-native way to do this.

<txp:variable name="booksbythisauthor" value="" />

<txp:if_custom_field name="Author_1">
  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  >
    <txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
  </txp:article_custom custom>
  <txp:else />
  <txp:article_custom custom_1='<txp:custom_field name="Editor_1" />' section="Books"  >
    <txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
  </txp:article_custom custom>
</txp:if_custom_field>

[repeat for Author_2 et al]

<txp:if_custom_field name="booksbythisauthor">
  <txp:article_custom form="book_title" id='<txp:variable name="booksbythisauthor"  />' section="Books" sort="custom_4 desc" limit="50" />
</txp:if_custom_field>

I just have no clue about performance. I also can’t figure out – as it works right now, isn’t it showing the current book as another book by the author?

---

Michael K. Pate | Third Superpower

#16 2014-10-10 00:53:35

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

I just have no clue about performance. I also can’t figure out – as it works right now, isn’t it showing the current book as another book by the author?

Thanks Michael
The current code it doesn’t find the current book as another book by author, which is why i think I went this route. I am trying your solution out to no success yet, trying to figure out why your code is failing (for me).

#17 2014-10-10 00:56:30

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284670:

Thanks Michael
The current code it doesn’t find the current book as another book by author, which is why i think I went this route. I am trying your solution out to no success yet, trying to figure out why your code is failing (for me).

I might have missed something but I did test the variable function.

---

Michael K. Pate | Third Superpower

#18 2014-10-10 01:17:50

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Hey Michael
I had to strikeout the “else if” part for now as it was failing completely (?) at that point. But this adjusted code spits out this (after the code) The very final “[<txp:if_custom_field name=“booksbythisauthor”>: false]” should be true, so a bit confused. And then there is the Tag error.

<txp:variable name="booksbythisauthor" value="" />

<txp:if_custom_field name="Author_1"> Line 1
  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  >
      <txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
  </txp:article_custom custom>

</txp:if_custom_field>

[repeat for Author_2 et al]

<txp:if_custom_field name="booksbythisauthor">
  <txp:article_custom form="book_title" id='<txp:variable name="booksbythisauthor"  />' section="Books" sort="custom_4 desc" limit="50" />
</txp:if_custom_field>


<!-- Ends other Books by this Author -->

</txp:if_individual_article>
<txp:variable name="booksbythisauthor" value="" />
<txp:if_custom_field name="Author_1">
	[<txp:if_custom_field name="Author_1">: true]
	<txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  >
		[attribute 'custom_1']
		<txp:custom_field name="Author_1" />
		[/attribute]
		Tag error:  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  > -> Textpattern Notice: Unknown tag attribute: custom_1  on line 758
		[SQL (0.00319290161133): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section IN ('Books') order by Posted desc limit 0, 10]
		[article 2236]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2235]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2234]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2233]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2232]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2231]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2230]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2228]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2227]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2222]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
	</txp:article_custom>
	<txp:if_custom_field name="booksbythisauthor">
		[<txp:if_custom_field name="booksbythisauthor">: false]
	</txp:if_custom_field>
</txp:if_custom_field>

Last edited by lazlo (2014-10-10 01:38:24)

#19 2014-10-10 01:26:35

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Also wonder if a server gurus have any opinion on why my original code runs on my MAMP/Macbook at this speed:

<txp:soo_article_filter update_set="custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41)">
			[SQL (0.187816143036): create temporary table textpattern select * from textpattern]
			[SQL (0.634253025055): update textpattern set custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41) where 1=1]

compared to what my mighty host is providing:

<txp:soo_article_filter update_set="custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41)">
			[SQL (15.7792680264): create temporary table textpattern select * from textpattern]
			[SQL (14.9772799015): update textpattern set custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41) where 1=1]

Last edited by lazlo (2014-10-10 01:29:23)

#20 2014-10-10 03:00:17

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Or for anymore clarity if anyone else is reading this far down 4 years ago this was my original question to John Jeff Soo:

lazlo wrote #226850:

Hey Jeff

Another question that maybe you can help with.

I have the following fields:
Author_1
Author_2
Author_3
Editor_1
Editor_2
etc.

I want to merge/join/union into a temporary table (you choice on most appropriate) and then match against an existing author_1.
I was doing this individually but came up against matching null fields and having to have a function for every field.

So what I was doing

Does Author_1 match
Author_1

Does Author_1 match
Author_2

Does Author_1 match
Author_3

or for actual code:

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” limit=“50” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_3=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Editor_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Editor_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Translator_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Translator_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Photographer=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Illustrator=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

The above code kinda works except I can’t get it to ignore NULL entries so after I saw your plug-in I thought it might be easier to merge all those fields and do one function instead.
This is one reference I found that has brought me to you with this question, as it seems to be what you are doing with you plugin.
http://www.webmasterworld.com/forum112/60.htm

regards
Les Smith

p.

Last edited by lazlo (2014-10-11 19:22:48)

#21 2014-10-10 03:15:19

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284672:

Hey Michael
I had to strikeout the “else if” part for now as it was failing completely (?) at that point. But this adjusted code spits out this (after the code) The very final “[<txp:if_custom_field name=“booksbythisauthor”>: false]” should be true, so a bit confused. And then there is the Tag error.

Try putting the code somewhere you can see it on the page

Books by This Author: <txp:variable name="booksbythisauthor"  />

The final output should be something like “3,2,1,” – you can see a simpler version here at the bottom.

Tag error:  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  > -> Textpattern Notice: Unknown tag attribute: custom_1  on line 758

I think that means your custom field isn’t actually named “custom_1” – I think Jeff’s plugin used a different system of referring to them.

From article_custom

customfieldname="value"

* Restrict to articles with specified value for specified custom field name. Replace "customfieldname" with the name of the custom field.
* IMPORTANT: Using dashes (-) or spaces may cause errors or render this feature ineffectual. Underscores in both custom field names and values are confirmed to work.
* Default: unset.

---

Michael K. Pate | Third Superpower

#22 2014-10-10 08:13:58

ruud

Developer Emeritus

From: a galaxy far far away

Registered: 2006-06-04

Posts: 5,068

Website

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284674:

Also wonder if a server gurus have any opinion on why my original code runs on my MAMP/Macbook at this speed:

Probably because your Macbook has a blazing fast SSD and/or different MySQL settings, which helps when you create a temporary table, while a webhost typically has multiple users on the same server using a raid array of normal harddisks with MySQL set to conserve memory.

#23 2014-10-10 08:39:18

gaekwad

Server grease monkey

From: People's Republic of Cornwall

Registered: 2005-11-19

Posts: 4,137

GitHub

Re: Help cleaning up bulky/slow sql calls

+1 for the SSD explanation. I have a similar thing here when working on localhost Magento: on my spinning drive, it’s slow as heck (many files called per operation – really, it’s crazy), but on a Digital Ocean SSD-based droplet – even the $5 option – it’s way faster.

---

Textpattern CMS demo – tech updates

#24 2014-10-10 09:02:10

etc

Developer

Registered: 2010-11-11

Posts: 5,053

Website GitHub

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284653:

Here is my existing textpattern code snippet and because I am not a PHP/MYSQL denizen could you explain your code a bit more in plain english or textpatternish.

Ah, you want to output the books having the current Author_1 (say, custom_1) or Editor_1 (custom_11) as contributors? This should work:

create an article-type etc_search query (say, id=1) with {"OR":";"} as logical operators, and

{custom_1,custom_11} AND Section='Books' AND ID != <txp:page_url type="id" /> ORDER BY custom_4 DESC

as query string. Then add this to the individual article form:

<txp:etc_search_results id="1"  form="book_title" limit="40"
	query='<txp:custom_field name="Author_1" />;<txp:custom_field name="Author_2" />;<txp:custom_field name="Editor_1" />;<txp:custom_field name="another_contributor_field" />;...' />

It will internally transform it into

... WHERE ( (custom_1 LIKE '%Author_1%' OR custom_11 LIKE '%Author_1%')
OR (custom_1 LIKE '%Editor_1%' OR custom_11 LIKE '%Editor_1%') ... )
AND Section='Books' AND ID != current_ID ORDER BY custom_4 DESC LIMIT 40

and ignore empty contributor fields.

---

bloatware