smd_query: Talk to the database directly via SQL

whaleen · 2009-11-28 13:39:36

Thanks Uli for your suggestion. Double-single quotes around the ign tag do make it possible for me to then make ?thingy work in smd_queries. It’s strange to me that when I was using single quotes around that tag, that <txp:variable name="thingy" /> would print the value of that var onto that page but that it wouldn’t by using ?thingy in smd_query as mentioned. To quote your two examples and to explain better what is happening now:

Creating a variable this way:

Method 1. <txp:variable name="thingy" value='<txp:smd_var_get name="<txp:ign_user_info type="name" />-thingy" />' />

lets me use <txp:variable name="thingy" /> but not ?thingy

Method 2. <txp:variable name="thingy" value='<txp:smd_var_get name=''<txp:ign_user_info type="name" />-thingy'' />' />

lets me use either. I have not tested so see that the txp:if_variable tag can now use <txp:variable name="thingy" /> but I assume it will work now too. As I mentioned previously method 1 would not make the variable available for use with the if_variable tag.

My conclusion is that while method 1 does create the variable it is a weaker half-dead sorry little variable while method 2 creates a higher quality full-of-life more flexible variable. :) I’m satisfied with that explanation as silly as it is but wonder what makes it seem this way. Maybe there are varied outcomes with variable creation with txp:variable. Something for the devs to look at? Maybe just a wormhole.

Bloke · 2009-11-28 15:55:13

whaleen

OT Texplanation:

Method 1 fails because any tags in double quotes are not parsed; stuff in single quotes is. Since you are already using the single quotes to nest smd_var, when you nest another tag you need to escape the first single quote and use a second single quote to start the new tag. Hence the reason your/Uli’s Method 2 works.

In nested cases like these, to save your sanity, it’s usually easier to use txp:variable as a container to assign the value:

<txp:variable name="thingy"><txp:smd_var_get name='<txp:ign_user_info type="name" />-thingy' /></txp:variable>

One caveat:

<txp:variable name="higgs"><txp:category name="boson" /></txp:variable>

is not the same as:

<txp:variable name="higgs">
<txp:category name="boson" />
</txp:variable>

especially when it comes to testing if something is ‘empty’ using txp:if_variable or smd_if. The second one has newlines (aka spaces) in the value, which are treated as ‘has some content, albeit blank’.

whaleen · 2009-11-28 16:21:23

Thanks Stef. The container method makes it much easier to scan over and understand when writing. Too bad about the newlines’ whitespace as they make it much more easy to read but those are present a lot in my code so I’m glad to know it can be a suspect.

I’m still curious as to why I was still able to print a value (w/ txp:variable) in a limited scope using method 1.

Bloke · 2009-12-02 19:46:39

Next major release is available. v0.3 features:

A few minor bug fixes (thanks speeke)
strictfields attribute for allowing or disallowing spaces in ? vars
Direct pagination support including a special pageform attribute for displaying paging stats (limit, offset and pgonly now supported)
Host of new replacement vars, including page number, row number (on this page and from the start of the result set), total number of pages in result set, and start/end record numbers on each page
<txp:newer /> and <txp:older /> will step through the result pages. See Example 10 in the help for a pagination use case

Hopefully this will help those of you who have been forced to use embedded <txp:article> tags to simulate paging. It also means that custom queries can be paged, which is good news since <txp:article_custom> can’t be paginated; this opens up some rather neat opportunities.

Note that it’s not “true” paging in the sense that it doesn’t work like a conventional ‘cursor’ view of a subset of the returned records. Each time you visit the page, the whole query is executed but only the relevant portion of the results are shown depending on the page number. The good news is that you can specify a hard MySQL LIMIT and still page through the results by setting the smd_query limit attribute to a smaller value.

Anyway, see how you get on with it. As usual, I’m here for bug reports or support requests. If you have any cool queries that’ll help out others, please also post them here.

Last edited by Bloke (2009-12-02 19:54:00)

kostas45 · 2009-12-04 10:11:50

Bloke wrote:

<txp:newer /> and <txp:older /> will step through the result pages. See Example 10 in the help for a pagination use case

These tags seem not to work for me in Example 10.
They just print nothing. :-(

-Kostas

Bloke · 2009-12-04 10:14:58

kostas45 wrote:

[older/newer] seem not to work for me in Example 10.

What’s the rest of your smd_query/form? Is it the same as example 10? If so, do you have more than 10 users in your TXP system?

kostas45 · 2009-12-04 10:20:21

Stef, I am limiting to 5 and I get:

Page 1 of 4
Showing records 1 to 5 of 19

but Next 5 and Previous 5 do not print.

-Kostas

Bloke · 2009-12-04 10:50:54

kostas45 wrote:

Page 1 of 4 Showing records 1 to 5 of 19 but Next 5 and Previous 5 do not print.

Weird, it works for me, though I have just thought of an enhancement: some new replacement variables to help with navigation that indicate how many records are on the next/prev pages and what the next/prev page numbers are. That will allow you to put the older/newer tags in your pageform and have it automaitcally render the correct number of records , e.g. Next 5, Next 5, Next 5, Next 4 in your case. It will also allow you to build your own next/prev links more easily. I’ll add that when I get a moment.

You may need to watch out for this caveat. It may be affecting you here(?)

Also, if you put the smd_query and associated older/newer tags inside an article they may not show up because, by their nature, they are designed to be used on list pages. I tried one a minute ago in an article and, although it rendered the next/prev links, their destinations were to the section landing page and not the article itself. My proposed fix above will address that so you can test for the existence of the next page and display your own next/prev links if you wish to do something a little more esoteric inside an article context.

After that I don’t know what could be up with your implementation. It works for me so it’ll probably be something subtle like the fact you only have a few articles or the newer/older tags are being intercepted by TXP’s article tag. It could be a bug in the plugin or a silly assumption I’ve made. Is there any more info you can supply, like where the smd_query tag is being used (page, form, section, article?), your exact tag code and a rough indication of how many articles are in the section in which the smd_query appears (because it may have a bearing on the results here).

Last edited by Bloke (2009-12-04 10:51:09)

kostas45 · 2009-12-04 19:01:42

Still no luck. It seems to me I need to change the whole site structure, for the older/newer tags to work!
I just need to page some records anywhere. I should not care if I am in the correct section, or in a list, or whatever. Just page records :-P
Hey, is this smd_pagicle territory? I believe, it is!
I think I ‘ll wait for it to grow up and be history aware ;-)
Stef, thanks anyway!

-Kostas

Bloke · 2009-12-05 08:34:38

kostas45 wrote:

It seems to me I need to change the whole site structure, for the older/newer tags to work! I just need to page some records anywhere. I should not care if I am in the correct section, or in a list, or whatever.

If you’re using txp:older and txp:newer to page records, it does matter a great deal because those tags do specific things in TXP! I’ve allowed you to use them as a convenience to page records in this plugin. If you’re already using them for other things then you’ll have to build your own older/newer links because the plugin and your article list will clash.

The new version of the plugin (I altered it yesterday) allows you to more easily make your own links and separate the functionality of your smd_query from your article lists so you’re not tied to using the built-in older/newer tags. I’m just documenting it and then it’ll be released.

Bloke · 2009-12-05 10:30:25

OK, try the enhanced v0.4

Features:

New replacement tags {smd_prevpage}, {smd_nextpage}, {smd_rows_prev} and {smd_rows_next} for greater flexibility in your pageform
New attribute: pagevar to control which variable smd_query uses for paging (in order to break the link between article paging and smd_query when using older/newer)
Two new conditional tags <txp:smd_query_if_prev> and <txp:smd_query_if_next>, useful for building your own pagination controls (you can of course use smd_if if you prefer)
Removed pgonly because paging can now only be done inside the pageform

See how you get on with that one. Remember, <txp:older> and <txp:newer> can now only step through smd_query results if they are used inside the pageform. smd_query no longer clobbers the ‘external’ paging information so it is is returned to its original state as soon as the smd_query closing tag is encountered.

kostas45 · 2009-12-05 11:31:40

My results from local testing of docs examples:

Example 10: It works, except:
I am using a sticky form to do fulltext searching, so I also need my url (get) vars on all pages together with the pg var.
Should next/prev links take account of that?

Example 11:
Same as above, url vars are not carried over to pages, plus:
Next/prev link targets are somehow wrong.

-Kostas

Textpattern CMS

Textpattern CMS support forum

#145 2009-11-28 13:39:36

Re: smd_query: Talk to the database directly via SQL

#146 2009-11-28 15:55:13

Re: smd_query: Talk to the database directly via SQL

#147 2009-11-28 16:21:23

Re: smd_query: Talk to the database directly via SQL

#148 2009-12-02 19:46:39

Re: smd_query: Talk to the database directly via SQL

#149 2009-12-04 10:11:50

Re: smd_query: Talk to the database directly via SQL

#150 2009-12-04 10:14:58

Re: smd_query: Talk to the database directly via SQL

#151 2009-12-04 10:20:21

Re: smd_query: Talk to the database directly via SQL

#152 2009-12-04 10:50:54

Re: smd_query: Talk to the database directly via SQL

#153 2009-12-04 19:01:42

Re: smd_query: Talk to the database directly via SQL

#154 2009-12-05 08:34:38

Re: smd_query: Talk to the database directly via SQL

#155 2009-12-05 10:30:25

Re: smd_query: Talk to the database directly via SQL

#156 2009-12-05 11:31:40

Re: smd_query: Talk to the database directly via SQL

Board footer