Extension talk:Cargo/Archive June to July 2018

From mediawiki.org

Where are errors defined for creating a new page with a property that is set to be unique?

I'm trying to create short id's that are more user friendly for use in a url. However, since the likelihood of a collision is more of a concern than with RFC compliant UUID's. I need to handle that error but I'm not sure how the error is expressed. How can I check, after creating a page with a potential collision, what the error array looks like?

I would say that the easiest and most foolproof way is to just check the value of that field and see if it's null or not - assuming it would only be null in the event of an error. Yaron Koren (talk) 02:29, 1 June 2018 (UTC)Reply

What would be the best way to keep track of the page's revision id such that I don't overwrite the page when my extension's local revision id is less than the page's current revision id. Magic words don't work.

I'd like to get the revision data when I do my initial cargo_query so I can reduce the overall number of api queries if possible. However, if it's not doable I can just make a revisions api request on page load.

Nice subject line. :) Is this a Cargo question? It sounds like a general MediaWiki question. Yaron Koren (talk) 16:40, 3 June 2018 (UTC)Reply
Well there is this $wgCargoPageDataColumns[] = 'modificationDate'; but I wasn't sure if that was the only approach towards determining potential conflicts. Also it says it's a date but is that actually a datetime?
Yes, it's actually a datetime. Yaron Koren (talk) 18:15, 4 June 2018 (UTC)Reply

Updating Cargo table's columns causes "no database exists" error

I'm using Cargo with the PageForms extension. After making my forms I decided to add some fields. After modifying the template associated with a Cargo table and recreating the table from the template's page, I can see the new fields' columns in the Cargo table in PhpMyAdmin. However, when I try to create a new page using a form referencing the template tied to the updated Cargo table, I get an "Error: No database table exists named x" message. Here's the odd part: When I go into PhpMyAdmin and manually change the Cargo main_table's name to "cargo__x", the form works! However, when I try to access the table using the Special:CargoTables page on my wiki, I get this error: "This table is registered, but does not exist! (not defined by any template)." How can I fix this error?

Could the problem be with the #cargo_store call in the template? Is the "_table" value set to the same thing in #cargo_declare and #cargo_store? If that's not the issue - could there be something unusual in the table name, like non-Latin characters, or punctuation? Yaron Koren (talk) 20:27, 3 June 2018 (UTC)Reply
Strangely enough it went away and works fine now. May have been a caching error.

Why does HOLDS implement RIGHT OUTER JOIN?

I'm utilizing the very helpful HOLDS command, but I've having confusion in why it uses RIGHT OUTER JOIN as opposed to LEFT OUTER JOIN because when I try to join with an empty array of IDs I would just expect it to still return the object from the first table.

I have a table Action_Items and I'm joining it with Updates because Action_Items can have n updates. However, if I do HOLDS with Action_Items.updates that is empty the overall result is an empty array as opposed to the originally queried Action_Items information.

UPDATE: I changed it to a LEFT OUTER JOIN by just editing CargoSQL.php and got the result I expected.

_pageData (and other tables) not storing values for a page

Hello, we're running into an issue with this page in that no Cargo tables are being stored for it, even _pageData. Tried null editing and even deleting and recreating the page with no change. Meanwhile other pages are working fine. Any ideas on how to resolve? --pcj (talk) 17:38, 6 June 2018 (UTC)Reply

Is it only that page for which the problem is happening? If I had to guess, I would guess that the massive amount of data in that page is causing the database connection to time out while the code tries to insert all the values. Or are there pages that are succeeding with even more data? Yaron Koren (talk) 01:32, 7 June 2018 (UTC)Reply
Haven't found any other page that has this problem (yet). We have a page where 793 rows are stored in a single table, which is much more then this particular page should create (which is (besides page data) 1 row in items, 1 row in skill gems, 40 rows in skill_levels, 4 in skill_stats_per_level, 1 row in item_purchase_costs, 1 row in item_sell_prices, 1 row in skill).
There are other pages with the same template that work as well.
On the other hand, it seems that it somehow stops somewhere in between, which would speak for the timeout thing, since it creates various rows and doesn't prune the old one, which is probably due to it not really finishing the process (so I assume anyway) see this for example
Not sure if it is related, but I've also noticed that the intermediate tables for list fields suffer from the duplication/data not being pruned problem that was fixed in cargo a while ago. Perhaps that is somehow related? OmegaK2 (talk) 11:41, 7 June 2018 (UTC)Reply
Alright. I didn't know about the problem with duplication in the helper/intermediate tables - that's very good to know. I don't think that's the cause of this issue, but who knows. What I would suggest is removing parts of that problem page until you can get it to save its data, to try to isolate what exactly is the cause of the error. I wouldn't be surprised if it's some character or other unusual value. Sorry to make you do that work, if you end up doing it - I can't think of a better approach. Yaron Koren (talk) 16:16, 7 June 2018 (UTC)Reply
It seems I might have figured the cause of this particular issue. There is an unique field for one of the tables - if I remove the data and then add it back in it works. I'm guessing the underlying problem here might be that it tries to insert into the table where a row with the unique already exists on the same page before deleting the old row and as a result gets some kind of error and stops all subsequent actions. I'm not sure why deleting/restoring the entire page didn't work though (I assume it should have nuked all the entries in tables for the pages). OmegaK2 (talk) 22:34, 7 June 2018 (UTC)Reply
Oh... that's a great find. It's not totally surprising: there was a fairly recent change in Cargo's data storage to make it one big DB transaction, and that's probably causing unrelated inserts to not get run when one insert runs into a "unique" problem. I'll have to look into that. Yaron Koren (talk) 23:21, 7 June 2018 (UTC)Reply

Issues querying fields with extended characters in the name?

We're running into some issues querying data from a table with fields with extended characters in the column name. For this table, querying for funções or "jogabilidade_de_heróis.descrição" fails while just "descrição" or jogabilidade_de_heróis.complexidade succeeds. The issue is perhaps more apparent when used on this page. Any thoughts on a resolution or workaround? --pcj (talk) 13:50, 7 June 2018 (UTC)Reply

In my case, I am doing Drilldown For a field named
Crée

[W38dVkIh1IgAAG28pl4AAAAH] /chat/Sp%C3%A9cial:Drilldown/Chansons Wikimedia\Rdbms\DBQueryError from line 1457 of /home/myUser/web_url/t/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: SELECT MIN(Crée) AS min_date,MAX(Crée) AS max_date FROM `cargo__Chansons` 
<br/>
Function: 
<br/>
Error: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\xC3\xA9e) AS min_date,MAX(Cr\xC3\xA9e) AS max_date FROM `cargo__Chansons`' at line 1 (mysql.web_url)

wpe 23.08.2018

I don't know - I believe whether or not non-ASCII characters in table and field names work may depend on the MySQL database configuration. There may be some way to fix this in the Cargo code; I don't know that either. For now, your best bet may be to avoid non-ASCII characters in table and field names. Yaron Koren (talk) 23:03, 23 August 2018 (UTC)Reply

Special:CargoExport JSON output has footer warning

Back with another weird error. I'm using Cargo's Special:CargoExport feature to get JSON output for a javascript widget. Ever since I deleted some tables via PhpMyAdmin, when I try to manually get the JSON output using variables in the URL, I get the JSON output, but then also this bit at the end:

<b>Warning</b>:  Cannot modify header information - headers already sent by (output started at /srv/data/web/vhosts/jollof.mariadavydenko.com/htdocs/wiki/extensions/Cargo/specials/CargoExport.php:405) in <b>/srv/data/web/vhosts/jollof.mariadavydenko.com/htdocs/wiki/includes/WebResponse.php</b> on line <b>46</b><br />

It's annoying because I've been using a $.getJSON to convert the output to JSON and that doesn't work anymore. I know I could use a javascript workaround (i.e. convert the weird output to a string and strip away everything from the <br /> onward) but I'd rather treat the cause and not the symptoms to avoid other weirdness. Any ideas?

Is there any other error message, or is that it? Usually that "headers already sent" error comes after something else has been printed on the screen. Yaron Koren (talk) 23:30, 7 June 2018 (UTC)Reply
No, the JSON prints perfectly right before in the [, { bracket formats. It's just these annoying break tags and error after that. Another update: The problem seems to be caused by the number of Cargo fields included in the export. When I remove one of the fields, the error described above goes away and the JSON loads smoothly. When I try to add back a field, it breaks down again.
Oh, that's very interesting. Is it one specific field, or does it happen if you remove any field? If it's the latter, how many fields are you querying? Yaron Koren (talk) 23:21, 8 June 2018 (UTC)Reply
It seems to be 4-5 max, unless I include a field that contains a lot of text -- then the length doesn't matter, I get the error regardless. The issue seems to be the length of the text inside the field, or possibly the special characters inside. Is there a way to increase this limit?
I'm guessing that this is somehow due to one or more specific values... is this on a public site? Yaron Koren (talk) 18:29, 10 June 2018 (UTC)Reply
Unfortunately not -- it's an internal site for a team. I made a test Cargo table to try to recreate the problem. With a string length of about 120 words (700 characters), I could only export one field at a time -- any more would produce the error. Could this be an export limitation from within MediaWiki itself?
Went with the workaround, using $.get and then stripping away the Cargo errors. If anyone finds a solution in the future I'd be grateful.

Parsing email addresses and dates

Hello all

I'm using Cargo tables to store users' email addresses, and dates. I'd like to parse these to show the email addresses and links, and to change the format of the dates from YYYY/MM/DD. Is this possible?

This is my query showing email addresses:

{{#cargo_query: tables=GigListings,MemberDetails |join on=GigListings.facilitator HOLDS MemberDetails.email |fields=email |order by=MemberDetails.full_name |format=list }}

and for the dates, it's simply this:

| '''Date''' | {{{date|}}}

Any help or pointers would be much appreciated! --Melat0nin (talk) 12:52, 10 June 2018 (UTC)Reply

For emails, how do you want to change their display? And for dates, are you talking about displaying them in a query, or just displaying the template parameter (as you have in your example)? Yaron Koren (talk) 18:03, 10 June 2018 (UTC)Reply
Thanks for the reply. I managed to implement what I was looking for using the CONCAT and DATE_FORMAT SQL commands. Now I'm trying to implement a recursive LEFT OUTER JOIN...--Melat0nin (talk) 13:52, 11 June 2018 (UTC)Reply

PostgreSQL and replacement tables

When the checkbox to use a replacement table is enabled:

I think there may be concurrency errors or attempts by Cargo to manually assign IDs over using sequences.

 Jun 12 06:31:02 db postgres[90423]: [7-1] ERROR:  duplicate key value violates unique constraint "cargo_tables_template_id_key"
 Jun 12 06:31:02 db postgres[90423]: [7-2] DETAIL:  Key (template_id)=(9908) already exists.

New __NEXT tables are created in the cargo database:

 public | cargo__ships__NEXT         | table | azurlane_wiki
 public | cargo__ships__NEXT___files | table | azurlane_wiki

But Cargo throws exceptions:

 [exception] [4ff8ae73bec7d8b9f2d2807b] /Special:CargoTables/ships?_replacement   MWException from line 196 of /var/www/azurlane.koumakan.jp/w/extensions/Cargo/includes/CargoUtils.php: Error: Table ships__NEXT not found.

The only way to fix this is to manually drop these __NEXT tables as Cargo is unable to do anything with the table in question. Trying to delete the table and recreate data without dropping these NEXT tables obviously fails.

Sorry for the delay. What version of Cargo are you running? I would try switching to the very latest code, if you're not running it already. Yaron Koren (talk) 18:04, 14 June 2018 (UTC)Reply

Cargo Admin User Class?

Would this make sense to have the extension create? To grant recreatecargodata and any other right that Cargo only gives to sysops currently. --RheingoldRiver (talk) 11:42, 13 June 2018 (UTC)Reply

I don't think it makes sense for MediaWiki extensions to define their own user groups, except in rare cases, but you could do it yourself on your wiki (or have the admins do it) by just adding something like the following to LocalSettings.php:
$wgGroupPermissions['cargoadmin']['recreatecargodata'] = true;
That by itself is enough to define a new user group in MediaWiki. Yaron Koren (talk) 16:15, 13 June 2018 (UTC)Reply

unique field not working?

I was trying to recreate a table on Gamepedia with a unique param, and instead of recreating it just got hung up on the spinning wheel indefinitely. I tried recreating on a sandbox wiki here, and same thing - regardless of how small we set the size it still won't create. You can check revision history to see things we did, basically every iteration without a unique param recreated no issue, but with the unique it failed with infinite loading circle. Do you know what might be going on? Thanks --RheingoldRiver (talk) 16:03, 13 June 2018 (UTC)Reply

It should be "size=", not "size:" - that might be the issue. Hopefully it works with a smaller field size... Yaron Koren (talk) 18:08, 14 June 2018 (UTC)Reply
Same thing is still happening. I tried (unique;size=10). --RheingoldRiver (talk) 23:02, 14 June 2018 (UTC)Reply
Okay, I just tried this out for myself, and now I see the issue. The problem is that the field in question, "UniqueLine", is of type "Text" - and the "unique" keyword doesn't work at the moment with "Text" fields, since they get stored as BLOBs, which have special handling for uniqueness. (I never thought to test it with "Text".) I guess there are three options: add support for "unique" with "Text" (which would involve just checking some initial substring of the value for uniqueness), ignore "unique" if it's a "Text" field, or display an error message. (The second two are more or less the same.) Any thoughts on this? Yaron Koren (talk) 03:33, 15 June 2018 (UTC)Reply
I think we need unique to work with fields of type "Text" in order to avoid duplication. The last of our SMW that isn't in Cargo yet is our (huge) database of game stats + (many) templates that query it, and I need to do queries with a bunch of fields with SUM and COUNT, so if there's dupes all of this will give the wrong results; and my current plan for setting a unique field is to do {{FULLPAGENAME}})_{{#var:N}}, where N increments with each line that's stored. If you can think of an alternative solution for this issue then I'd be happy to consider something else, but I haven't been able to think of any other option. --RheingoldRiver (talk) 11:19, 15 June 2018 (UTC)Reply
Ah, pcj reminded me that text and string aren't the same in Cargo as they are in SMW. I can probably use string instead --RheingoldRiver (talk) 11:48, 15 June 2018 (UTC)Reply
Alright - I think I'll add an error message for "unique" with "Text". Let me know if you decide you need "unique" to be able to work with "Text", though. By the way, "Text" and "String" in Cargo are modelled after their equivalents in SMW, so they should work more or less the same, though there are some differences in the implementation. Yaron Koren (talk) 13:25, 15 June 2018 (UTC)Reply
Some SMW version a couple years ago made string and text equivalent right? --RheingoldRiver (talk) 14:00, 15 June 2018 (UTC)Reply
That's true... they're stored the same way in the database now, although they're still handled differently in Page Forms and Semantic Drilldown (with "String" intended to represent shorter strings). So I guess it depends on how you look at it. Yaron Koren (talk) 14:21, 15 June 2018 (UTC)Reply

Table of Contents on Page Values page

I think I may have brought this up before but I don't remember any response to it, so apologies if you saw this already. Would it be possible to add a table of contents (ideally a flatlist or aligned right) to the "page values" page? We have some pages with a lot of tables/rows stored, and it would be a nice QOL change over using ctrl+F to navigate. Alternatively a system message that could be used to add a TOC if we want (if this possible to work). Thanks! --RheingoldRiver (talk) 11:26, 15 June 2018 (UTC)Reply

Yes, you did bring it up before - it's good to keep reminding me of stuff, so feel free to re-post things if I don't respond. You make a good point that this would be useful for pages with lots of rows; I just checked in an addition to add a TOC to this page. Yaron Koren (talk) 19:52, 18 June 2018 (UTC)Reply

Using Cargo data for reusing file data

We are using Cargo for storing featurd image data on pages. We would like than to load this data on tiles leading to these pages. It seems that we are missing somthing, because this information is not parsed. We are trying to make this work:

[[file:{{#cargo_query:tables=images|fields=image-name|where=images._pageName = "{{{target-page-name}}}"}}|250px|link={{{target-page-name}}}]]

This is what we have tried:

  1. Storing the data as "File" will not work with external image repo. Also it does not allow us to create the tile with the specific settings we need.
  2. Storing the data as "String" will not parse - we are getting the markup displayed as text (i.e. displaying: [[file:Query-image.jpg|other image settings]]
  3. Storing the data as "Wikitext" is parsed in a very strange manner and does not produce the intended behavior. We even tried storing it with "[[file:" appendix.

What can we do about it?

Also, is there's a way to use Cargo quering for with parserFunctions logic? i.e. - if we want to use a boolean variable in order to hide or show content like this:

{{#ifeq: {{#cargo_query:tables=sometable|fields=boolean-var|where=sometable._pageName = "{{{target-page-name}}}"}} | yes | show content | don't show }}

Wess (talk) 20:01, 16 June 2018 (UTC)Reply

It's possible that adding "|no html" to both #cargo_query calls will fix the problem. Yaron Koren (talk) 19:59, 18 June 2018 (UTC)Reply

Error giving a table an alias when one of the fields queried has type list

Here is the example. In the first query, I'm only querying fields that have Integer types. In the second query one of the fields has a list type and the query fails; the third query doesn't have any kind of join condition, just an alias, and still fails. The final query has no alias and queries the same fields as the 2nd and 3rd, but works fine because there is no alias. Do you know what's going on? --RheingoldRiver (talk) 05:06, 18 June 2018 (UTC)Reply

Well I guess I figured out a solution? It works fine if I add __full. Still this seems weird but no longer something that's in the way of the code I'm working on. --RheingoldRiver (talk) 10:13, 19 June 2018 (UTC)Reply

Adding an _isRedirect field to _pageData

Is it be possible to add an _isRedirect or similar field to the _pageData table to easily exclude redirect pages from queries? --92.217.24.175 11:39, 23 June 2018 (UTC)Reply

That's a very good idea - I just added a column called "_isRedirect" in to the code. Yaron Koren (talk) 16:34, 25 June 2018 (UTC)Reply
Thanks! --94.216.183.244 21:28, 25 June 2018 (UTC)Reply

Row separated by a comma

Hi, how to make a row separated by a comma. - 212.80.62.177 06:30, 25 June 2018 (UTC)Reply

I found. - 212.80.62.177 06:47, 25 June 2018 (UTC)Reply

Make `mw.ext.cargo.query()` raise Lua errors instead of exceptions

Currently the mw.ext.cargo.query() raises exceptions for most issues apart from missing arguments. There are two major problems with this behaviour:

  1. Exceptions are not catchable using Lua's pcall() function, making error handling impossible.
  2. Exceptions prevent the entire page from being saved. While this is usually fine, this can cause some major issues with the entire page not being displayed in some specific situations (e.g. a table/field name changing/being deleted, module changes, …)

These issues make modules exposing the mw.ext.cargo.query() function to users non-ideal. I've even noticed cases where such a query "bricked" an entire page to a degree where only deleting and restoring everything but the affected revision would fix it (I'm not sure how to reproduce this though).

Instead, the mw.ext.cargo.query() function should raise a Lua error(), which would solve those problems. --Litzsch (talk) 12:09, 27 June 2018 (UTC)Reply

This sounds like a good idea, though I don't think I have enough Scribunto/Lua knowledge to do it myself. Would it be possible for you to create a patch that fixes this problem? Yaron Koren (talk) 14:33, 27 June 2018 (UTC)Reply
I don't think there's a way to catch the exception in Lua, so you'd have to to it in PHP which I have zero experience in. From what I can see you can just wrap the actual function call using try catch and then throw a Scribunto_LuaError() (see here for how Extension:ParserFunctions does it). --Litzsch (talk) 17:13, 27 June 2018 (UTC)Reply
Here's some more examples: [1], [2], [3] --Litzsch (talk) 17:20, 27 June 2018 (UTC)Reply
Okay, I think I get it. Could you try editing the file CargoLua.library.php, in the main Cargo directory, and change these lines:
                $query = CargoSQLQuery::newFromValues( $tables, $fields, $where, $join,
                        $groupBy, $having, $orderBy, $limit, $offset );
                $rows = $query->run();
...to:
                try {
                        $query = CargoSQLQuery::newFromValues( $tables, $fields, $where, $join,
                                $groupBy, $having, $orderBy, $limit, $offset );
                        $rows = $query->run();
                } catch ( Exception $e ) {
                        throw new Scribunto_LuaError( $e->getMessage() );
                }
...and see if that fixes it? Yaron Koren (talk) 18:35, 27 June 2018 (UTC)Reply
Yes it works perfectly. Thank you so much for the quick fix :) Just one more small thing: The Scribunto_LuaError() function automatically adds "Lua error: " at the beginning and a full stop at the end of the error message, making Cargo's error message Lua error: Error: Table test not found... You might want to strip the "Error: " and the "." to avoid repetition. --92.217.25.96 19:41, 27 June 2018 (UTC)Reply
That's great to hear. I just checked in this fix. Getting rid of the double "Error" text is more complicated than it sounds, unfortunately, but I hope it gets done eventually. Yaron Koren (talk) 02:41, 28 June 2018 (UTC)Reply
Wouldn't something like this work:
$errorMessage = substr($e->getMessage(), 7, -1);
throw new Scribunto_LuaError( $errorMessage );
Or is the Error {message}. format not consistent? --Litzsch (talk) 12:16, 28 June 2018 (UTC)Reply
The main problem is that the string for "Error:" will not always be in English - some of these error messages are hardcoded, but others are translated. Yaron Koren (talk) 13:58, 28 June 2018 (UTC)Reply
Maybe using explode() would work?
$errorMessage = substr( $e->getMessage(), 0, -1 );
$errorMessage = explode( $errorMessage, ": " )[1];
throw new Scribunto_LuaError( $errorMessage );
That should work in all languages. --Litzsch (talk) 14:05, 28 June 2018 (UTC)Reply
Not necessarily - some languages might not use colons. Yaron Koren (talk) 14:17, 28 June 2018 (UTC)Reply
Ah damn :( Well still thanks for the fix itself the message is just a cosmetic thing anyways. --94.216.148.152 14:34, 28 June 2018 (UTC)Reply

Still problems with HOLDS when aliasing table names, also duplicates with HOLDS

So, the workaround of using __full that I posted last time I had this issue is now failing for two reasons:

  1. I actually want the not full field list in this particular query
  2. I'm running into the same issue in my WHERE condition when I'm trying to do an OR.

Here is the query I want to run:

{{#cargo_query:table=ScoreboardGame
|where=ScoreboardGame.Team0Bans HOLDS "LeBlanc" OR ScoreboardGame.Team1Bans HOLDS "LeBlanc"
|fields=ScoreboardGame.Team0Bans,ScoreboardGame.Team1Bans,ScoreboardGame.Team0Bans__full,ScoreboardGame.Team1Bans__full,ScoreboardGame.UniqueLine}}

(So the not __full fields are to see which team banned the champion in question.) But it will not work if I alias ScoreboardGame=SG and try to use that instead (and this is important in the case of wanting to self join a table).

Here is the above query on a page both with and without the alias.

Could you see if there's any possible fix for this?

Separately, I'm wondering why I'm getting duplication here - UniqueLine is forced to be unique. So I think this is another issue with HOLDS?

I would switch to using LIKE, but there are champions whose names are fully contained in other champions names (for example one called Vi and another called Viktor). Ok I'm using RLIKE as a workaround for now but I'd really like to use HOLDS for it so that I can know which field contains the value in question.

Thanks --RheingoldRiver (talk) 17:53, 30 June 2018 (UTC)Reply

Sorry about that - unfortunately, HOLDS parsing still doesn't work correctly when there's more than one HOLDS condition. But I think you can get around the problem by using the actual table names, even though that makes things a little awkward. In your case, I think you can change the relevant parts of the query to:
tables=ScoreboardGame, ScoreboardGame__Team0Bans, ScoreboardGame__Team1Bans
|join on=ScoreboardGame._ID=ScoreboardGame__Team0Bans._rowID, ScoreboardGame._ID=ScoreboardGame__Team1Bans._rowID
|where=ScoreboardGame__Team0Bans._value = "LeBlanc" OR ScoreboardGame__Team1Bans._value = "LeBlanc"
Please let me know if (a) that worked, and (b) you still see the duplication problem. Yaron Koren (talk) 13:39, 2 July 2018 (UTC)Reply
Sorry for delayed response. I'm not entirely sure what to put for the fields in this example, but I tried this as the full query:
{{#cargo_query:tables=ScoreboardGame, ScoreboardGame__Team0Bans, ScoreboardGame__Team1Bans
|join on=ScoreboardGame._ID=ScoreboardGame__Team0Bans._rowID, ScoreboardGame._ID=ScoreboardGame__Team1Bans._rowID
|where=ScoreboardGame__Team0Bans._value = "LeBlanc" OR ScoreboardGame__Team1Bans._value = "LeBlanc"
|fields=ScoreboardGame.Team0Bans__full,ScoreboardGame.Team1Bans__full,ScoreboardGame.UniqueLine}}

I'm still getting duplicates, you can see the result here. I was able to alias fields if I only queried the __full values, but I couldn't alias ScoreboardGame as SG and query SG.Team0Bans, only SG.Team0Bans__full. --RheingoldRiver (talk) 04:38, 5 July 2018 (UTC)Reply

Actually I'm not sure if the duplicates in this case are a bug even - it's not what I want but maybe it makes sense for them to be there. --RheingoldRiver (talk) 04:40, 5 July 2018 (UTC)Reply
Sorry, I should have included the corresponding "fields" value as well - it should be something like:
|fields=ScoreboardGame__Team0Bans._value=Team0Bans,ScoreboardGame__Team1Bans._value=Team1Bans,ScoreboardGame.Team0Bans__full,ScoreboardGame.Team1Bans__full,ScoreboardGame.UniqueLine}}
The duplication problem looks pretty bad... but I guess, one thing at a time. Yaron Koren (talk) 15:11, 5 July 2018 (UTC)Reply
That seems to work, yeah. It's a bit nicer that in the HOLDS version, it's either empty string/nil or the value, but that's not a big deal, I can just check if the strings are the same instead of if one is empty. Unfortunately...there are now WAY more duplicates present. Somehow there's 49 lines for this one game, which seems really weird - when it was 6 per it almost made sense if it was giving me one line per entry in one of the bans fields, but this seems totally random. Result is here. --RheingoldRiver (talk) 18:34, 5 July 2018 (UTC)Reply

How to structure Cargo Query for subset of possible fields

I'm working on the template for a Form for RunQuery. I have a single table with 5 possible fields, of which, I want to be able to query on 2 of the fields, or 3 of the fields etc... How do you implement a multiple WHERE to include fields that are filled in, but ignore fields that have no entries?

I've tried single cargo queries for each field - that yields separate tables for each field completed.

I've tried joining all into one cargo query with AND for each field. But that requires all fields to be completed or it gives a no result. May214 (talk) 11:05, 2 July 2018 (UTC)Reply

I haven't tried it, but you could potentially have a "where=" clause like "('{{{MyField|}}}' = '' OR MyField = '{{{MyField|}}}') AND ...". Yaron Koren (talk) 13:54, 2 July 2018 (UTC)Reply
Thanks, Yaron. I ended up using the #if parser inside the where clause, terminating each with AND. Then I ended the where clause with a _pageID > '0'. Seems to be working so far. Looks like this:
{{#cargo_query:
tables=Repertoire
|fields=_pageName=Repertoire, Level, _pageID
|where=
{{#if:
  {{{LevelLo|}}}
  |Level > '{{{LevelLo|}}}' AND
}}
{{#if:
   {{{LevelHi|}}}
   |Level < '{{{LevelHi|}}}' AND
}}
{{#if:
   {{{Period|}}}
   |Period = '{{{Period|}}}' AND
}}
{{#if:
   {{{Mode|}}}
   |Mode = '{{{Mode|}}}' AND
}}
{{#if:
   {{{Tempo|}}}
   |Tempo = '{{{Tempo|}}}' AND
}}
_pageID > '0'
|format=dynamic table
}}

May214 (talk) 15:03, 2 July 2018 (UTC)Reply

That works too. Yaron Koren (talk) 15:17, 2 July 2018 (UTC)Reply

Problem with some dates in Cargo tables

Hi,

I have a problem with some dates like 1967-01-01 wich appears to be registered as 1966-01-01 in the Cargo table. Some dates are registered as I expect, so I really don't know what I am missing.

Examples :

  • 1967-01-01 -> 1966-01-01
  • 1966-01-01 -> 1966-01-01
  • 1932-01-01 -> 1931-01-01
  • 1933-01-01 -> 1932-01-01
  • 1933-01-02 -> 1933-01-02


I'm using Mediawiki 1.30.0 and Cargo 1.4. I have the same behaviour on another wiki with the same versions.

Best regards, --Ludovic Strappazon (talk) 12:03, 2 July 2018 (UTC)Reply

Sorry about that problem - it's due to a known issue with date display in PHP, for some January 1 dates. I just checked in a fix for it. Yaron Koren (talk) 15:18, 2 July 2018 (UTC)Reply
Ok, thanks for your answer. Best regards. --Ludovic Strappazon (talk) 07:16, 3 July 2018 (UTC)Reply

Problems with Unique

Hi Yaron, two issues with unique.

1) If there is a conflict in one table due to a unique parameter, it will stop all tables below in the same page from storing any data, even though those tables don't have conflicts.

2) When recreating a table & blank editing to force repopulation, sometimes the page will think that there is a conflict when there actually isn't, I assume this is when the page would have saved a duplicate entry before but now isn't. Specifically, what I noticed is that I recreated a table with a field {{PAGENAME}}N, N a variable that increments per store. I recreated the table, and no values were storing to the table. Then in random debugging I changed the field to {{PAGENAME}}_N and suddenly everything was storing, presumably because it was no longer a duplicate of some "ghost" entry that it thought existed.

In the meantime I've re-recreated all 3 tables affected without the unique parameter. They are TournamentPlayers, TournamentResults, and TournamentRosters here, so pretty big tables.

Do you know what might be going on? The statistics data that we still have in SMW is going to be much bigger tables (250k+ rows per table across 3 tables) so I'm concerned about doing that move with these problems (particularly the second one).

Thanks --RheingoldRiver (talk) 21:16, 7 July 2018 (UTC)Reply

Hi - I'm not sure I understand. You've removed "unique" from #cargo_declare in these templates, but the number of rows in the generated tables is still much lower than it should be? If so, that sounds like a much bigger problem than these issues with "unique", no? Or did I misunderstand that? Also, when you recreate tables, I would recommend going with the "replacement table" option, unless there's a good reason not to. Yaron Koren (talk) 11:31, 9 July 2018 (UTC)Reply
No, the problem was only when unique param was there. I added the unique param, and then blank edited the page. On doing so, no values were storing from any page. I then changed the unique field slightly (so there was an underscore before the 2 strings were just concatenated with no separator), and everything showed up. I believe this is due to two separate and unrelated issues, the first of which is that a conflict with unique prohibits all tables on the page from storing data (or at least all tables below the conflict). The second I think is related to the problem with duplicates that unique is supposed to solve, somehow during recreation the table is getting confused about some "ghost" row that doesn't actually exist. So when I changed the values, there was no longer any conflict to this ghost row. But I need the data tables to actually work right now so I re-recreated without unique, and everything is fine again (but I need a group by in my queries to fend off against dupes, which was the reason I attempted to add unique).
RE:Replacement tables, I'll start doing that now the next time I have to recreate...I remembered there being some bugs with them so I was just in the habit of not using them. And I think that any bug that happens during recreation is likely to happen eventually anyway if the tables are just left alone, recreation just speeds up the process. But maybe I'll recreate with replacement, then blank edit, then put replacement live. --RheingoldRiver (talk) 14:35, 9 July 2018 (UTC)Reply
Any idea what might be causing these problems? (Particularly the "ghost duplicates" issue - unique conflicts causing the rest of the page to not update isn't really prohibitive to anything I need to do, just inconvenient) Right now I'm holding off on the rest of our SMW->Cargo migration, do you think it's realistic to wait for a fix for this to do that? --RheingoldRiver (talk) 19:54, 31 July 2018 (UTC)Reply
Sorry, what's the "ghost duplicates" issue? And are you seeing it for all tables, or just some tables? Yaron Koren (talk) 01:19, 1 August 2018 (UTC)Reply
The issue #2 that I reported above in this thread. It's only happened in some really big tables (50k+ rows). But the last set of tables I need to make will all be over 100k rows. --RheingoldRiver (talk) 05:13, 2 August 2018 (UTC)Reply
Oh, okay - I think I never fully understood that one. You don't need to do blank edits to force the data to re-populate, by the way. And why do you have a field with the value "{{PAGENAME}}N"? If you want an incrementing number, can't you just use the "_ID" field? Yaron Koren (talk) 13:46, 2 August 2018 (UTC)Reply
That field was supposed to be used to force the unique parameter for that row, not for querying - is there a better way to do that? And we've found on pretty much every wiki that uses Cargo with tables bigger than maybe 500 rows that we do need blank edits to repopulate, regardless of whether replacement table is used. So the problem was like this: I added the unique parameter and repopulated the table. As I was blank editing, with unique active, I found that some rows were refusing to store. This caused issue #1 to happen for the page. So then I went into the template that stores data, and I changed the store line from (PAGENAME)(N) to (PAGENAME)_N and now there were no more unique conflicts & everything displayed properly. If I changed it back to (PAGENAME)(N) then the conflict returned. So I ended up just removing the unique param from the table altogether and recreated, and now it works, but we still have the dupes issue on occasion so I need a groupBy in all my queries.
I believe that what is happening to cause this problem is that the table doesn't properly delete old entries when new ones are written or a blank edit happens, and so there's a unique conflict with one of these "ghost" lines that shouldn't actually exist. It seems like this is still the same issue that causes the dupes in the first place, but now the symptom is different because of the unique requirement. I can attempt to set something up on a sandbox wiki to cause the error to happen again but I'm not sure how successful I'll be since this kind of thing doesn't happen in smaller tables. --RheingoldRiver (talk) 16:47, 2 August 2018 (UTC)Reply
Are you sure that blank edits are needed? Tables get re-populated using the job queue - could it just be a matter of needing to wait longer until all the jobs are run? (Using a replacement table may help with that too, in that it won't be as pressing to get the table populated as quickly as possible.) Not that blank edits should lead to duplicate data, but maybe avoiding them will help with this situation at the moment. Yaron Koren (talk) 02:59, 3 August 2018 (UTC)Reply
Yeah 100% sure. It's probably the most-commonly asked question in our Cargo help channel from people who are new to it, why their tables aren't fully repopulating when they rebuild. And sometimes people will wait a couple days before asking about it. I agree that avoiding blank edits, or at least waiting a day or 2 after rebuilding to start blank editing would probably help this situation in the short term, but I'm really concerned that this is just a way to get persistent problems to show up quickly, and if I switched everything over now (which is going to involve replacing the syntax for every scoreboard to be more Lua-friendly, so it won't be easy to undo), we'd end up seeing similar errors eventually that there's nothing we could do about. --RheingoldRiver (talk) 03:33, 3 August 2018 (UTC)Reply
Now I'm wondering about that repopulation issue, because it sounds very odd. When "recreate data" is called, a job (i.e., a row in the "job" database table) is supposed to be created for every page that calls any template that declares or attaches to that table. Do you know if the issue is that the relevant jobs are not being created, or that they are created but never get run? It would be very important to find this out - maybe with the help of someone with access to the database, who can look at the "job" table. (There may be other ways to see the current status of the job queue, but if so I can't remember them now.) If it's just a matter of jobs not getting run, it may be that you just need a more "aggressive" job-running approach, like increasing the value of $wgJobRunRate or running runJobs.php as a regular cron job. Yaron Koren (talk) 12:56, 3 August 2018 (UTC)Reply
Sent a ticket about this, I'll let you know when I have an update! --RheingoldRiver (talk) 14:26, 3 August 2018 (UTC)Reply
Okay - this URL should, at least in theory, show the current size of the job queue. I would try recreating a large table (using the "replacement table" option), then seeing what happens to the queue. Yaron Koren (talk) 02:24, 7 August 2018 (UTC)Reply

Error: unclosed string literal.

code {{{название| {{#replace:{{#replace:{{PAGENAME}}|&#39 ;|'}}|&#34 ;|"}} }}}

where=_pageName='{{{1|}}}' → not working Доро Т'ибс

where=_pageName="{{{1|}}}" → working Доро Т'ибс

where=_pageName="{{{1|}}}" → not working Плакат "Разыскивается": Куска

How to use Apostrophe and Quotation mark? Previously, everything worked. - 212.80.62.177 17:16, 9 July 2018 (UTC)Reply

What's the difference between the 2nd and 3rd "where" lines? They look identical. Yaron Koren (talk) 19:52, 9 July 2018 (UTC)Reply
='Доро Т'ибс'
="Доро Т'ибс"
="Плакат "Разыскивается": Куска"
I can not use ' and " - 212.80.62.177 14:18, 10 July 2018 (UTC)Reply
Oh... now I get it. This problem has come up a few times before, like here. I would recommend what I recommended there - using single quotes, and "{{#replace:{{PAGENAME}}|'|\'}}". If you try that, please let me know if it worked. Yaron Koren (talk) 16:04, 10 July 2018 (UTC)Reply
Does not work. - 212.80.62.177 17:08, 10 July 2018 (UTC)Reply
Template:Item_icon code {{#cargo_query:table=Items|where=_pageName='{{{1|}}}'|group by=_pageName|fields=name|no html|limit=1|more results text=|default={{{1|}}}}}
Template:Item_infobox code | name = {{{название|{{#replace:{{PAGENAME}}|'|\'}}}}}
Does it fail for both apostrophes and quotation marks? It seems like it should work... Yaron Koren (talk) 04:05, 12 July 2018 (UTC)Reply
Apostrophes do not work {{item icon|Доро Т'ибс}} -> Error. 212.80.62.177 04:47, 12 July 2018 (UTC)Reply

Questions

  1. How to sort values in tables? Template:Recipe table How to sort by level? - (Oleksiy) 212.80.62.177 20:13, 13 July 2018 (UTC)Reply
  2. How many values can store in "where"?
    • work | where = ingredient1_item OR ingredient2_item = '{{{1|{{PAGENAME}}}}}'
    • does not work | where = ingredient1_item OR ingredient2_item OR ingredient3_item OR ingredient4_item = '{{{1|{{PAGENAME}}}}}'

I need it to search for all "ingredient1_item, ingredient2_item, ingredient3_item, ingredient4_item" and it only searches for the last value of "ingredient4_item" - (Oleksiy) 212.80.62.177 07:09, 14 July 2018 (UTC)Reply

You can sort using the "order by" parameter. As for the "where" value - I don't think either of those will work. Instead, you need to set it to something like "ingredient1_item = '{{{1|{{PAGENAME}}}}}' OR ingredient2_item = '{{{1|{{PAGENAME}}}}}' OR ...". Yaron Koren (talk) 15:55, 15 July 2018 (UTC)Reply
Thanks, all works fine. - (Oleksiy) 212.80.62.177 04:34, 16 July 2018 (UTC)Reply

Hello. I have another question. How to check the values in the "where". I need {{{1|}}} and {{{2|}}} to work together. Is there anything else besides the AND and OR? (Oleksiy) 212.80.62.177 11:00, 17 July 2018 (UTC)Reply

  • "AND" - if the value in {{{1|}}} or {{{2|}}} is empty, this causes an error
  • "OR" - does not fit
{{#cargo_query:
tables=Recipes,Items
|join on=Recipes._pageName=Items._pageName
|fields=Recipes._pageName,Recipes.quantity,Recipes.professions,Recipes.level,Recipes.ingredient1_quantity,Recipes.ingredient1_item,<br>Recipes.ingredient2_quantity,Recipes.ingredient2_item,Recipes.ingredient3_quantity,Recipes.ingredient3_item,<br>Recipes.ingredient4_quantity,Recipes.ingredient4_item,Recipes.ingredient5_quantity,Recipes.ingredient5_item,Recipes.name,Items.type
| where = Recipes.professions = '{{{профессия|}}}' {{#if:{{{профессия|}}}|AND|OR}} Items.type HOLDS LIKE '%{{{тип|}}}%'
| group by = Recipes.name
| link = none
| order by = Recipes.level
| limit = {{{limit|200}}}
| offset = {{{offset|0}}}
| default = No recipes found that match the constraints of the query.
}}
Is this for running in Special:RunQuery? If so, the section above discussing this issue may be helpful. Yaron Koren (talk) 14:12, 17 July 2018 (UTC)Reply
You can list all of the options in an array with like {{#if:{{{1|}}}|FieldName='{{{1}}}'}} as each member of the array and then do arrayunique to remove the empty entries and then arrayprint with the separator being ' AND ' to get around the issue of an extra trailing/leading AND or OR. --RheingoldRiver (talk) 23:57, 17 July 2018 (UTC)Reply

Joining the same table twice

Hi, in a message from archive of february 2017, I've read about joining the same table twice. Is there a documentation or an example of a such query ? I can't find how to write it in the cargo way.

Best regards

--Ludovic Strappazon (talk) 12:22, 20 July 2018 (UTC)Reply

Something like this should work, I think: {{#cargo_query:tables=MyTable=TableAlias1,MyTable=TableAlias2 |join on=TableAlias1.FriendID=TableAlias2.MainID |fields=... |where=... }}. Yaron Koren (talk) 21:26, 20 July 2018 (UTC)Reply

Finding and displaying values in table 1 which are not in table 2 (left join?)

I'm trying (and failing) to display a list of values (members) from a table that have not been selected in other fields. I have a list of members who have signed up to various roles (A, B, C, D) for a particular Event (this is a template). The event table is like this: `Events|roleA=|roleB=|roleC=|roleD|no_response=` and the members can choose their role in the interface according to a list populated from a separate members table: `Member_details|full_name=|phone=` (I've used PageForms to do that).

Whenever an Event page is created, Events.no_response is pre-populated with the whole list of members (from Member_details.full_name). I want to remove (at least in the view, using a Cargo query) entries displayed from Events.no_response as members sign up to the roles A, B, C, and D. This means multiple joins (I think), between Event.no_response and each of Events.roleA, Events.roleB, Events.roleC, and Events.roleD, as well as a join with Member_details.full_name to get the actual name values.

Is this possible with cargo?

Any help would be much appreciated :) I've gone round in circles with experimentation and reading the docs and Talk pages, but to no avail :'(

Melat0nin (talk) 13:20, 22 July 2018 (UTC)Reply

That's very tricky. I can't think of a solution that uses just SQL (or Cargo's implementation of SQL). The only solution I can think of its to use the Scribunto extension - once you install it, you can create a Lua module that calls a Cargo query, then does the necessary processing on the results and displays the values as a table. It's not as bad as it sounds - Scribunto/Lua makes a lot of things easier to do, and it may be useful for handling other queries as well. Yaron Koren (talk) 20:39, 23 July 2018 (UTC)Reply

Follow-up on random html spam from cargo queries sometimes

I'm suspecting that this might be only happening when a wiki has both Cargo and PageForms enabled. Moving code to modules seems to always fix the issue (it's only when |format=template) so it's not a big deal for me personally but maybe this might help to figure out what's causing it? Though whether the page with the error has a form or not seems to not matter. The reason I started to think this might be the case is that I had a user report that the error didn't go away when he blank edited, but it did go away when he blank edited with form. Hope this helps somehow --RheingoldRiver (talk) 18:13, 22 July 2018 (UTC)Reply

Ah, never mind, the dota 2 wiki is encountering the bug without PF installed. --RheingoldRiver (talk) 18:56, 22 July 2018 (UTC)Reply
Sorry, what was the previous discussion you're referring to? Yaron Koren (talk) 20:28, 23 July 2018 (UTC)Reply
Here and here. --RheingoldRiver (talk) 13:12, 24 July 2018 (UTC)Reply
I can't remember if we reported it here in another discussion (I thought we did but can't find now), but the issue is basically that if |format=template, the Cargo will sometimes show as the HTML code instead of the actual template. Blank editing fixes usually. It happens on a bunch of wikis and the only workaround we know to make it never happen is to use modules instead of templates. We don't have ConfirmEdit. --RheingoldRiver (talk) 13:20, 24 July 2018 (UTC)Reply