1

I am trying to get all subcategories which have articles linked to it.

To visualize the needed result:

  • subcategory A (10 articles linked)
  • subcategory B (0 articles linked) <- I do not want this one
  • subcategory C (1 article linked)

So for my pods , I have the following: (all Advanced Content Type)

Category pod:

  • Text field: slug
  • Relational field: category_relation (this field is only filled when the category is a subcategory)

Article pod:

  • Relational field: category

I have this query, which works, but it does not skip the subcategories who has no articles in it.

$subcategories = pods( 'category',
    array( 'where' => "category_relation.slug = 'Top Category" 
) );

I know I should do a left join, but I do not see how to get that working because I can only select one pod type at once.

Have anyone experience with this type of getting pods data?

Note: I asked this question on the Pods forum too, but did not get any response. On SO I will reach more people I think and update the forum post on Pods.io with the best answer.

DelphiLynx
  • 911
  • 1
  • 16
  • 41

1 Answers1

1

Have you tried 'where' => 'category_relation.slug = "Top Category" AND 0 < tt.count' yet?

Also, if you pass any string into the 'where' that's a $variable, definite be sure to sanitize it first (pods_sanitize( $variable ), $wpdp->prepare( "category_relation.slug = %s", $variable ), etc..)

Scott Kingsley Clark
  • 987
  • 5
  • 10
  • Hi @Scott, I do not have a column named count so I get an error message about that. Thanks for the sanitization tip! Is there a way to do it? I am trying for hours, I have now a solution in PHP which uses 20 queries extra (for each subcategory one) Edit: I am also pretty sure that I need a LEFT JOIN to get my desired result. – DelphiLynx Nov 12 '15 at 15:17
  • I assumed your 'category' pod is actually a WP Taxonomy type, is that incorrect? Pods doesn't allow using names of reserved objects like that, if you were able to create one, you should definitely change the name to something else to avoid potential issues. In terms of count, if you had a relationship field on the 'category' pod that was bi-directional and related to articles, you could use `AND related_articles.id IS NOT NULL` – Scott Kingsley Clark Nov 12 '15 at 18:34
  • Hi, no it is just a plain Advanced Content Type. Can I create a related_articles bi-directional Pick field afterwards? Or should I have done it at first? Otherwise I create my SQL query by hand, only the wp_podsrel makes it very difficult to create a LEFT JOIN. – DelphiLynx Nov 13 '15 at 08:31
  • Do you have a relationship from your Article pod to your Category pod? If so, you might be able to create a new relationship field on Category to Article and then select 'Bidirectional' and save it -- then try out the code. If that still isn't working, you might try and re-save a few articles to see if they start showing up. – Scott Kingsley Clark Nov 13 '15 at 15:39