Help talk:Selecting pages

From semantic-mediawiki.org
Jump to: navigation, search
Please note

Talk pages on this wiki should primarily be used to address possible mistakes as well as missing and superseded information in the documentation.
In case you are seeking support concerning individual questions, please have a look at this page.
The SMW user mailing list is always a good idea for seeking help.

Please note

Archive

Case insensitive query possible?

I have a search RunQuery that I would like to be case-insensitive. Is this possible? It seems the Like function used to be case insensitive, but isn't anymore. My query looks like this:

{{#ask:{{#if:{{{City|}}}|[[City::{{{City|}}}]]}}
|format=table
}}
D3mon187 (talk)17:56, 5 November 2012

I haven't found any configuration settings or query settings to make a query case insensitive. I think you have to do a feature request in the mailing lists.

Yury Katkov (talk)12:52, 10 November 2012
 

Check your database collation.

Alex Mashintalk12:56, 17 November 2012

I was thinking about changing the collation, but I know very little about mysql. In phpmyadmin I found a table called smw_ids, but not sure if this is where to make the change. Clicking on structure, there was no collation shown on any of the rows. I tried changing the title and sort_key to utf8-general-ci, but it still didn't show anything set for collation in these rows. Anyone know where/how to make the change? Thanks

D3mon187 (talk)21:55, 19 November 2012

This is a long-standing feature request, but yes, the answer tends to be "change your collation". Unfortunately, no one has been able to tell how to do so in a safe way (e.g. in phpmyadmin).

Cavila (talk)19:42, 30 April 2013

I've managed to get case insensitive ask queries in MW 1.21.1, SMW 1.8.0.5, by setting the character set and collation, and changing 3 columns from varbinary to varchar.

  1. During the database step in the mediawiki installation, choose UTF8 as the database character set. (If binary was chosen the DB needs to be recreated and pages migrated). Note that issues have been noted by other users with UTF8 and some languages, so test this thoroughly if using a language containing special characters.
  2. Backup database using mysqldump
  3. Open MySQL Workbench as the root (or other authorised) user
  4. Run the following script:
USE [Your_Wiki_DB];
 
/* The following show the character set (expecting utf8) and the collation (expecting utf8_general_ci) of the database, respectively.  
 * Uncomment and run against the relevant database to show the default settings. 
 * Note that changing these does not affect existing tables. 
 */
--show variables like "character_set_database";
--show variables like "collation_database";
 
/* The following alter the relevant columns in the SMW tables to get case insensitive searching working */
-- smw_title
ALTER TABLE [Your_Wiki_DB].smw_object_ids CHANGE smw_title smw_title_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_object_ids ADD smw_title VARCHAR(255) AFTER smw_namespace;
UPDATE [Your_Wiki_DB].smw_object_ids SET smw_title = CAST(smw_title_bak AS CHAR) ;
 
-- smw_sort_key
ALTER TABLE [Your_Wiki_DB].smw_object_ids CHANGE smw_sortkey smw_sortkey_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_object_ids ADD smw_sortkey VARCHAR(255) AFTER smw_subobject;
UPDATE [Your_Wiki_DB].smw_object_ids SET smw_sortkey = CAST(smw_sortkey_bak AS CHAR) ;
 
-- smw_di_blob
ALTER TABLE [Your_Wiki_DB].smw_di_blob CHANGE o_hash o_hash_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_di_blob ADD o_hash VARCHAR(255) AFTER o_blob;
UPDATE [Your_Wiki_DB].smw_di_blob SET o_hash = CAST(o_hash_bak AS CHAR) ;
COMMIT;
5. Case insensitive searching should now work

Note that you may get an error with the UPDATE statements if your preferences in MySQL Workbench are set with "Safe Update" on. This can be turned off by going to Edit>Preferences...>SQL Queries tab. Then uncheck "Safe Updates"... and reconnect to the DB.

Renae (talk)01:15, 10 October 2013

Note that the SQL table changes will be reverted if you upgrade your SMW version, so would need to be reapplied. Painful I know, but for us case insensitivity is mandatory.

Renae (talk)23:09, 29 October 2013
 
 
 
 

You can try query like as workaround.. It won't work for vAlUe, but might help to solve it.

Exbe (talk)23:06, 3 April 2014
 

How can I sort a table for current's date is displayed?

I tried putting this:

{{#ask: [[testdate::current]]
| ?testdate
| ?testdate#SORTKEY |+limit=1
}}
<pre>
Juan Cristobal Viscarra (talk)06:13, 26 March 2014

How does one "SUM" and "GROUP BY"?

Hi all,

I'm trying to select a set of data and do a "SUM" and "GROUP BY" on the result.

I have the following ask:

{{#ask:[[Category:Knowledge Transfer Log]]
|?Date
|?Total
|format=broadtable
|sort=Date
}}

Which results in this:

                Date            Total 
Item 0004 	29 April 2013 	5 	
Item 0005 	6 May 2013 	2 	
Item 0006 	10 May 2013 	3 	
Item 0007 	14 May 2013 	16 	
Item 0003 	14 May 2013 	17 	
Item 0008 	15 May 2013 	10 	

As you can see, Item 7 and Item 3 are both on the 14th May, however I'd like one record, summed, with a total of 33 instead of two separate records.

I've read up about the Sum format but that adds all rows together. I've also seen this trick of getting unique values, but it won't work with #ask.

Any help would be greatly appreciated.

IronSwalt (talk)10:10, 19 May 2013

Hi,

Has no-one solved this problem?

Cheers Walter

IronSwalt (talk)15:23, 8 August 2013
 

Grouping ORs with ANDs in arrays

Wow, been spending all day trying to get this working correctly. I have a SemanticForms RunQuery with a form entry that asks for a location. I'd like for the ability to put in "Texas" or "Austin" or "Austin, Texas". The first two are easy, but having the entry work with an array of "Austin, Texas" is proving a nightmare. If I manually type in

{{#ask:[[City::Austin||Texas]] [[State::Austin||Texas]]}}

I get what I'm looking for. Austin is matched with City, Texas is matched with State, and only Austin locations are shown. If I try to use an array or variable like

{{#ask: [[City::{{#arraymap:{{{Entry|}}}|,|x|x|<no*wiki>||</no*wiki>}}]][[State::{{#arraymap:{{{Entry|}}}|,|x|x|<no*wiki>||</no*wiki>}}]]}}

I get an Error: the value was not understood. I feel like I've tried everything. I've even tried to do a vardefine before the ask, and then ask on the var. Any ideas on how I can get this to work? Thanks

D3mon187 (talk)23:39, 16 November 2012

It's probably the double pipes that don't work, even when enclosed within nowiki tags (btw, why the asterisk?). I'm not sure if I understand you correctly, but is the intended query equal to the following one?

{{#ask: [[City::Austin]] OR [[City::Texas]] [[State::Austin]] OR [[State::Texas]]
}}

In that case, this should do the trick, right?

{{#ask: {{#arraymap:{{{Entry|}}}|,|x|[[City::x]]|OR}} {{#arraymap:{{{Entry|}}}|,|x|[[State::x]]|OR}} 
}}
Cavila (talk)19:28, 30 April 2013

Define a template {{!}} as | and try to use the template as the separator in {{#arraymap:}}: {{!}}{{!}}.

Alex Mashintalk19:55, 30 April 2013
 
 

How to exclude the current page from the results of a query on the page

For specific are use cases it might be desirable to exclude exactly the one page that contains the query from the set of results of this query. This is currently not a feature of SMW but there are workarounds, for example:

  1. Install Extension:Semantic Extra Special Properties
  2. When following the installation instructions make sure you add the special property for the revision ID, for example by adding the following to LocalSettings.php:
    $sespSpecialProperties[] = '_REVID';// Add property with current revision ID
  3. Wait until Special:SMWAdmin is done
  4. Add [[Revision ID::!{{REVISIONID}}]] to the query string of the inline query
Eric (talk)02:57, 27 February 2013

Filtering deleted pages seems not to work with SQLStore3 as well

Similar to the problems already mentioned with the old store, I ran in problems with deleted pages showing up as result with SMW1.8 on MW 1.20 using SQLStore3. I consider redirects to be part of the problem as well. As there is no bug referenced in the other thread on this page: Has the problem really been properly adressed and solved yet?

Regards

Argi

Argi (talk)10:29, 27 February 2013

See bug 42896 which is still open.

[[kgh]] (talk)02:13, 28 February 2013
 

Selecting pages that have the same value set for a property

On the page for a movie (directed by a director) I would like to display a list of other movies directed by the same director. I.e. other pages in the category:Movies that have the same value set for the property:Directed by.

{{#ask: [[Directed by::{{#show: {{PAGENAME}}| ?Directed by}}]] [[Category:Movies]] |limit=10 |format=datatable }}

The above example produces a datatable with other movies directed by the same director, but it also outputs a warning (caused by the |-sign probably... WARNING: part "" of query not understood, may cause unexpected results...). Is there a better way for doing this?

Regards, Martin

Martinwiss (talk)23:33, 8 October 2012

Try ?Directed by#-.

But I think that querying itself is a bad idea. Better use [[Directed by::{{{Whatever_template_parametre_for_director}}}]].

Alex Mashintalk03:45, 9 October 2012

Thanks

Martinwiss (talk)17:08, 13 October 2012
 

What do you mean by {{{Whatever_template_parametre_for_director}}}. Indeed, I have the same request but I don't know how to do.

I have a public proposal linked to Themes. For example, a proposal page :"Give an house to all people" with links to Themes "Human Rights" and "Economy" (property = Proposal-Themes) and I want to see all the other proposals related to the same Themes (in this case "Human Rights" and "Economy").

When I try to list with the primary "ask" as describe by Martinwiss, I get parsing errors like :

The part "|Human Rights" of the query was not understood. Results might not be as expected. The part "," of the query was not understood. Results might not be as expected. The part "|Economy" of the query was not understood. Results might not be as expected. The part "," of the query was not understood. Results might not be as expected. The part "]]" of the query was not understood. Results might not be as expected.

Pbj (talk)12:42, 2 February 2013

Of course, you have to process the parametre before passing it to a query, if it is a list or contains wiki markup. I do it with regular expressions.

Alex Mashintalk16:40, 2 February 2013
 
 
 

Filtering based on the titles of the page

I have a number of pages whose titles are dates, and I would like to filter them to receive pages within a certain date range. The pages don't have any specific property set that is equal to the date in the title. Is there a way to do this with the SMW query language?

Sagittarian (talk)02:36, 25 January 2013

No there isn't.

Jan "Saruman!" S. (talk)07:41, 25 January 2013

Thanks for the reply. I've figured out that I could create a special property along the lines of the SemanticExtraSpecialProperties extension.

Sagittarian (talk)09:24, 25 January 2013