User:TweetsFactsAndQueries/Queries/cocktail recipes
Jump to navigation
Jump to search
Try it!
Originally posted on Twitter.
#defaultView:ImageGrid
SELECT ?cocktail ?cocktailLabel (SAMPLE(?recipe) AS ?recipe) (SAMPLE(?image) AS ?image) WHERE {
{
SELECT ?cocktail ?cocktailLabel (GROUP_CONCAT(DISTINCT ?ingredient; separator=", ") AS ?ingredientsList) (GROUP_CONCAT(DISTINCT ?garnishLabel; separator=", ") AS ?garnishList) (COUNT(DISTINCT ?ingredient) + COUNT(DISTINCT ?garnishLabel) AS ?count) WHERE {
?cocktail wdt:P31/wdt:P279* wd:Q134768;
p:P186 ?materialStat.
MINUS { ?materialStat pq:P518/wdt:P279* wd:Q2453629. }
MINUS { ?materialStat ps:P186 wd:Q488463; pq:P366 wd:Q26876981. }
MINUS { ?materialStat pq:P366 wd:Q59541. }
?materialStat ps:P186/rdfs:label ?materialLabel.
FILTER(LANG(?materialLabel) = "en")
BIND(?materialLabel AS ?ingredientSolo)
OPTIONAL {
?materialStat ps:P186/rdfs:label ?materialLabel;
pq:P1114 ?quantity.
FILTER(LANG(?materialLabel) = "en")
BIND(CONCAT(STR(?quantity), " ", ?materialLabel) AS ?ingredientWithQuantity).
OPTIONAL {
?materialStat pq:P1114 ?quantity;
ps:P186/rdfs:label ?materialLabel;
pqv:P1114/wikibase:quantityUnit ?unit.
FILTER(LANG(?materialLabel) = "en")
FILTER(?unit != wd:Q199)
?unit rdfs:label ?unitLabel.
FILTER(LANG(?unitLabel) = "en")
BIND(CONCAT(STR(?quantity), " ", IF(?quantity = 1, ?unitLabel, CONCAT(?unitLabel, IF(STRENDS(?unitLabel, "sh"), "es", "s"))), " ", ?materialLabel) AS ?ingredientWithUnit)
}
}
BIND(COALESCE(?ingredientWithUnit, ?ingredientWithQuantity, ?ingredientSolo) AS ?ingredient)
OPTIONAL {
?cocktail p:P186 [ ps:P186 ?garnish; pq:P366 wd:Q59541 ].
?garnish rdfs:label ?garnishLabel.
FILTER(LANG(?garnishLabel) = "en")
}
?cocktail rdfs:label ?cocktailLabel.
FILTER(LANG(?cocktailLabel) = "en")
}
GROUP BY ?cocktail ?cocktailLabel
}
BIND(
IF(REGEX(?ingredientsList, ", .*,"),
REPLACE(?ingredientsList, "(.*), (.*)", "$1, and $2"),
REPLACE(?ingredientsList, "(.*), (.*)", "$1 and $2"))
AS ?ingredients)
BIND(
IF(REGEX(?garnishList, ", .*,"),
REPLACE(?garnishList, "(.*), (.*)", "$1, and $2"),
REPLACE(?garnishList, "(.*), (.*)", "$1 and $2"))
AS ?garnishes)
OPTIONAL {
?cocktail p:P186 [ ps:P186 ?glass; pq:P518/wdt:P279* wd:Q2453629 ].
?glass rdfs:label ?glassLabel.
FILTER(LANG(?glassLabel) = "en")
BIND(IF(REGEX(?glassLabel, "^[AEIOUaeiou]"), "an", "a") AS ?article)
BIND(CONCAT(" in ", ?article, " ", ?glassLabel) AS ?container)
BIND(", served" AS ?served)
}
OPTIONAL {
?cocktail p:P186 [ ps:P186 wd:Q488463; pq:P366 wd:Q26876981 ].
BIND(" on the rocks" AS ?onTheRocks)
BIND(", served" AS ?served)
}
OPTIONAL {
FILTER(STRLEN(STR(?garnishes)) > 1)
BIND(CONCAT(" with ", ?garnishes) AS ?garnish)
BIND(", served" AS ?served)
}
BIND(CONCAT(
?ingredients,
COALESCE(?served, ""),
COALESCE(?onTheRocks, ""),
COALESCE(?garnish, ""),
COALESCE(?container, ""))
AS ?recipe)
OPTIONAL { ?cocktail wdt:P18 ?image. }
}
GROUP BY ?cocktail ?cocktailLabel
ORDER BY DESC(MAX(?count))