I have a number of cpts:
publication dates,
pubulications, there can be multiple publications per date
adverts, there will be many adverts per publication and each advert can be many adverts
Currently I have it setup that each woocommerce (post form) order creates one advert post with multiple custom date fields corresponding to a publication date.
I am building a custom back end and I have a list of publications and I need a link to a list of all adverts in that publication. I am looking at view filters and passing url arguments and I am beginning to wonder if this is going to cause me and issue with speed.
It is likely that there will be tens of thousands of advert posts, and with the current setup I think the query will need to get all of these post then query each post "if publication_date == advert_date_1 OR advert_date_2 OR advert_date_3"
1) Am I going to be better off having multiple identical advert posts one for each publication that a specific advert is in? This seems like a lot of extra post. I would also need to then add more custom code to track these individual posts and handle updating all of them when one if updated. Which would also have to take into account, if one has been published it shouldn't be updated etc.
2) Is a post relationship going to help with any of this?
3) is there a better way to filter/query the view?
Hi, I'm not quite clear what this means:
adverts, there will be many adverts per publication and each advert can be many adverts
Do you mean there can be many adverts in each publication, and each advert can also be in many different publications, i.e. a many-to-many relationship between adverts and publications? I will assume so.
1) Am I going to be better off having multiple identical advert posts one for each publication that a specific advert is in? This seems like a lot of extra post. I would also need to then add more custom code to track these individual posts and handle updating all of them when one if updated. Which would also have to take into account, if one has been published it shouldn't be updated etc.
If the answer to the question above is "yes - a many-to-many relationship between adverts and publications", then there's no need for multiple identical advert posts. A many-to-many Toolset post relationship can help you establish these connections between individual adverts and individual publications without the need for post duplication.
2) Is a post relationship going to help with any of this?
3) is there a better way to filter/query the view?
If the answer to my question about the relationship details is "yes - a many-to-many relationship between adverts and publications", then a post relationship can be helpful because there's no need to duplicate posts or set up queries that test for a custom field value in any one of an undetermined number of custom fields. A toolset_get_related_posts query can be used to fetch all of a post's related posts in a specific post relationship. See our post relationships API information here: https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_get_related_posts
I am looking at view filters and passing url arguments and I am beginning to wonder if this is going to cause me and issue with speed.
It's hard to say for sure, hypothetically speaking, but I can give you some additional information that may help. Query speed is limited by WordPress's database architecture and many other factors. Out of queries based on taxonomy, queries based on custom field values, and queries based on custom database table joins (e.g. toolset post relationship queries), the most performant queries are taxonomy queries. That's an effect of the WordPress database architecture. Therefore, if you can structure your relationships somehow based on taxonomy terms, you'll get the fastest results in a query of "related" posts. However, the speed of a query based on an undetermined number of custom field values versus a query based on multiple custom database table joins is not easily, hypothetically, compared. It greatly depends on the number of custom fields you need to query, and whether you need to then make another query to fetch the "related" post somehow based on those custom field values. Your example only included 3 possible fields ("if publication_date == advert_date_1 OR advert_date_2 OR advert_date_3"), but if the number of possible fields is truly unlimited, for example if an advert can belong to a hundred publication dates, then the answer is not clear. If you need to then also query publication posts based on that publication_date value, the query becomes even more complex and less performant. Your best bet is to set up a sandbox site to test this out with a large number of dummy generated posts before you commit to some data structure. I like Fakerpress for this type of performance test, you may find it useful to generate dummy content. If you want to compare a multiple custom field query to post relationship queries, you can set up a CSV import process to import relationships into your dummy content: https://toolset.com/course-lesson/how-to-import-posts-from-csv-files-and-maintain-relationships-when-using-the-csv-importer-plugin/
Implementing pagination on whatever query you choose is obviously a good idea. Let me know if you have follow-up questions about this.
Thanks Christian,
Your assumptions are correct.
I have implemented the many to many relationship between publications and adverts, this has help solved the current hurdle as I can now query the view using post id passed via urls.
I am still a bit torn about if it is a good idea to have dates and publications separate. I could integrate them there is only a few fields in dates and if there was two publications on one date it would still work when integrated. Having dates and publications I don't think actually affords me any speed or easy benefits.
I do use lists of dates quite extensively on the site, so I would like to find a way to have these either cached or queried very quickly. Like you say I would need to do some testing to quantify that.
The Taxonomy info is good thankyou, I never really under stood that about taxonomy.
I think in my use case it would need to be used in conjunction with either the date CPT or date custom field to handle the other data associated with the date. But I am assuming in larger DBs it would be more efficient to search taxonomy for post ids and then get the specific post based on id rather than searching custom post data.
For anyone else reading this it is worth remembering the taxonomy is string based so using numbers or dates is a little harder I think in this case could be worked around.
Thanks for sharing your feedback. One thing I'd like to mention is Views caching.
I do use lists of dates quite extensively on the site, so I would like to find a way to have these either cached or queried very quickly.
Toolset Views implements its own caching layer, which will help performance in certain cases. Information about scenarios that bypass that cache can be found here:
https://toolset.com/documentation/legacy-features/views-plugin/how-to-use-views-parametric-search-on-large-sites/
If you can work around the scenarios described there, Views caching may help speed up performance of the date lists you mentioned. Otherwise, a 3rd-party caching plugin may increase the performance of pages containing Views that are not otherwise cached by Views caching.
