smd_query: Talk to the database directly via SQL

Bloke · 2010-01-12 09:55:20

speeke wrote:

as soon as I place this in an article form, and call it using the <txp:article /> in post #167 above, the error here message fails.

Hmmm, clearly something’s amiss because it works as expected on my installation — even when the smd_query is put in the default form :-\

Back to first principles methinx:

Put your site in debugging mode
On your Page, make sure there’s a <txp:article /> tag that calls the default form
In your chosen Section that calls your Page, make sure there are some articles for one month and not for another
In your default article form, put:

<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 AND (SUBSTR(Posted,1,10)='?month' OR SUBSTR(Posted,1,7)='?month' OR SUBSTR(Posted,1,4)='?month') GROUP BY Posted DESC" limit="1" debug="2">
   Yay! Articles :-)
<txp:else />
   Booo, no articles :-(
</txp:smd_query>

and see what happens with various ?month values. That works on my test site.

I wonder — just a stab in the dark — if the reason you’re not seeing anything at all is because you’re getting a freak circular reference. If you’re using an article form to get the page, and inside your smd_query you’re showing an article with the same form, you’ll get a warning and no output. Debugging mode should show you any oddness.

EDIT: Second thoughts, it can’t be that because otherwise it’d fail when it finds a valid article too… *ponder*

EDIT 2: Just out of curiosity, what is all the smd_ and txp:variable paraphernalia for in post #168?

<txp:smd_switch item="?s">
  <txp:smd_case value="section-name">
   ...
  </txp:smd_case>
</txp:smd_switch>

is the same as <txp:if_section name="section-name"> isn’t it?

And I assume you must have adi_gps or something on the page, otherwise the <txp:if_variable name="month" value=""> will always fail…?

Last edited by Bloke (2010-01-12 10:08:04)

speeke · 2010-01-12 23:33:31

Bloke wrote:

Back to first principles methinx

All works as expected. However this example doesn’t appear to be testing for the error message scenario that I’m having problems with (ie when a user enters a different date in the title bar url for which no articles exist).

I wonder — just a stab in the dark — if the reason you’re not seeing anything at all is because you’re getting a freak circular reference.

I’ve had to include limit="1" in the article form tag that is included on the page that calls all this stuff.

<txp:article form="content-main_entries" limit="1" />

Could this be the culprit?

Just out of curiosity, what is all the smd_ and txp:variable paraphernalia for in post #168?

Good question :)

1. The test for <txp:if_variable name="month" value=""> is used because, in some sections, I need to sort articles by Title ASC or Posted ASC, for example, and so if the user wishes to sort by date I need to ensure the sort is Posted DESC instead.
2. The error message is designed to catch any dates that are manually typed in the title url, by the user, for which no articles exist.
3. I am using <txp:smd_switch item="?s"> instead of <txp:if_section name="section-name"> because I am testing for different sections within the same page template, and the switch option appeared more elegant. I omitted the other cases in this example for simplicity.

I assume you must have adi_gps or something on the page, otherwise the <txp:if_variable name="month" value=""> will always fail…?

Correct!

The thing is, my code works perfectly well when I place it directly in a page. But when I call it from the article form <txp:article form="content-main_entries" limit="1" /> the error message part of the equation fails. I could just put all my code back into the page, but I was trying to keep the template as uncluttered as possible.

BTW: Is there a problem with calling an article form from a page, and then having that article form call another article form? Perhaps this is where the circular reference is occurring? For example: in my page <txp:article form="content-main_entries" limit="1" /> calls the <txp:smd_switch item="?s"> code in the article form “content-main_entries”, and within this form the tag <txp:article_custom listform="article_list" id="{ID}" /> calls the article form “article_list”.

Last edited by speeke (2010-01-12 23:59:10)

Bloke · 2010-01-13 09:33:32

speeke wrote:

However this example doesn’t appear to be testing for the error message scenario that I’m having problems with (ie when a user enters a different date in the title bar url for which no articles exist).

Oh, perhaps I’ve misunderstood then. Sorry. For me, if I put site.com/section/article?month=2009-11 I see an article shown in the output of smd_query. But if I use site.com/section/article?month=2009-09 I see the ‘else’ message because I have no articles with Posted dates in that month. Same result if I mangle the date in any way; the error message appears. I wonder why it’s different on your setup? smd_query is supposed to understand else conditions correctly; you’re using plugin v0.4, right?

I’ve had to include limit="1" in the article form tag that is included on the page that calls all this stuff.

If you omit the limit, do you get any error messages in debugging/testing mode?

1… / 2… / 3…

OK, all makes sense.

when I call it from the article form <txp:article form="content-main_entries" limit="1" /> the error message part of the equation fails.

Totally odd, because there’s very little difference (logically) with the two setups.

Is there a problem with calling an article form from a page, and then having that article form call another article form?

Only if the two forms are the same (as far as I know). My understanding is that as long as the forms differ, TXP won’t complain.

in my page <txp:article form="content-main_entries" limit="1" /> calls the <txp:smd_switch item="?s"> code in the article form “content-main_entries”, and within this form the tag <txp:article_custom listform="article_list" id="{ID}" /> calls the article form “article_list”.

Aha! Hang on. article_custom has no listform attribute so it’ll look for the container. Since there’s no container, it’ll use the default form and is actually ignoring your article_list form. Bet that’s it.

The question is why, when you set your Admin->Prefs Production Status to Debugging or Testing, is your site not reporting these two failures:

incorrect use of a tag attribute for article_custom
the circular reference generated by the article tags

? Spooky.

speeke · 2010-01-13 10:51:20

Bloke wrote:

For me, if I put site.com/section/article?month=2009-11 I see an article shown in the output of smd_query. But if I use site.com/section/article?month=2009-09 I see the ‘else’ message because I have no articles with Posted dates in that month.

If that’s the case, then for some reason my set up is not producing the same results – using your “back to basics” code. Hmm.

If you omit the limit, do you get any error messages in debugging/testing mode?

No error messages as such. But I do get duplicates in articles listed on the page.

Aha! Hang on. article_custom has no listform attribute so it’ll look for the container. Since there’s no container, it’ll use the default form and is actually ignoring your article_list form. Bet that’s it.

Oh! These tags are tricky little buggers aren’t they? Bad news however – I altered listform to form to no avail – everything is working (and not working) as before.

I’m concerned that I’m now taking up your valuable time on something which is indeed a little spooky, unrelated to the workings of your wonderful plugin, and probably not beneficial to others. Any last suggestions, or should I give up, and simply place the code back in the page (instead of calling a form)?

Bloke · 2010-01-13 13:27:46

speeke wrote:

If that’s the case, then for some reason my set up is not producing the same results – using your “back to basics” code. Hmm.

Bizarre indeed. Trying to think what else could vary between setups that might affect this. Perhaps permlink mode? (doubt it, though)

I’m concerned that I’m now taking up your valuable time on something… unrelated to the workings of your wonderful plugin

Well, it may be the plugin interacting weirdly with your installation (perhaps even the PHP version?) so I’d ilke to nail it down in case it’s something that needs to be addressed. Plus I hate giving up on niggling things like these!

For the record, I have /section/title permlinks and in my page I have a straight <txp:article /> tag (no attributes) which calls my default form. After the <txp:body/> tag I have this code:

<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 AND (SUBSTR(Posted,1,10)='?month' OR SUBSTR(Posted,1,7)='?month' OR SUBSTR(Posted,1,4)='?month') GROUP BY Posted DESC" limit="1" debug="1">
   <txp:article_custom form="article_list" id="{ID}" wraptag="ul" />
<txp:else />
   error here
</txp:smd_query>

and in form article_list I have <li><txp:title /></li>.

With this URL: http://site.com/section/article?month=2009-11 I see an article title. With this one: http://site.com/section/article?month=2009-09 I see error here and no article.

One other thing that might be worth checking is to verify your tags are all paired properly and there are no dangling <txp:else /> tags or unclosed tags or something like that — check your Page, default form and any called forms, including ones that have been brought in with <txp:output_form />. Sometimes a pesky misplaced ‘/’ in a tag or an unclosed quote can wreak havoc in else clauses further down the page and cause the behaviour you’re witnessing. If necessary (and yeah, this is a pain), strip out all code except the bare minimum HTML and the tags under test to check if it all works. If it does, build up your page piece by piece until it stops working to track it down!

If you’re sure everything checks out in this regard, could you post the output of the smd_query SELECT statement in both article and non-article cases here please? debug="1" will show you that with the least amount of other crap on the page :-)

Last edited by Bloke (2010-01-13 13:28:59)

speeke · 2010-01-14 22:51:11

Bloke wrote:

I’d ilke to nail it down in case it’s something that needs to be addressed. Plus I hate giving up on niggling things like these!

I very much appreciate your zeal and commitment :-)

OK, just to be sure, my set up is:

1. I also have permlinks set to /section/title
2. I have only the <txp:article /> tag (no attributes) in the page that calls my default form
3. My default form is:

<txp:if_individual_article>
<h1 class="entry-title"><txp:permlink><txp:title /></txp:permlink></h1>
<txp:else />
<h3 class="entry-title"><txp:permlink><txp:title /></txp:permlink></h3>
</txp:if_individual_article>
<p class="published"><txp:posted /></p>
<div class="entry-content">
<txp:body />
<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 AND (SUBSTR(Posted,1,10)='?month' OR SUBSTR(Posted,1,7)='?month' OR SUBSTR(Posted,1,4)='?month') GROUP BY Posted DESC" limit="1" debug="1">
   <txp:article_custom form="article_list" id="{ID}" wraptag="ul" />
<txp:else />
   error here
</txp:smd_query>
</div>
<address class="vcard author">— <span class="fn"><txp:author /></span></address>
<p class="tags"><txp:category1 title="1" link="1" />, <txp:category2 title="1" link="1" /></p>
<txp:comments_invite wraptag="p" />
<div class="divider"><img src="<txp:site_url />images/1.gif" width="400" height="1" alt="---" title="" /></div>

4. and in form article_list I have only <li><txp:title /></li>.

And the results are:
a. URL: http://site.com/section/?month=2005-12 – I get the title
b. URL: http://site.com/section/?month=2005-11 – I get a completely blank page (no error here, no debug output, zip, nada)!

Note: I didn’t include the article name in my urls, like you did in your example. When I included the article name, to see what would happen, I got the title for the former, and error here for the latter test. Perhaps this is where the confusion is? I don’t want to sort by date within an individual article, rather, I need to sort a list of articles by date within a section. Is this the problem I wonder???

Last edited by speeke (2010-01-14 22:55:30)

Bloke · 2010-01-15 13:52:07

speeke wrote:

Perhaps this is where the confusion is? I don’t want to sort by date within an individual article, rather, I need to sort a list of articles by date within a section. Is this the problem I wonder???

Riiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiight!

Aha, it all becomes clear. Sorry for being dim. Stepping through what you’re asking TXP to do:

http://site.com/section lists all articles in that section — i.e. a standard TXP section list. It calls your default form for each article in the section
Each article will thus execute the smd_query; this is not what you want, but let’s sidestep it for now
Adding ?month=2005-12 tells TXP to filter the articles to show only those in the given year-month
Each article will thus run the smd_query. If you only have one article in this month, it’ll run the query once; two articles will cause it to be run twice, and so on
Again, changing the URL to ?month=2005-11 tells TXP to show only article in the given year-month
Since there are no articles in this month/year combination, your default form is never called and thus your smd_query is never called. Result: a blank page

To successfully run it you must therefore put the smd_query on your Page and not in your default form. Your page is always only called once. You may need to detect if the URL var exists before calling smd_query, but essentially that’ll sort you out. Sorry again for not spotting that earlier. D’oh!

speeke · 2010-01-15 19:38:56

Bloke wrote:

Riiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiight!

Okaydokay!

So it’s back to the original code in a page. I assume that I will also be able to remove the limit="1" part of the article tags now too, since there should be no duplication of article list results.

Thanks for the explanation. The logic is starting to sink in ;)

You may need to detect if the URL var exists before calling smd_query

Indeed I do.

Bloke · 2010-01-15 19:45:24

speeke wrote:

I assume that I will also be able to remove the limit="1"

I guess so. One way to find out for sure… ;-)

Bloke · 2010-01-17 11:56:29

Maintenance release v0.41 adds:

mode attribute so you can use the plugin as a single tag if you are performing INSERT/DELETE/UPDATE operations. This attribute is set to automatically detect the query type so you should never have to alter it
hashsize which governs how the plugin creates a unique reference for each smd_query tag. This is useful in situations where you want to put multiple smd_query tags on the same page and paginate each independently. Ties in with…
… {smd_query_unique_id} which allows you to put the unique ID the plugin has generated into your pageform

Take it for a spin and shout if anything breaks or you discover the meaning of life while using the plugin ;-)

speeke · 2010-01-18 04:45:52

<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 GROUP BY Posted DESC" limit="1" pageform="content-main_nextprev">
    <txp:article_custom form="article_list" id="{ID}" />
</txp:smd_query>

The above code does not appear to account for articles posted in the future. It still counts the future dated article in the pageform count, such that my pagination output, for example, is: Page 1 of 3 (Showing records 1 to 1 of 3) even though 1 of the 3 articles is dated in the future. The article list paginated page for the future dated article is blank apart from the pagination output.

I’ve added AND Posted <= CURDATE() to the query to overcome this. However, I was wondering if this is a bug???

Bloke · 2010-01-18 09:47:59

speeke wrote:

It still counts the future dated article in the pageform count

And here is where things get murky… your query does indeed just count Live articles, irrespective of date (you are correct that Posted < CURDATE() is the right way to do things).

Regarding the pagination, it’s actually displaying stuff correctly. There are three articles that matched your query, so as far as the plugin is concerned it is trying to step through them all. However, if (as I assume) you’re using txp:newer and txp:older to page through the results then you’re out of luck because they do not — and will not — take future articles into account. So you’ll get a blank page as you experienced.

Bug? Nope. Unexpected behaviour? Perhaps.

Maybe smd_horizon should be expanded to allow stepping through future article lists as well as individual articles. Hmmmm…

Textpattern CMS

Textpattern CMS support forum

#169 2010-01-12 09:55:20

Re: smd_query: Talk to the database directly via SQL

#170 2010-01-12 23:33:31

Re: smd_query: Talk to the database directly via SQL

#171 2010-01-13 09:33:32

Re: smd_query: Talk to the database directly via SQL

#172 2010-01-13 10:51:20

Re: smd_query: Talk to the database directly via SQL

#173 2010-01-13 13:27:46

Re: smd_query: Talk to the database directly via SQL

#174 2010-01-14 22:51:11

Re: smd_query: Talk to the database directly via SQL

#175 2010-01-15 13:52:07

Re: smd_query: Talk to the database directly via SQL

#176 2010-01-15 19:38:56

Re: smd_query: Talk to the database directly via SQL

#177 2010-01-15 19:45:24

Re: smd_query: Talk to the database directly via SQL

#178 2010-01-17 11:56:29

Re: smd_query: Talk to the database directly via SQL

#179 2010-01-18 04:45:52

Re: smd_query: Talk to the database directly via SQL

#180 2010-01-18 09:47:59

Re: smd_query: Talk to the database directly via SQL

Board footer