Custom Fields
Re-Envisioned
Support MB Relationships WordPress database (MariaDB) error in SQL syntax - MB Relationships

  • Creator
    Topic
  • #31766
    Prabakaran Shankar
    Participant

    Hello,

    I'm getting error notice as below:
    WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) WHERE 1=1 AND wp_1p_posts.post_type = 'job' AND ((wp_1p_posts.post_statu...' at line 1 for query SELECT wp_1p_posts.* , mbr.to AS mbr_69_to FROM wp_1p_posts INNER JOIN wp_1p_mb_relationships AS mbr ON (mbr.from = wp_1p_posts.ID AND mbr.type = '69' AND mbr.to IN ()) WHERE 1=1 AND wp_1p_posts.post_type = 'job' AND ((wp_1p_posts.post_status = 'publish')) GROUP BY mbr_69_to, wp_1p_posts.ID ORDER BY wp_1p_posts.post_date DESC LIMIT 0, 5 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Posts_Controller->get_items, WP_REST_Posts_Controller->prepare_item_for_response, apply_filters('the_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, MBViews\Shortcode->render, MBViews\Renderer->render, MetaBox\Dependencies\Twig\Environment->render, MetaBox\Dependencies\Twig\TemplateWrapper->render, MetaBox\Dependencies\Twig\Template->render, MetaBox\Dependencies\Twig\Template->display, MetaBox\Dependencies\Twig\Template->displayWithErrorHandling, __TwigTemplate_2ca5de825498539a4cbcc43de44f09c90e875b4f3005df4f967576ee9ab978b1->doDisplay, twig_get_attribute, MBViews\TwigProxy->__call, call_user_func_array, get_posts, WP_Query->query, WP_Query->get_posts

    For your reference, please look at the below PHP code of the relationship id 69:

    
    <?php
    add_action( 'mb_relationships_init', 'your_prefix_function_name' );
    
    function your_prefix_function_name() {
        MB_Relationships_API::register( [
            'id'   => '69',
            'from' => [
                'object_type' => 'post',
                'post_type'   => 'job',
                'meta_box'    => [
                    'title'    => 'Add Employer Name',
                    'context'  => 'normal',
                    'priority' => 'high',
                ],
                'field'       => [
                    'name' => 'Job connecting to employer',
                    'desc' => 'Connect to the Country, University (if available Department)',
                ],
            ],
            'to'   => [
                'object_type' => 'post',
                'post_type'   => 'emp',
                'meta_box'    => [
                    'title'    => 'Jobs by the Employer',
                    'priority' => 'high',
                ],
                'field'       => [
                    'name' => 'Job connected from Employer',
                ],
            ],
        ] );
    }
    
Viewing 4 replies - 1 through 4 (of 4 total)
  • Author
    Replies
  • #31781
    Long Nguyen
    Moderator

    Hi,

    Can you please share the code that you use to get the relation posts? And MariaDB version on your server?

    #31786
    Prabakaran Shankar
    Participant

    Hi,

    Server version: 10.3.31-MariaDB-1:10.3.31+maria~bionic

    Initially, 10 days back i have used the below code to get the posts

    
    <section style="background-color:#D5F5E3; padding: 40px 20px 20px; border-top-style: solid; border-top-color: #2ECC71; border-top-width: 5px;">
    <h2>
        Recent Jobs at {{ post.title }}
    </h2>
    {% set job_args = { post_type: 'job', posts_per_page: 5, relationship: {id: '69', to: post.ID} } %}
        {% set jobs = mb.get_posts( job_args ) %}
        {% for job in jobs %}
                <li><a href="{{ mb.get_the_permalink( job.ID ) }}"><strong>{{ job.post_title }}</strong></a></li>
            {% endfor %}
            <p style="padding-top: 30px;">
            <strong>For other related Jobs, please visit <a href="https://nviewscareer.com/job/">Jobs</a></strong>
        </p>
        </section>
    

    But to debug this issue, I have deleted the above code and added new PHP-based code in my function.php

    
    add_shortcode( 'jobs-by-employers', function() {
        ob_start();
        
        $args= array(
            'post_type' => 'job',
            'post_status' => 'publish',
            'posts_per_page' => 10,
            'orderby' => array('title' => 'DESC'),
            'tax_query' => array(
                array(
                    'taxonomy' => 'current-status',
                    'field'    => 'slug',
                    'terms'    => array( 'ongoing' ),
                ),
            ),
            'relationship' => array(
                'id' => '69',
                'to' => get_the_ID(),
            ),
        );
        $query_jobs_employers = new WP_query( $args );
            if( $query_jobs_employers->have_posts() ){
                echo '<ul>';
                while( $query_jobs_employers->have_posts() ){
                    $query_jobs_employers->the_post();
                    ?>
                    <li><a href="<?php the_permalink()?>" target="_blank" rel="noopener noreferrer">
                    <?php the_title(); ?>
                    </a></li>
                    <?php 
                }echo '</ul>';
            }
            else echo 'Sorry No Jobs availalbe at present, Please check later';
        wp_reset_postdata();
        return ob_get_clean();
    }
    );
    
    #31801
    Long Nguyen
    Moderator

    Hi,

    If you use this View code

    <section style="background-color:#D5F5E3; padding: 40px 20px 20px; border-top-style: solid; border-top-color: #2ECC71; border-top-width: 5px;">
    <h2>
        Recent Jobs at {{ post.title }}
    </h2>
    {% set job_args = { post_type: 'job', posts_per_page: 5, relationship: {id: '69', to: post.ID} } %}
        {% set jobs = mb.get_posts( job_args ) %}
        {% for job in jobs %}
                <li><a href="{{ mb.get_the_permalink( job.ID ) }}"><strong>{{ job.post_title }}</strong></a></li>
            {% endfor %}
            <p style="padding-top: 30px;">
            <strong>For other related Jobs, please visit <a href="https://nviewscareer.com/job/">Jobs</a></strong>
        </p>
        </section>

    You have to assign the location to the Employer emp post type.

    And if you use the shortcode [jobs-by-employers], please make sure that it is put in a loop to query the Employer emp posts.

    Just to make sure that post.ID or get_the_ID() will return an current/available emp post ID.

    #31804
    Prabakaran Shankar
    Participant

    Hello,

    Thank you very for the suggestion.

    I have checked the first 2 suggestions. 1st case, I have assigned to emp post type only. In 2nd case, it is inside the loop (based on the template part).

    For your kind information, the query in the error shows "wp_1p_posts.ID ORDER BY wp_1p_posts.post_date DESC LIMIT 0, 5". Is this referred posts_per_page is 5?

    If so, I have kept posts_per-page => 10.

    One more, I have noticed that after deleting the posts, the relationships data are not flushing out. it is stored in the DB table.

    Just giving some known information to debug it.

    Thanks.

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