Help with a multiple left join in DataTables editor

concettodg · April 2019

There have been some similar questions and answers before, but nothing that I have found quite gets to the point for me. I am nearly positive that my problem is that I am doing a "many to one" left join and that I don't get a unique table/alias. When I try to alias, I am running into the same problem.

I am getting the following error message: DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'hashtag'

I am using two files:
1. review.php which is within the controllers folder;
2. form_11.php which is where my HTML and DATATABLES output occurs.

REVIEW.PHP is as follows:

<?php

include( "../lib/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'review', 'review_id' )
    ->fields(
        Field::inst( 'review.review_text' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A brief review text is required.' ) 
            ) ),
            
        Field::inst( 'review.article_id' )
        ->options( Options::inst()
            ->table( 'article' ) 
            ->value( 'article_id' ) 
            ->label( 'article_name' ) 
        )
        ->validator( Validate::dbValues() ),
        Field::inst( 'article.article_name' ),
         // feeder table name to appear

// HERE IS INFO FOR MY FIRST JOIN: 

        Field::inst( 'review.hashtag_id_1' )
            ->options( Options::inst()
            ->table( 'hashtag' ) 
            ->value( 'hashtag_id' ) 
            ->label( 'hashtag' ) 
        )
        ->validator( Validate::dbValues() ),
        Field::inst( 'hashtag.hashtag' ),


// HERE IS INFO FOR MY SECOND JOIN: 

        Field::inst( 'review.hashtag_id_2' )
            ->options( Options::inst()
            ->table( 'hashtag' ) 
            ->value( 'hashtag_id' ) 
            ->label( 'hashtag' ) 
        )
        ->validator( Validate::dbValues() ),
        Field::inst( 'hashtag.hashtag' )
    )
    ->leftJoin( 'article', 'article.article_id', '=', 'review.article_id' )
    ->leftJoin( 'hashtag', 'hashtag.hashtag_id','=', 'review.hashtag_id_1')
    ->leftJoin( 'hashtag', 'hashtag.hashtag_id','=', 'review.hashtag_id_2')
    ->process( $_POST )
    ->json();

FORM_11.PHP


<!DOCTYPE html>  
<!-- review an article --> 
 <html>  
      <head>  
           <title></title>  
           <!--jQuery and Bootstrap-->
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" /> 

          <!--DataTables jQuery markup-->
           <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
           <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>  
           <script src="https://cdn.datatables.net/buttons/1.5.4/js/dataTables.buttons.min.js"></script>  
           <script src="https://cdn.datatables.net/select/1.2.7/js/dataTables.select.min.js"></script>  
           <script src="datatables/js/dataTables.editor.min.js"></script>   
           <script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>     


           <!-- styles -->
           <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />  
           <link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css"/>
           <link rel="stylesheet" href="https//cdn.datatables.net/buttons/1.5.4/css/buttons.dataTables.min.css"/>
           <link rel="stylesheet" href="https://cdn.datatables.net/select/1.2.7/css/select.dataTables.min.css"/>
           <link rel="stylesheet" href="datatables/css/editor.dataTables.min.css"/>
           <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.0/themes/base/jquery-ui.css"/>


          <!-- tinyMCE -->
            <script src="//tinymce.cachefly.net/4.2/tinymce.min.js"></script> 
            <script>tinymce.init({selector: "textarea"});</script>
            <script>//tinymce.init({selector: "tr"});</script>
            <script src="//tinymce.cachefly.net/4.2/editor.tinymce.js"></script> 

          <!-- ckEditor -->
             <script src="https://cdn.ckeditor.com/4.7.1/standard/ckeditor.js"></script>
             <script src="//cdn.tinymce.com/4/tinymce.min.js"></script>


      </head> 

      <body>  
           <br /><br />  
           <div class="container">  
                <h3 align="center"></h3>  
                <br />  
                <div class="table-responsive">  
                    <table id="example" class="display" cellspacing="0" width="50%">
                        <thead>
                            <tr>
                                <th></th>
                                <th>Review Text:</th>
                                <th>Article ID:</th>
                                <th>Hashtag:</th>
                                <th>Hashtag 2:</th>
                        </thead>
                    </table>
                </div>  
           </div>  
      </body>  
 </html>  


//ckeditor omitted for pace
//autocomplete omitted for space

 <script>


var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "datatables/controllers/review.php",
        table: "#example",
        fields: [ {
                    label: "Review Text:",
                    name: "review.review_text",
                    type: "ckeditor"
                  },
                  
                  {
                    label: "Article ID:",
                    name: "review.article_id",
                    type: "select"
                  },

                  {
                    label: "Hashtag:",
                    name:  "review.hashtag_id_1",
                    type: "autoComplete"
                  } ,
/
                {
                    label: "Hashtag 2:",
                    name:  "review.hashtag_id_2",
                    type: "autoComplete"
                  }   
        ]
    } );
 
    // Activate the bubble editor on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.bubble( this );
    } );
 
    $('#example').DataTable( {
        dom: "Bfrtip",
        scrollY: 1000, //provides length of scroll
        //paging: false, //when false, all shows up, otherwise, you get pages
        scrollCollapse: true, //this will make it look like a table with a footer no matter how many entries you have, 
        ajax: "datatables/controllers/review.php", //
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false,
            },
            
            { data: "review.review_text" },
            { data: "article.article_name", editField: "review.article_id" },
            { data: "hashtag.hashtag", editField: "review.hashtag_id_1" },
            { data: "hashtag.hashtag", editField: "review.hashtag_id_2" },

        ],
        order: [ 1, 'asc' ],
        select: {
            style:    'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );


</script>

allan · April 2019

I think the key here will be to use:

    ->leftJoin( 'hashtag as ht1', 'ht1.hashtag_id','=', 'review.hashtag_id_1')
    ->leftJoin( 'hashtag as ht2', 'ht2.hashtag_id','=', 'review.hashtag_id_2')

Then update your two Field instances for the hashtag to be:

 Field::inst( 'ht1.hashtag' )
// and
 Field::inst( 'ht2.hashtag' )

You'll also need to update your Javascript in the table renderer to reflect the slightly changed names.

Allan

concettodg · April 2019

Allan, that fixed the issue. I'm very grateful for you.

Help with a multiple left join in DataTables editor

Help with a multiple left join in DataTables editor

Answers

Howdy, Stranger!

Categories

DataTables

Help with a multiple left join in DataTables editor

Help with a multiple left join in DataTables editor

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables