Help with a multiple left join in DataTables editor

Help with a multiple left join in DataTables editor

concettodgconcettodg Posts: 2Questions: 1Answers: 0

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>

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • concettodgconcettodg Posts: 2Questions: 1Answers: 0
    edited April 2019

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

This discussion has been closed.