[Resolved] Lost With SQL to Query Tables Directly so I can Calculate an Average on a Field

[Author]

Posts

[tacoF]

I'm querying the tables directly. I know, not the best, but here's the thing.

People submit Ratings and Reviews. We store those for the user. Each rating has:
- Title
- Description
- Rating value

One rating is linked to an Exercise. An exercise can have many ratings. So, it's a one-to-many relationship.

When the user submits a rating, after it's stored in the Rating table, I want to run a function, which I know how to do, that is like

SELECT AVG(ratingValue) FROM ratings_table WHERE parent_id = 45

Of course, the SQL is incorrect, but it's to give you an idea. The AVG() function is correct. It would have to refer to the correct field value. The table would also have to point to the right table and probably be connected with some JOINS. The where would define the id of Exercise ID, in other words, get all records where the rating was left on the selected exercise.

I have dug deep to find that the tables are the relations table, connected elements table, and associations, but from there I'm quite lost.

So, I'm looking to understand how to SELECT ALL Ratings LEFT on Exercise XYZ
If I just have an SQL that returns all rows, then I can do the rest.

TIA

[Minesh]

Hello. Thank you for contacting the Toolset support.

First of all I would like to know, did you created the one-to-many post relationship using Toolset post relationship between your post types Exercise (one) and Ratings (many).

I would like to have debug information that will help us to debug and investigate your issue:
- https://toolset.com/faq/provide-debug-information-faster-support/

[tacoF]

Yes. As for debugging, it means removing bugs from your code. There are no bugs.

[Minesh]

Ok to fetch the related posts - Toolset offers the post relationship API function: toolset_get_related_posts()
=> https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_get_related_posts

You can also query the related posts using the WP_Query():
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/#wp_query-argument-for-querying-by-related-posts

Please let me know if you require further assistance.

[tacoF]

The question is in regards to db structure not to whether an API is available.

[Minesh]

Do you want to know how the post relationship is stored with Toolset? If yes:
- Toolset stores the relationship content using custom Tables added to the database.

I would suggest you to check your database and chech the following tables and try to understand the structure.
=> toolset_associations
=> toolset_relationships
=> toolset_type_sets