Page MenuHomePhabricator

Querying data in cargo with the "HOLDS" command combining with "AND" clause
Closed, ResolvedPublic

Description

Hi,
How can I modify the "HOLDS" command in this example to use an "AND" clause?
Example what I'm trying to achieve (lets pretend the Authors wrote one book together and Authors is decleared as a List of String):

{{#cargo_query:
tables=Books
|fields=_pageName=Book
|where=Authors HOLDS 'Leo Tolstoy' AND Authors HOLDS 'Dostoevsky'
}}

It works fine with 'OR' but sadly no results for 'AND'.
MediaWiki 1.32.1
Cargo 2.1.1

Event Timeline

Grombol triaged this task as Medium priority.May 14 2019, 10:44 AM

The HOLDS command only works fine for one author. If you use the HOLDS query as you say with an OR, and a book is authored both by 'Leo Tolstoy' and 'Dostoevsky', then that book will appear twice in the query resutls.

For the example that you give the following SQL query is being executed:

SELECT  `_pageName` AS `Book` FROM `cargo__Books` LEFT OUTER JOIN `cargo__Books__Authors` ON ((`cargo__Books`.`_ID`=`cargo__Books__Authors`.`_rowID`))   WHERE `cargo__Books__Authors`.`_value`="Leo Tolstoy" AND `cargo_Books__Authors`.`_value`="Dostoevsky";

If you know how relational databases and SQL work, you can see that the results you are getting are the expected. Now, a SQL query that gives you the desired results of a publication having those 2 authors would be much more complicated. I think that for that you would need to make a Join on cargo__Books__Authors with itself, and then another join with cargo__Books to obtain the desired fields of the books. Therefore, i think this cannot be implemented in cargo, it is too complicated.

An easy workaround which will give you the desired results is to make a query with a where clause using the INSTR SQL function, i.e.:

{{#cargo_query:
tables=Books
|fields=_pageName=Book
|where=INSTR(Authors__full, 'Leo Tolstoy') AND INSTR(Authors__full, 'Dostoevsky')
}}

The INSTR functions returns the position of the first occurrence of a substring, if there is no occurrence it returns 0. You can see the doc for the MySQL function in https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_instr. In order to use this function in cargo you must enable it explicitly, as explained in https://www.mediawiki.org/wiki/Extension:Cargo/Querying_data#Using_SQL_functions. Maybe there are another ways to do it, but I thinks this is the easiest one.

I tried to make the query in cargo (without the instr function), just for curiosity, and as I said before it can be done with a 2 join query. For the example of books it would be like this:

{{#cargo_query:
tables=Books, Books__Authors=authorA, Books__Authors=authorB
|fields=Books._pageName, Books.Authors__full
|join on=Books._ID=authorA._rowID, Books._ID=authorB._rowID
|where=authorA._value='Leo Tolstoy' AND authorB._value='Dostoevsky'
}}

Note that this query only works for the AND case, not for the OR. For the OR case the following query works:

{{#cargo_query:
tables=Books, Books__Authors=authorA
|fields=Books._pageName,Books.Authors__full
|join on=Books._ID=authorA._rowID
|where=authorA._value='Leo Tolstoy' OR authorA._value='Dostoevsky'
|group by=Books._pageName
}}

In this case you must group by Books._pageName because as I said in the first response there may be duplicates in the query, and as explained in the cargo documentation (https://www.mediawiki.org/wiki/Extension:Cargo/Querying_data), the SQL command "DISTINCT" does not work in Cargo. Maybe using group by with the HOLDS command as in the example you posted also works for the OR case, but take into account that the group by clause is important or you may get duplicates.

I hope it helps. I still think that modifying the HOLDS command to consider such cases may be too complicated.

Thank you very much!
Not only for the cool ways how to deal with the problem, but also for a way to understand what the problem was.
I'm really appreciate your help!

Can I add your explanation here? Or maybe you want to do it yourself? I think it will help a lot of folks out there.

Grombol claimed this task.

You are welcome 。^‿^。, you can use my explanations as you wish.