Relationship traversal not working after switching from wp-meta to table-based meta

pods-framework / pods

The Pods Framework is a Content Development Framework for WordPress - It lets you create and extend content types that can be used for any project. Add fields of various types we've built in, or add your own with custom inputs, you have total control.

GNU General Public License v2.0

1.06k stars 266 forks source link

Hello!

When using Pods with CPT and WP-Meta, the query below works. After recreating the Pod to use table-based meta storage to improve performance, I removed the '.meta_value' affix from the relevant field. The query no longer works.

$where = 
  "bracket.round.tournament.id=$tournament_id " . 
  "AND match_status.code.meta_value <> '". MatchStatus::INITIAL ."'"; 

  //any matches in this tournament?
  $match = pods('match', array(
    'where' => $where
  ));

When I removed '.meta_value', I get the following sql:

Unknown column 'match_status.code' in 'where clause' for query 

                SELECT DISTINCT
                't'.*, 'd'.*
                FROM 'wp_posts' AS 't'

                    LEFT JOIN 'wp_podsrel' AS 'rel_bracket' ON
                        'rel_bracket'.'field_id' = 80
                        AND 'rel_bracket'.'item_id' = 't'.'ID'

                    LEFT JOIN 'wp_posts' AS 'bracket' ON
                        'bracket'.'ID' = 'rel_bracket'.'related_item_id'

                    LEFT JOIN 'wp_podsrel' AS 'rel_bracket_round' ON
                        'rel_bracket_round'.'field_id' = 58
                        AND 'rel_bracket_round'.'item_id' = 'bracket'.'ID'

                    LEFT JOIN 'wp_posts' AS 'bracket_round' ON
                        'bracket_round'.'ID' = 'rel_bracket_round'.'related_item_id'

                    LEFT JOIN 'wp_podsrel' AS 'rel_bracket_round_tournament' ON
                        'rel_bracket_round_tournament'.'field_id' = 56
                        AND 'rel_bracket_round_tournament'.'item_id' = 'bracket_round'.'ID'

                    LEFT JOIN 'wp_posts' AS 'bracket_round_tournament' ON
                        'bracket_round_tournament'.'ID' = 'rel_bracket_round_tournament'.'related_item_id'

                    LEFT JOIN 'wp_podsrel' AS 'rel_match_status' ON
                        'rel_match_status'.'field_id' = 85
                        AND 'rel_match_status'.'item_id' = 't'.'ID'

                    LEFT JOIN 'wp_posts' AS 'match_status' ON
                        'match_status'.'ID' = 'rel_match_status'.'related_item_id'

                LEFT JOIN 'wp_pods_match' AS 'd' ON 'd'.'id' = 't'.'ID'
                WHERE ( ( 'bracket_round_tournament'.'id'=106 and 'match_status'.'code' <> 'INITIAL' ) AND ( 't'.'post_type' = "match" ) AND ( 't'.'post_status' IN ( "publish" ) ) )

                ORDER BY 't'.'menu_order', 't'.'post_title', 't'.'post_date'
                LIMIT 0, 15

It seems that Pods is linking to match_status as a reference to the wp_posts table, but is not extending the join to include the match

$where = "bracket.round.tournament.id=$tournament_id " . "AND match_status.id is not null ". //this is to force pods to generate rel_match_status "AND ms.code <> '". MatchStatus::INITIAL ."'"; $join = 'LEFT JOIN wp_pods_match_status as ms '. 'ON ms.id = rel_match_status.related_item_id'; //any matches in this tournament? $match = pods('match', array( 'where' => $where, 'join' => $join ));

pods-framework / pods

Relationship traversal not working after switching from wp-meta to table-based meta #1939