Support » Plugin: Pods - Custom Content Types and Fields » DB Error: Unknown column in ‘on clause’

  • Resolved bigbanks

    (@bigbanks)


    Hi,

    I’m executing quite simple find function:

    
    $my_pod = pods( 'my_pod' );
    
    $params = array(
      'limit' => 1,
      'where' => '
          simple_relationship_field_1.meta_value = "' . pods_sanitize( 'value_1' ) . '" AND
          simple_relationship_field_2.meta_value = "' . pods_sanitize( 'value_2' ) . '"
         '
    );
    
    $my_pod->find( $params );
    
    if ( $my_pod->total() > 0 ) {
       while ( $my_pod->fetch() ) {            
           echo $my_pod->field('ID');
       }
    }

    As a result it throws a Database error:

    Database Error : [Unknown column ‘simple_relationship_field_2.post_id’ in ‘on clause’]

    SELECT DISTINCT t.* FROM wpbf20_posts AS t LEFT JOIN wpbf20_postmeta AS simple_relationship_field_1 ON simple_relationship_field_1.meta_key = ‘simple_relationship_field_1’ AND simple_relationship_field_1.post_id = t.ID LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2_meta_value ON simple_relationship_field_2_meta_value.meta_key = ‘meta_value’ AND simple_relationship_field_2_meta_value.post_id = simple_relationship_field_2.post_id LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2 ON simple_relationship_field_2.meta_key = ‘simple_relationship_field_2’ AND simple_relationship_field_2.post_id = t.ID WHERE ( ( simple_relationship_field_1.meta_value = ‘value_1’ ) AND ( simple_relationship_field_2.meta_value = ‘value_2’ ) AND ( t.post_type = “my_pod” ) AND ( t.post_status IN ( “publish” ) ) ) ORDER BY t.menu_order, t.post_title, t.post_date LIMIT 0, 1

    Database Error; SQL: SELECT DISTINCT t.* FROM wpbf20_posts AS t LEFT JOIN wpbf20_postmeta AS simple_relationship_field_1 ON simple_relationship_field_1.meta_key = ‘simple_relationship_field_1’ AND simple_relationship_field_1.post_id = t.ID LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2_meta_value ON simple_relationship_field_2_meta_value.meta_key = ‘meta_value’ AND simple_relationship_field_2_meta_value.post_id = simple_relationship_field_2.post_id LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2 ON simple_relationship_field_2.meta_key = ‘simple_relationship_field_2’ AND simple_relationship_field_2.post_id = t.ID WHERE ( ( simple_relationship_field_1.meta_value = ‘value_1’ ) AND ( simple_relationship_field_2.meta_value = ‘value_2’ ) AND ( t.post_type = “my_pod” ) AND ( t.post_status IN ( “publish” ) ) ) ORDER BY t.menu_order, t.post_title, t.post_date LIMIT 0, 1; Response: Unknown column ‘simple_relationship_field_2.post_id’ in ‘on clause’

    However I can perform similar query using WP_Query and it does work with no issues:

      $my_query = new WP_Query( 
        array(
          'post_type' => array('my_pod'), 
          'meta_query' => array( 
            array(
              'key'     => 'simple_relationship_field_1',
              'value'   => pods_sanitize( 'value_1' ),
              'compare' => '='
            ),
            array(
                'key'     => 'simple_relationship_field_2',
                'value'   => pods_sanitize( 'value_2' ),
                'compare' => '='
            ),
           )
        ) 
      );
    
      while ( $my_query->have_posts() ) :
          $my_query->the_post();
          echo get_the_id() . '<br/>';
      endwhile;

    What may cause an issue?

    Thank you

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

    (@keraweb)

    Hi @bigbanks

    That is weird. When I look at the SQL I don’t really see any issues there.
    The post_id should be in the postmeta table so I don’t get the error.
    Though I don’t get the second LEFT JOIN in this query..

    
    SELECT DISTINCT t.* FROM wpbf20_posts AS t 
    
    	LEFT JOIN wpbf20_postmeta AS simple_relationship_field_1
    		ON simple_relationship_field_1.meta_key = ‘simple_relationship_field_1’
    			AND simple_relationship_field_1.post_id = t.ID
    
    	LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2_meta_value
    		ON simple_relationship_field_2_meta_value.meta_key = ‘meta_value’
    			AND simple_relationship_field_2_meta_value.post_id = simple_relationship_field_2.post_id
    
    	LEFT JOIN wpbf20_postmeta AS simple_relationship_field_2
    		ON simple_relationship_field_2.meta_key = ‘simple_relationship_field_2’
    			AND simple_relationship_field_2.post_id = t.ID
    
    	WHERE (
    		( simple_relationship_field_1.meta_value = ‘value_1’ )
    		AND ( simple_relationship_field_2.meta_value = ‘value_2’ )
    		AND ( t.post_type = “my_pod” )
    		AND ( t.post_status IN ( “publish” ) )
    	)
    
    	ORDER BY t.menu_order, t.post_title, t.post_date
    
    	LIMIT 0, 1
    

    I’ll try to reproduce locally and let you know.

    Cheers, Jory

    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @bigbanks

    When I try the exact same query locally I do not get an error..

    Could you check for a plugin conflict?
    https://docs.pods.io/faqs/plugin-theme-conflicts/

    Cheers, Jory

    Hi Jory,

    Thank you very much for the response. I have followed the link, installed “Health Check” plugin, and could discover that the error occurs when “WPML Multilingual CMS” plugin is activated. Additionally I have tested my code on another project, that doesn’t have any WPML-related plugins and everything works correctly.

    Not sure, what could I do next to fix the issue?

    Thank you

    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @bigbanks

    I’m honestly not sure. I think it’s best to contact WPML about this. I almost get the impression that their plugin altered the table or something, which is odd..

    Cheers, Jory

    Hi,

    Thank you for your effort anyway. I don’t think WPML team would take an action to solve the problem, that appears in Pods. In my practice, their support always forwarding to a problem plugin’s developer, saying that plugin’s developer can contact them directly to work over the compatibility. Sad.

    As a workaround, for now I will use WP_Query that I mentioned in the first post.

    Btw on the Pods plugin page, mentioned:
    “Plugins we’ve integrated with: – WPML”.

    There’s probably somebody in Pods team who had worked on the integration with WPML ^_^ ?…


    Cheers

    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @bigbanks

    Sorry for my late reply.

    In the beginning Pods created some compatibility code internally but the current compatibility code is actually made by a WPML employee.. Not sure if he is still part of the WPML team though.

    Cheers, Jory

Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.