SQL syntax for LEFT JOIN of two related PODS Custom Post Types

  • Resolved jjci

    (@jjci)


    3 years, 9 months ago

    I need help with the syntax of creating the ‘join’ parameter of a PODS extract.
    I have two related PODS Custom Post Types; Location_Items is the parent of Location_Offers, and the relationship is supported by bi-direction fields in each CPT.

    The Location_Items POD (‘loc_items’) uses field ‘child_loc_offers’.
    The Location_Offers POD (‘loc_offers’) uses field ‘parent_loc_item’

    I am using PODS to extract a complete list of Location_Items for only one Location, therefore I have set up a ‘where’ clause in the PODS parameters.
    However, I want to also retrieve the related Location_Offers associated to each retrieved Location_Item.

    My (latest) attempt at the ‘join’ clause is as follows:

    “LEFT JOIN ‘loc_offers’ ON ‘loc_offers’.’parent_loc_item.ID’ = ‘t.ID'”

    This seems to align with the example in the documentation, but it’s not working.
    I found that the single example in the documentation to be somewhat vague.

    What am I missing?

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @jjci

    I’m not sure where you use this code. Within the where param??
    You shouldn’t be adding JOIN’s within our shortcodes or API calls so I’m curious where you found this documentation.

    From what I read in your issue it looks like you should just fetch the relationship field of the current location item:

    $pod = pods( 'loc_items', THE_LOCATION_ID );
$offers = $pod->field( 'child_loc_offers' );

    Let me know!

    Cheers, Jory

    Thread Starter jjci

    (@jjci)

    Documentation page URL: https://pods.io/docs/code/pods/find/

    Under “Additional Parameter Options”

    Join:
    Additional table(s) to JOIN, using SQL syntax like “LEFT JOIN my_table ON my_table.id = t.id”

    Regarding my application, I am using it in some PHP code designed to respond to an AJAX call.

    I have since developed a work-around similar to what you suggested. However, I would still like to understand it if I should ever consider using JOIN in the future as a means to retrieve related data in a single extract process.

    That said, this is no longer inhibiting my development, therefore you can consider it resolved.

    Thanks,
    JJCI

    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @jjci

    The only application I can think of at the moment is when using custom tables in your database. For metadata or other WordPress/Pods tables it seems redundant.

    Cheers, Jory

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘SQL syntax for LEFT JOIN of two related PODS Custom Post Types’ is closed to new replies.