Sorting custom post type columns with external data and without meta values

Question

I created a custom post type 'Book', for which data is stored in an external database (like ISBN, author etc). The WordPress database contains the ISBN per custom post, which connects the correct data to the custom post via a custom database connection.

The ISBN number is stored in the 'content' field of the custom post. The single-book template extracts the ISBN from the post content, and then loads in the additional info based on the ISBN from the external database.

In the backend, on the 'All Books' page, I want to use sortable columns (for instance, sort on author name). I know that sorting for custom post types works fine with meta values, however since the data of the custom post type is stored in a separate database, these meta values are nonexistant. Is there any way I can sort the posts using the data from the external database (i.e. by not using meta values which would contain duplicate data)?

As a side note, I'm creating this from scratch and plugins aren't the solution in this particular case.

Answer 1

If I understand it right your workflow is that you query posts (implicitly via normal WP mechanics), then display data from external database for them, but want to sort based on external data.

This isn't trivial (without mirroring you don't want to do), since WP relies primarily on SQL over its own data for sorting.

One of the ways you could implement it (assuming parity of entries data with external database) is to use that database for sorting:

1. Query external database for entries with required sort.
2. Get a set of ISBN entries.
3. Query posts from WP database corresponding to entries.
4. Override main query with this set of posts (post__in query argument and orderby=>post__in for example).

Quite roundabout, but I can't think of other way without data mirror.