Relationship between two Custom Post Types – all child records show up

I’d suggest doing things a different way.

The first choice that I’d offer is to set the post_parent field of the tour to the package that it belongs to. That way you can easily filter with get_posts() using the post_parent parameter to get the tours for that package only.

The second choice that probably works a little bit better for a really extensible system is to add in a new relationship column into your database. Something like wp_packages_tours where you store a package ID and a tour ID. That will let you set up tours to packages, but will also let you assign tours to multiple packages if you ever need to in the future.

Hi catacaustic,

Thank you for your reply. The second choice makes more sense! One thing I did not understand though. You said ”

…a new relationship column where you store a package ID and a tour ID

Did you actually mean to create a new table instead with probably three columns like MappingID, TripID and PackageID?

And if I create a custom table (never done that yet) I should be able to use WP_Query and use an Inner Join between three tables to pull data based on either a TripID or a PackageID?

• This reply was modified 7 years, 10 months ago by Subrata Sarkar.

When I’ve done it before I’ve used two columns only. So, I’d have TripID and PackageID and have thoe related to the trips and packages respectively. Without knowing your system I can’t say if there’s a need for the map ID as well, but I would guess that there isn’t because you’re really only working off the package and not the map.

To get the data from this, I normally create my own SQL statements and run them through $wpdb->get_results( $query );. As a very quick and in-accurate example…

$query = $wpdb->prepare( "SELECT package_id FROM ".$wpdb->prefix."trips_packages WHERE trip_id = %d", $trip_id );

$packages = $wpdb->get_results( $query );

foreach( $packages as $package ){
    echo "<p>Package ID: '" . $package->package_id . "'</p>";
}

Thank you so much once again. Sorry for asking for one more help.
I am using Package post type as a Relationship field in Trip post type. How would I get the IDs of selected Package?

What I have read so far I need to create a hook in functions.php so that when a post is added via Trip post type the function would trigger. What hook I should use here – save_post like this?

function create_map_on_save($id) {
  // Code to insert Trip ID and Package ID in the custom table
}
add_action('save_post','create_map_on_save');

What I don’t understand are
1. where to call this function from?
2. $id is the Post ID, i.e Trip ID but how would I pass the Package ID (e.g. $pid) in the function?

Sorry for asking stupid question! I am fairly new to WordPress.

Hi,
I am back once again with new issue ??

I created a table wp_trip_packages and with two columns trip_id and package_id. I wrote the following function in my functions.php file:

function save_trip_package_relationship($id, $post){
        //Code to insert mapping
        if( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
            return;
        }

        if (isset($post->post_status) && 'auto-draft' == $post->post_status) {
            return;
        }

        global $wpdb;
        $table_name = $wpdb->prefix . "trip_packages";

        $wpdb->insert($table_name, array(
            'trip_id' => $id,
            'package_id' => 20
        ));
    }

    add_action('save_post_trips', 'save_trip_package_relationship');

When I am adding a Trip records are getting added to the table however a few things I could not figure out:

1. Whenever I am clicking on Add New a new record is getting added
2. When I delete a post draft, a new record is being added
3. When updating a record is being added as well

I found the following two snippets by Googling but nothing is working

if( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
   return;
}

if (isset($post->post_status) && 'auto-draft' == $post->post_status) {
   return;
}

How can I prevent these duplicates to get saved in the table?

NB: From my previous message: “$id is the Post ID, i.e Trip ID but how would I pass the Package ID (e.g. $pid) in the function” – please also help me with this. I explained the problem of getting relationship field ID in my previous post.

Looking forward to your suggestion for one more time…

I hope you don’t mind my jumping in. catacaustic and I have worked together on topics in the past.

You need a different action hook, save_post_trips is too general. If your intention is to only add a record when a trips post is first published, the action “draft_to_publish” should work for you. The problem here is it fires for all newly published posts, not just trips. Since the current post object is passed to your callback, it’s simple enough to add a conditional so the record is only added when the post type is trips.

“draft_to_publish” resolved duplicate entries but it creates another issue now. The trip_id is being saved as 0, which means $id is not getting the actual ID value of the trip. How to get that with this hook?

And I am yet to figure out how to get IDs of relationship field (tour_package). Would you please tell me how I can retrieve ID of selected tour_package? Is there a better way to do this?

I have finally got a solution but not sure if this is the best one!

This is how my function looks like now

function create_trip_package_map($post_id){
        global $wpdb;
        $selected_packages = get_field('available_tour_packages');
        if($selected_packages) {

            $wpdb->query('DELETE FROM ' . $wpdb->prefix . 'trip_packages WHERE trip_id = ' . $post_id);

            foreach($selected_packages as $package) {
                $table_name = $wpdb->prefix . "trip_packages";

                $wpdb->insert($table_name, array(
                    'trip_id' => $post_id,
                    'package_id' => $package->ID
                ));
            }
        }
    }

    add_action('acf/save_post', 'create_trip_package_map', 20);

I referred to this article:
https://www.advancedcustomfields.com/resources/get_field/

And then on my tour page I am running a custom query to retrieve records from my custom table wp_trip_packages using get_the_ID() of current tour.

By doing the above I am now able to filter out only those package tours associated with current trip.

I believe there is a better way to do it but don’t know what it is. So please advise me the best way of doing this.

If that works for you then perhaps it’s good enough. Removing and recreating data on every save doesn’t appear to be the best approach, but I don’t know enough about your overall schema to be a proper judge, nor to suggest what might be “best”.

It appears the draft_to_publish approach would be better since it only runs when a post is first published. You had the issue of getting the trip ID. Is this the same as the post ID of the post being published? If so, get the ID from the passed post object. If you collect the passed object with $post, the ID within is available as $post->ID

Even if that is not the trip ID, I would expect you to be able to get it given the post ID. For example, if the trip ID is in postmeta, you could get the value with get_field() or get_post_meta(). If there’s any relationship at all with the post being published, there’s some way to get to it with the post ID.