Solved this. Wrong way to go about the whole thing.
You could do what you’re describing above with an Advanced Relationship field (allows you to point something outside of the WordPress into a table external to WordPress but in the same table). These are activated under Pods Admin, Components, Advanced Relationships.
What did you end up using to solve this?
I had done exactly what you described – but the problem seems to be that although I created the fields with linked relationships – and Pods recognized and allowed me to select the correct database table – the resulting saved Pod doesn’t show any content.
How does the advanced relationship field “pull” data from the specified table? Only when the Pod is called on some page?
The “other” method I was going to try was just to use a SQL query from within WordPress, but after consultation with others far more proficient in SQL than I, decided not to approach the problem that way.
The basic issue is that I have a 7700 row Excel file given to me by a client, that I need to set up so that I can and allow visitors to search within WordPress. Exporting that Excel file to CSV and pulling it into a MySQL table is simple. How to query it from within WordPress, so far, has been elusive.
Yes, you have to use Pods methods (https://pods.io/docs/code/pods/ and fetch, field, display, etc.) to show the data in Advanced Relationships. WordPress Core has no awareness of these connections (since they are outside of their standard configuration), so you would have to use Pods Methods to display them.
7700 rows honestly isn’t really that much, depending on what kind of data you’re searching in the table.
You could setup a Custom Post Type with Table Storage (activated under Pods Admin, Components, Table Storage and under the ‘Advanced’ when you create the Post Type. This puts all the meta fields for the Post Type in their own table (named like wp_pods_<post-type>). These fields are indexed so the access is much faster and WP Queries work against them as well.
If you’re not sure the best direction to take, please feel free to drop onto our Slack Chat at https://pods.io/chat/ in the #dev-chat channel and we’ll get you into discussion with some of the developers to help you make a decision that will work best for you.
Thanks. After some more experimeting, I was able to approach the problem like this:
1.)Import sample CSV file (just a subset of the enormous file itself, for testing…) into WP via WP All Import plug. This, of course, results in one new Post being created for each of the imported file’s records.
2.)Imported post fields are NOT custom fields as defined by ACF, WordPress itself, etc. But they show up fine, and therefore, I’m able to Search them in the usual manner. I can set the imported records to all have the same (existing) Category when they are imported. Styling simple via CSS.
3.)The (eventual) result will be, 7700 Posts, each very small, all just text. A look at the wp_posts table shows this will consume about 90 Mb, not a big deal.
I still wonder if there is a way to Search this data without having to create such a large number of posts. My understanding of the way Pods works is admittedly not very good. Is it true that if I would be able to access this data via Pods that it would just create 7700 Pods? Or can Pods somehow query the internal WP custom database table without having to generate the large number of Pods visible from the Backend?
If all of your records are inside wp_posts, then no, there’s no difference with searching those 7700 records in one table or an external SQL table, because that’s an identical setup.
You wouldn’t create 7700 ‘Pods’; you’d create 7700 posts in wp_posts with a post-type of whatever you called the import. We can generate a separate table, sure, but it’s no different than what you’ve done.
If you’re looking for a search that will only search those post-types, you can limit to specific post types with Ivory Search (https://ww.wp.xz.cn/plugins/add-search-to-menu/)
We always recommend working smarter not harder; anything that integrates with the primary structures that WordPress supports is the smartest way to integrate into your website, because then anything within the WordPress infrastructure of plugins will work with it. You get the benefit of both and there really is no performance issue by having those posts in their own table.
Here’s a really good article on the structure of the WordPress Database:https://deliciousbrains.com/tour-wordpress-database/
Thanks for the advice. In fact, I am using Ivory Search in exactly the manner you mentioned. I guess that the relatively large number of posts – something I’ve never seen in any WordPress site on which I’ve worked – made me feel that perhaps this wasn’t the right way to go about implementing the custom search I needed.
wp_posts and taxonomy are indexed. If you had custom meta, yes, I would’ve suggested a Table Storage for your Custom Fields as that would’ve placed them in their own table, ie wp_pods_<post-type> for just the fields and that would be one row per post as well linked to wp_posts. And indexed.
Have you noticed any slowness? Search is typically indexed ahead of time anyway, and weighted, so it should be performant.
I was able to take the existing WP fields and tie them to the data I needed. The imported file only has 10 potential fields, and I think I’ll only need 8 of those. So, I need to edit my Import settings and try another test to make sure I have everything I’ll need.
As far as performance goes, I have no idea yet, of course. That will be the thing that determines whether the custom search (set to a single category) is even functional. I will check back in a few days when I know. My plan is to load the CSV data in relatively small batches.
Just to bring this issue to a close: after successfully imported some 7700 records via a CSV file through WP All Import, and making use of Custom Fields, I had that huge number of Posts all set up.
The site performance isn’t affected as far as I can see. Doing a Search limited to that single category of posts, the typical response time, while obviously somewhat dependent upon the number of hits returned, is pretty much instantaneous (for a return of say, a dozen posts…) to less than 2 seconds (for many dozens of hits). So, I’m really very pleased. Your advice to stay within the WordPress was echoed by an SQL developer I ran into at a local WordPress advanced users’ group meeting. All good!
