User:TweetsFactsAndQueries/Queries/cocktail recipes

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))

Try it!