Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
need Help for article_custom and sql wildcard characters
Hello sql & Txp masters,
I work on a events website.
- I’m a section for events
- I’m a section for speaker
I use glz_custom_field for add a select multiple custom-field that return list of speaker.
And when I create a new Event article I select the speakers that participate to this event.
In database, glz_custom_field store the list of speakers with speaker article id separated with a pipe.
4|14|24In bottom of Individual event article, I display a list of speaker that participates on this event. No problem with this. In my Event article form I add
<txp:variable name="speakers-id"><txp:smd_wrap delim="-" param_delim="/" transform="replace//|/,/"><txp:custom_field name="speakers" /></txp:smd_wrap></txp:variable>
<txp:article_custom id='<txp:variable name="speakers-id" />' time="any" wraptag="ul" break="li">
<txp:permlink><txp:title /></txp:permlink>
</txp:article_custom>I replace | with , and display this list of id with article_custom.
BUT
In bottom of Individual speaker article I want to display a list of events in which the speaker participates.
I’m tested that
<txp:article_custom section="schedule" speakers='%<txp:article_id />%' time="any" wraptag="ul" break="li">
<txp:permlink><txp:title /></txp:permlink>
</txp:article_custom>And it is not quite accurate.
If I’m in Individual speaker article with id=4
This code return events with 4|8 but also 8|14 or 12|41 because 4 are present.
A idee someone ?
Offline
Re: need Help for article_custom and sql wildcard characters
The only way using the core tags, is by running multiple article_custom tags, where each specifies the pipe separators in the custom field value, e.g.
<txp:variable name="related_by_speaker">0</txp:variable>
<txp:article_custom section="schedule" speakers='<txp:article_id />' time="any">
<txp:variable name="related_by_speaker"><txp:variable name="related_by_speaker" />, <txp:article_id /></txp:variable>
</txp:article_custom>
<txp:article_custom section="schedule" speakers='%|<txp:article_id />|%' time="any">
<txp:variable name="related_by_speaker"><txp:variable name="related_by_speaker" />, <txp:article_id /></txp:variable>
</txp:article_custom>
<txp:article_custom section="schedule" speakers='<txp:article_id />|%' time="any">
<txp:variable name="related_by_speaker"><txp:variable name="related_by_speaker" />, <txp:article_id /></txp:variable>
</txp:article_custom>
<txp:article_custom section="schedule" speakers='%|<txp:article_id />' time="any">
<txp:variable name="related_by_speaker"><txp:variable name="related_by_speaker" />, <txp:article_id /></txp:variable>
</txp:article_custom>Which populates related_by_speaker variable with a list of related article IDs. But this is, well, resource hog. The alternative is to do the same by running a single query:
<txp:variable name="related_by_speaker"><txp:php>
$id = (int) parse('<txp:article_id />');
$cf = 'custom_1';
echo join(',', safe_column('ID', 'textpattern', "{$cf} = '{$id}' or {$cf} like '%|{$id}|%' or {$cf} like '{$id}|%' or {$cf} like '%|{$id}'"));
</txp:php></txp:variable>Downside is that this will break when 4.6.0 lands. When partial/value storage support lands, custom fields will no longer be stored in the same table.
Offline
Re: need Help for article_custom and sql wildcard characters
You can also post-filter the articles, though this is pluginish and suboptimal:
<txp:variable name="id" value='<txp:article_id />' />
<txp:article_custom section="schedule" speakers='%<txp:article_id />%' time="any" wraptag="ul" break="li">
<txp:etc_query data='{?||explode(|,<txp:custom_field name="speakers" />).in_array(<txp:variable name="id" />,$)}' argsep=",">
<txp:permlink><txp:title /></txp:permlink>
</txp:etc_query>
</txp:article_custom>Offline
Re: need Help for article_custom and sql wildcard characters
I’ve used Jukka’s rah_replace to strip the pipes:
<txp:if_custom_field name="locations">
<h2 class="text-center meta-head">Locations</h2>
<div class="meta-info">
<txp:rah_replace from="|" to="<br />">
<p><txp:custom_field name='locations' /></p>
</txp:rah_replace>
</div>
</txp:if_custom_field>Maybe this is another option for you.
Offline
Re: need Help for article_custom and sql wildcard characters
Slightly OT for devs: I have not observed much runtime difference between LIKE '%word%' and REGEXP 'word' on small non-indexed field like “Keywords”. Maybe, switching to REGEXP if custom field search pattern contains some special character could be an option?
Offline
Re: need Help for article_custom and sql wildcard characters
etc wrote:
Maybe, switching to
REGEXPif custom field search pattern contains some special character could be an option?
Regular expression operators in MySQL are not multi-byte safe, and also do not follow collation rules, making them, well, useless. The change would result in DDoS vulnerabilities (you can craft slow patterns, and queries that die in errors). User defined values should never be used as regular expression patterns.
Offline
Re: need Help for article_custom and sql wildcard characters
Gocom wrote:
User defined values should never be used as regular expression patterns.
I agree with you, but in this case “user” is the site admin. Switching to regexp could require php privileges, for example, and then caveat emptor.
Back to the problem, a way to cheat is sort attribute (untested and not 4.6-proof):
<txp:php>mysql_query("SET @pattern='[[:<:]]".parse('<txp:article_id />')."[[:>:]]'");</txp:php>
<txp:article_custom speakers='%<txp:article_id />%' sort="Posted desc HAVING custom_2 REGEXP(@pattern)" ... />Offline
Re: need Help for article_custom and sql wildcard characters
etc wrote:
I agree with you, but in this case “user” is the site admin
Doesn’t matter. Template features should be safe. Anything that isn’t, should be considered broken and patched.
Switching to regexp could require php privileges
Ugh.
Offline
Re: need Help for article_custom and sql wildcard characters
thank you for your answers,
If I summarize:
I cut into 4 queries
or
I made a single query using PHP or smd_query but it will not be compatible with 4.6
or
I made only one request and I made a post-processing to sort …
Mmmm … dilemma
Offline
Re: need Help for article_custom and sql wildcard characters
sacripant wrote:
I made a single query using PHP or smd_query but it will not be compatible with 4.6
I am not sure glz_custom_field will be compatible with 4.6 either. Neither <txp:php>, given it’s unsafe. :)
Offline
Offline