Where declaration not working with field setup to associate users. Unknown Column error #894

Closed
eccentricpixel opened this Issue Dec 19, 2012 · 12 comments

Comments

Projects
None yet
3 participants

In short, this scenario is this:

  • the goal is to find records which have been associated to a specific user
  • a pod is setup as a custom post type and not advanced content type, with meta storage type
  • front-end renders results successfully when using other fields in this pod within WHERE declaration
  • however, when using a field setup as a multiple select direct to wp users (not another pod), the result is the front-end not rendering but instead reporting the field as an "unknown column".
    • using pods 2.1
  • tried several setups and its just not working .
Owner

pglewis commented Dec 20, 2012

Are you using PHP code and the API? Can you paste the code you're using here or in pastebin?

@ghost ghost assigned pglewis Dec 20, 2012

Owner

pglewis commented Dec 20, 2012

I have a CPT and an ACT both with a single added field, 'members', a multi-select relationship to WP users.

The following code works with an advanced content type:

        $params = array(
            'where'   => 'members.user_login = "testuser1"',
            'limit'   => -1
        );
        $obj = pods('issue_894_act', $params);
        while($obj->fetch()) {
            pods_debug($obj->field('name'));
        }

The same code does not work for the CPT setup the exact same way.

Exactly. I would just take the time to recreate everything with ACT but
then I lose search and commenting functionality aside from it being time
consuming. Any ideas on why CPT won't work? I tried user_id also but no
go.

@ghost ghost assigned sc0ttkclark Dec 20, 2012

Owner

pglewis commented Dec 20, 2012

Nah, you shouldn't have to change your strategy of course. I'm not 100% sure if this is a bug or if there is an alternate syntax to use, so I wanted a short snippet to demonstrate; with that, @sc0ttkclark will know at a glance.

In either case we'll get it ironed out for you.

Owner

sc0ttkclark commented Dec 20, 2012

Can you retest this again on the latest 2.1.1 http://pods.io/latest/

Owner

pglewis commented Dec 20, 2012

WordPress database error: [Unknown column 'members.user_login' in 'where clause']
SELECT DISTINCT t.* FROM wp_posts AS t LEFT JOIN wp_podsrel AS rel_members ON rel_members.field_id = 5 AND rel_members.item_id = t.id LEFT JOIN wp_usermeta AS members ON members.meta_key = 'members' AND members.user_id = rel_members.related_item_id WHERE members.user_login = "testuser1" AND t.post_type = "issue_894" ORDER BY t.menu_order, t.post_title, t.post_date

It's joining wp_usermeta but not wp_users

P.S.: Boo on me for not posting the SQL error before!

Owner

sc0ttkclark commented Dec 20, 2012

Okay that makes it easier to dig into now, thx!

Owner

sc0ttkclark commented Dec 27, 2012

It joins wp_usermeta table for members.anything, but it should really only join it if you're calling members.anything.meta_value as our new setup should use. So, in this case, we need to add some logic to make sure that's what's happening.

sc0ttkclark added a commit that referenced this issue Dec 29, 2012

@ghost ghost assigned pglewis Dec 30, 2012

Owner

sc0ttkclark commented Dec 30, 2012

This needs multiple tests:

  • t.field_name
  • meta_field_name.meta_value
  • relationship.field_name
  • relationship.meta_field_name.meta_value
  • relationship.another_relationship.field_name
  • relationship.another_relationship.meta_field_name.meta_value
  • relationship.another_relationship.tertiary_relationship.field_name
  • relationship.another_relationship.tertiary_relationship.meta_field_name.meta_value
Owner

pglewis commented Dec 30, 2012

[Edit] I thought this was resolved but it turned out my page code I was using for testing needed a manual clearing of the cache after making changes.

Still getting the same SQL error with a custom post type.

Owner

pglewis commented Dec 30, 2012

To go with the code above: #894 (comment)

Import package: https://gist.github.com/4410616

Owner

pglewis commented Dec 30, 2012

After some research, this appears to be a general issue with meta based pods and relationship fields. Here's my attempted mind-meld on what I've found.

In traverse_recurse(), all pods using meta storage are routed through the branch here:

https://github.com/pods-framework/pods/blob/2.1.x/classes/PodsData.php#L2029

and due to the very next line:

if ( !in_array( $traverse[ 'type' ], $tableless_field_types ) ) {

this means every meta storage Pod with a pick field will be routed through here to build the join:

https://github.com/pods-framework/pods/blob/2.1.x/classes/PodsData.php#L2048


  • Case 1: a meta based CPT and a relationship to WP users (pick field named "members"). This is the original issue.

Attempting to use 'where' => 'members.user_login = "testuser1"' results in this join:

LEFT JOIN `@wp_podsrel` AS `rel_members` ON
    `rel_members`.`field_id` = 5
    AND `rel_members`.`item_id` = `t`.`id`
LEFT JOIN `wp_usermeta` AS `members` ON
    `members`.`meta_key` = 'members'
    AND `members`.`user_id` = `rel_members`.`related_item_id`

Which in turn gives: Unknown column 'members.user_login' in 'where clause' because it's aliasing wp_usermeta as members instead of wp_users.


  • Case 2: meta based CPT with a relationship to a regular ACT Pod runs through that same branch at L2048. Using 'where' => 'campuses.name = "campus 01"' in this case yields:
LEFT JOIN `@wp_podsrel` AS `rel_campuses` ON
    `rel_campuses`.`field_id` = 216
    AND `rel_campuses`.`item_id` = `t`.`id`
LEFT JOIN `wp_pods_campuses` AS `campuses` ON
    `campuses`.`name` = 'campuses'
    AND `campuses`.`id` = `rel_campuses`.`related_item_id`

The campuses pod is just an ACT I had sitting around, this can be duplicated with any relationship to an ACT. I'm not really sure what's up with campuses.name = 'campuses' but it ensures the query can never return any records, without an actual SQL error.


It occurs to me that ACTs in the same situations get the joins correct and we probably don't care about the storage type here in the case of the "tableless_field_types", which includes pick fields. Indeed, hacking things to force my CPT through the branch an ACT takes at:

https://github.com/pods-framework/pods/blob/2.1.x/classes/PodsData.php#L2062

... immediately works for the two simple CPT cases that failed above.

If I understand enough about what's going on, maybe we want to do a blanket check first on in_array( $traverse[ 'type' ], $tableless_field_types ) and filter that case out with the joins currently at L2063 before breaking down into meta/table specific handling.

pglewis added a commit that referenced this issue Jan 3, 2013

sc0ttkclark added a commit that referenced this issue Jan 3, 2013

sc0ttkclark added a commit that referenced this issue Jan 3, 2013

@ghost ghost assigned pglewis Jan 3, 2013

sc0ttkclark added a commit that referenced this issue Jan 3, 2013

@sc0ttkclark sc0ttkclark closed this Jan 3, 2013

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