find() Traversal should be able to reference table/meta #972

Closed
sc0ttkclark opened this Issue Jan 10, 2013 · 20 comments

4 participants

@sc0ttkclark
Pods Foundation, Inc member

Available options as of Pods 2.3

  • t.object_field (exists)
  • d.custom_table_field (exists)
  • custom_meta_field.meta_value (exists)
  • related_post_type.post_title (exists)
  • any_relationship.user_relationship.user_login (exists)
  • related_post_type.custom_meta_field.meta_value (in Pods 2.2)
  • another_post_type.d.custom_table_field (in Pods 2.3)
  • taxonomy_name.term_id (in Pods 2.3)
  • taxonomy_name.d.custom_table_field (in Pods 2.3)
// A field on the post type that's part of it's object table
$params['where' ] = 'related_post_type.post_title = "Test"';

// A field on a user relationship field that's part of it's object table on a relationship field
$params['where' ] = 'any_relationship.user_relationship.user_login = "test"';

// A field on the post type that's meta-based
$params['where' ] = 'related_post_type.custom_meta_field.meta_value = "Test"';

// A field on the post type that's table-based
$params['where' ] = 'another_post_type.d.custom_table_field = "Test"';

// A field on the taxonomy that's part of it's object table (Built-in Taxonomies, separate from relationship fields)
$params['where' ] = 'taxonomy_name.term_id = 1';

// A field on the taxonomy that's table-based (Built-in Taxonomies, separate from relationship fields)
$params['where' ] = 'taxonomy_name.d.custom_table_field = "Test"';

// Rinse, repeat, traverse as many relationship levels as needed

screen shot 2013-05-14 at 8 53 24 pm

@sc0ttkclark sc0ttkclark was assigned Jan 10, 2013
@gingerbeardman

custom simple fields having an issue

@sc0ttkclark
Pods Foundation, Inc member

Ready for testing!

@gingerbeardman

still getting an error here:

Response: Incorrect table name ''
@gingerbeardman

to follow up...

(using latest)

this code:

$events = pods( 'events', array( 'where' => "event_fundraising.meta_value = 1 AND event_type.meta_value = 'Lecture'", 'limit' => -1 ) );

yields the following:

Database Error; 
SQL: SELECT DISTINCT `t`.* 
FROM `wp_posts` AS `t` 
LEFT JOIN `wp_postmeta` AS `event_fundraising` ON `event_fundraising`.`meta_key` = 'event_fundraising' AND `event_fundraising`.`post_id` = `t`.`id` 
LEFT JOIN `` AS `event_type` ON `event_type`.`name` = 'event_type' AND `event_type`.`id` = `t`.`id` 
LEFT JOIN `wp_postmeta` AS `event_date` ON `event_date`.`meta_key` = 'event_date' AND `event_date`.`post_id` = `t`.`id` 
WHERE `event_fundraising`.`meta_value` = 1 AND `event_type`.`meta_value` = 'Lecture' AND `t`.`post_type` = "events" 
ORDER BY `event_date`.`meta_value` ASC, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`; 
Response:  Incorrect table name ''
@sc0ttkclark
Pods Foundation, Inc member
@gingerbeardman
@sc0ttkclark
Pods Foundation, Inc member

It would help to have access to the site? Could you manage that? scott@pods.io

@pglewis
Pods Foundation, Inc member

I'm currently setting up the series of test cases for this. It's going to continue to be time consuming initially, getting all the dependent Pods and data created for the tests, but we'll have the test suite available for the future.

@pglewis
Pods Foundation, Inc member

This one was not in the tests above, but I threw it in while I was at it. The meta relationship version of this test passes.

Removing the @ signs from the generated SQL and running it directly in phpMyAdmin appears to work.

    /**
     * Meta CPT -> related Table CPT -> related user (native WP) -> user_login
     *
     * FAILING: query seems to be leaving @ in front of pods_rel
     */
    function test_RelTCPT_RelUser_UserLogin() {
        $params['where'] = 'rel_t_cpt.rel_user.user_login = "Test User 3"';
        $obj = pods( 'podsut_parent_m_cpt', $params ); // Parent, meta CPT
        $this->assertEqual( $obj->field('post_title'), 'Parent Record 03' );
    }
@gingerbeardman

@sc0ttkclark I will email you about access

@gingerbeardman

All fine for me now.

@sc0ttkclark
Pods Foundation, Inc member

Taxonomies should be working now, though not how I want them to; Problem is that taxonomies haven't been associated to post types and vice versa because initial object_fields is populated pre-registration

@sc0ttkclark
Pods Foundation, Inc member

As of today, Pods find() 'where' and 'having' parameters now accepts WP_Query meta_query-like arrays too:

$pods = pods( 'mypod' );

$params = array(
    'where' => array(
        'relation' => 'OR', // AND | OR, default is AND

        // A field on the pod that's part of it's object table
        array(
            'key' => 'object_field', // the field name, or you can define traversal
            'value' => 'Test', // takes an array, like meta_query
            'compare' => '=', // accepts normal meta_query compare options
            'type' => 'CHAR' // accepts normal meta_query casting options
        ),

        // A field on the pod that's table-based
        array(
            'key' => 'custom_table_field',
            'value' => 'Test'
        ),

        // A field on the related post type that's part of it's object table
        array(
            'key' => 'related_post_type', // or related_post_type.post_title
            'value' => 'Test'
        ),

        // A field on a user relationship field that's part of it's object table on a relationship field
        array(
            'key' => 'any_relationship.user_relationship.user_login',
            'value' => 'test'
        ),

        // A field on the related post type that's meta-based
        array(
            'key' => 'related_post_type.custom_meta_field.meta_value',
            'value' => 'Test'
        ),

        // A field on the related post type that's table-based
        array(
            'key' => 'another_post_type.d.custom_table_field',
            'value' => 'Test'
        ),

        // A field on the related taxonomy that's part of it's object table (Built-in Taxonomies, separate from relationship fields)
        array(
            'key' => 'taxonomy.term_id',
            'value' => 1
        ),

        // A field on the related taxonomy that's table-based (Built-in Taxonomies, separate from relationship fields)
        array(
            'key' => 'taxonomy.d.custom_table_field',
            'value' => 'Test'
        )
    )
);

// OMG BBQ
$pods->find( $params );

For more options for value/compare/type, see: http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

@sc0ttkclark
Pods Foundation, Inc member

This same format / usage (as the example shows above) will be used for the integration in #399

@sc0ttkclark sc0ttkclark added a commit that referenced this issue Jan 29, 2013
@sc0ttkclark sc0ttkclark Traversal fixes for unit tests on #972; Fixes to where/having array h…
…andling from PodsData::query_fields
8acfb46
@sirbeagle

This feature is working well for us with the exception of related Pods that have file/image/video fields.

So in our case we can use something like:

findArray = Array
(
    [select] => t.*, associated_table.textfield1, associated_table.textfield2
    [orderby] => `t`.`created` DESC
)

Which works great. Once we try to add in a file field in the associated table like:

findArray = Array
(
    [select] => t.*, associated_table.textfield1, associated_table.textfield2, associated_table.filefield1
    [orderby] => `t`.`created` DESC
)

We get the following error:

Database Error; SQL: 
SELECT 
    DISTINCT t.*, 
    `associated_table`.`textfield1`, 
    `associated_table`.`textfield2`, 
    `associated_table`.`filefield1` 
FROM 
    `wp_15_pods_first_table` AS `t` 
    LEFT JOIN `wp_15_podsrel` AS `rel_associated_table` 
        ON `rel_associated_table`.`field_id` = 1333 AND `rel_associated_table`.`item_id` = `t`.`id` 
    LEFT JOIN `wp_15_pods_associated_table` AS `associated_table` 
        ON `associated_table`.`id` = `rel_associated_table`.`related_item_id` 
ORDER BY `t`.`created` DESC, `t`.`name`, `t`.`id` 
LIMIT 0, 15; 

Response: Unknown column 'associated_table.filefield1' in 'field list'

I'm guessing this is a bug, but... :)

@sc0ttkclark
Pods Foundation, Inc member

Not a bug, you'll want to use filefield1.ID or whatever field you need off of the file field. It's not stored in the table, filefield1 will translate to an alias to the wp_posts table.

@pglewis pglewis was assigned Feb 15, 2013
@sc0ttkclark
Pods Foundation, Inc member

Assigning to @pglewis to finish unit tests so we can run and determine if they all pass, so we can close this ticket.

@sc0ttkclark sc0ttkclark closed this Apr 7, 2013
@sc0ttkclark sc0ttkclark was assigned Apr 7, 2013
@sc0ttkclark
Pods Foundation, Inc member

Just added a screenshot of the new reference table for the coverage of this ticket, it'll be on the find() docs page on the new site.

@sc0ttkclark
Pods Foundation, Inc member

Tests have been replaced for this in our latest /tests/ work for 2.x and will be updated for 3.x when we get there too.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment