[Resolved] Query records that have children

[Author]

Posts

[Steve]

This is for an art gallery. I have artists (parents) and paintings (children) Id like to make a list that displays only artists that the gallery currently has paintings of in stock. the query is slightly more complex as there are some artists who they represent contractually that they want to display in this list no matter what and so we have a check box (always display) for that. For that second part im easily able to create the query but i don't see a way to select only artists if they have children. How would I approach that

[Christian Cox]

Hello, there's no built-in filter that tests whether or not a post has related posts in a given post relationship. You could approach this in a couple of different ways. One way is to create a View of the child post type and use the "list with separator" output format to generate a comma-separated list of the parent post ID in the results. That could give you an output like:

1,1,2,3,3,3,4

You can see some parent post IDs will be repeated if that parent post has multiple child posts. You could use those post IDs to filter a View of the parent post type with a post ID filter set by a shortcode attribute ids:

[wpv-view name="your-parent-view-slug" ids="[wpv-view name='your-child-view-slug']"]

Or for a pure code-based solution, you can use the toolset_get_related_posts API to query for all parent posts for an array of child post IDs, as in this example:

$parents = toolset_get_related_posts(
    [
      'child' => [123, 234, 345, 456]
    ],
    'book-chapter',
    [
      'role_to_return' => 'parent'
    ]
  );

You would replace the child post IDs and replace the slug book-chapter with the slug of your one-to-many relationship. The result is an array of parent post IDs, which you can use to generate your own list.

Let me know if you have questions about either approach.

[Steve]

Christian thanks so much. The first scenario could work if I could limit the returned ids of the child to one per parent. Some of these artists will have one others will have dozens.

In the second scenario I'll likely need some hand holding as i'm not a programmer- But right off im not sure if this will work either as the list of post ids would need to be dynamic so that as paintings get added no edits to this snippet would be required

[Christian Cox]

The first scenario could work if I could limit the returned ids of the child to one per parent. Some of these artists will have one others will have dozens.
If your concern is that there will be duplicate parent posts in the View results, that is not the case. Even if the list of parent post IDs has duplicates, each parent post will only be shown once in the parent View results. That functionality is handled internally by the View's post IDs filter.

In the second scenario I'll likely need some hand holding as i'm not a programmer- But right off im not sure if this will work either as the list of post ids would need to be dynamic so that as paintings get added no edits to this snippet would be required
It's just an example, we can modify it to fit your needs. If you want a dynamic list of child IDs, you would combine this with the results of a separate query of child posts:

$children_args = array(
  'post_type' => 'child-post-type-slug',
  'numberposts' => -1,
  'fields' => 'ids'
);
$children = get_posts($children_args);
$parents = toolset_get_related_posts(
    [
      'child' => $children
    ],
    'post-relationship-slug',
    [
      'role_to_return' => 'parent'
    ]
  );

Documentation for get_posts:
https://developer.wordpress.org/reference/functions/get_posts/

[Steve]

Christian thanks again. So using the shortcode method am i able to combine that somehow then with a field filter. It doesnt seem to work if i add the filter directly to the parent view . I also tried to add the field filter directly to the shortcode something like this
[wpv-view name="artisitswithpaintingsordisplayalways" always-display="yes" or ids="[wpv-view name='idsofpaintings']"]

Though i saw no documentation that this was possible

[Christian Cox]

I think you need 3 Views to accomplish this without any additional custom code.

Main Artists View: A View of Artists filtered only by post ID, where ID is set by the shortcode attribute ids. You will design the results here as you expect to see the list of Artists displayed on the front-end of the site.

Secondary Artists View: A View of Artists filtered only by the Always Display checkbox. Use the legacy Views editor to create this View, and choose the output type "List with separators". In the loop, insert only the post ID, and use a comma as the separator. This View will output a list of post IDs like 1,2,3.

Paintings View as we discussed earlier: https://toolset.com/forums/topic/query-records-that-have-children/#post-1980279
It should output a comma-separated list of Artist post IDs like 1,2,3.

You will combine the results of the Paintings View and the "always display" View. Both of those Views output lists of Artist post IDs, so you will combine them in the Main Artists View like so:

[wpv-view name="your-main-artists-view-slug" ids="[wpv-view name='your-paintings-view-slug'],[wpv-view name='your-always-display-view-slug']"]

[Steve]

Christian I got dragged away in another project but wanted to thank you for the prompt attention. I'll look at this probably on monday and let you know how it goes

Thanks

[Christian Cox]

No problem, I will stand by for your update and we can continue when you are ready.

[Steve]

Ok im getting closer. Im stuck on the view of the paintings which you describe..

"One way is to create a View of the child post type and use the "list with separator" output format to generate a comma-separated list of the parent post ID in the results. That could give you an output like:"

Im not sure how to filter this to get the parent (or artist id) Ive looked at the post relationship filter- but i don't see the artist's post id field in the field picker to insert into the loop. No matter what i set in the filters it looks like i get ids of paintings not artists

[Christian Cox]

When you create the View of Paintings you should not apply any post relationship Query Filters because the Paintings View should query all Paintings posts. See screenshot 1 here, showing no filters applied. When you use the Loop Wizard to create the View's loop, you will choose the "List with separators" format as we already discussed (screenshot 2). In the Loop Wizard you will insert the Post ID in the loop, then click the "Edit" button in the same row to display some additional settings for the post ID field (screenshot 3). Choose the option for a post related to the current post, set by a Types relationship. Select your Artists - Paintings post relationship (screenshot 4). In these examples, my relationship is between Books and Chapters but you'll get the idea. Screenshot #5 shows the generated loop syntax after configuring the other options.

[Steve]

Christian thanks for that detailed explanation, ive got it now.

Thanks Thanks Thanks