Update publication status of IO and descendants with raw SQL and ES' _update_by_query

update_publication_status.md

Update pub. status in the database:

UPDATE status
SET status.status_id = 159
WHERE status.type_id = 158
AND status.object_id IN (
  SELECT des.id
  FROM information_object io
  JOIN information_object des
    ON des.lft >= io.lft AND des.lft < io.rgt
  JOIN slug
    ON io.id = slug.object_id
  WHERE slug.slug = 'target-slug'
);

Use the top-level description slug and set the status_id to 159 for draft or 160 for published.

Get the top-level id (needed for the Elasticsearch update):

SELECT object_id FROM slug WHERE slug = 'target-slug';

E.g.: 12345

Update ES documents:

curl -XPOST "http://localhost:9200/atom/QubitInformationObject/_update_by_query" -d'
{
  "query": {
    "bool" : {
      "should" : [
        { "term" : { "ancestors" :  12345} },
        { "term" : { "_id" : 12345 } }
      ]
    }
  },
  "script": {
    "source": "ctx._source.publicationStatusId = 159",
    "lang": "painless"
  }
}'

See the ES docs for more info about the response.