wp-cli
Output single column from db query without formatting
Feature Request
- [X] Yes, I reviewed the contribution guidelines.
Describe your use case and the problem you are facing
The db query command currently formats the results in a table. While there is a --skip-column-names option (which doesn't show in the options when running help, only in the examples), there doesn't seem to be a way to disable the table formatting. While it may be possible to pass the results into a sed command to strip out the formatting, having it built-in without the extra steps may be handy.
Describe the solution you'd like
My use case was a query to get specific post IDs that I needed to pass to another command, ideally something like this:
wp db query --skip-column-names --format=csv "select ID from wp_4_posts where post_title like '%boston%' and post_type = 'hotel'"
This would give me just the post IDs in a list, which I could pipe to the paste command to turn them into a comma-delimited list, which I could pass to another command, ideally something like this, with a --column or --format flag.
wp elasticpress index --post-ids=$(wp db query --skip-column-names --column=ID "select ID from wp_4_posts where post_title like '%boston%' and post_type = 'hotel'" | paste -s -d, -)
Yes, that would prove useful indeed. Are you interested in producing a PR for this?
Yes, I'm interested. Not sure when I'll get to it, but I'd be glad to tackle this.
I was surprised just now that wp db query doesn't support the --format flag, since most other commands do. This would be a good addition.
There is mysql-shell https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html which is almost drop-in replacement for mysql, and has output-format parameter. But this complicates soo many things.
I ended up using --skip-column-names --batch which returns each result delimited by a newline.
Full example:
while IFS= read -r domain; do
echo "found $domain"
done < <(wp db query --skip-column-names --batch "select distinct(SUBSTRING_INDEX(SUBSTRING_INDEX(guid, '://', -1), '/', 1)) from wp_posts;")
found domain1.com
found domain2.com
Maybe --raw would be nice alias for --skip-column-names --batch ?
