Editor is querying for a column that doesn't exist, but I also didn't specify that column

stevevance

I am trying to develop an interface where one can upload an image. The image details are stored in a table called files. A "link" table called councilmatic_files_link keeps track of the links between an uploaded file and the content type it was uploaded to represent (in this case here, a graphical rendering of a property zoning change in a major city). Those zoning change records are stored in a table called councilmatic.

I have tried many iterations of the upload and uploadMany techniques in the Examples, to no avail. I was successful
in adding a files component to the councilmatic table but that's not what I want in the end. I want each file uploaded to have additional information recorded, like the file author, and a URL for where the file came from.

Link to DataTables debugger

Here's my PHP server-side code:

$editor = Editor::inst( $db, 'files', 'id' )
    ->fields(
        Field::inst( 'councilmatic.gid' ),
        Field::inst( 'files.url' ),
        Field::inst( 'files.author' )
    ->leftJoin( 'councilmatic', 'councilmatic.gid', '=', '5259' ) // this is here to load only those images that apply to the zoning change record with the GID of 5259 (although it may be redundant)
    ->leftJoin( 'councilmatic_files_link', 'councilmatic.gid', '=', 'councilmatic_files_link.target_id' )
    ->join(
        Mjoin::inst( 'files' )
            ->link( 'councilmatic.gid', 'councilmatic_files_link.target_id' )
            ->link( 'files.id', 'councilmatic_files_link.file_id' )
            ->fields(
                Field::inst( 'id' )
                    ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/uploads/councilmatic_'.'__ID__.__EXTN__' )
                        ->db( 'files', 'id', array(
                            'filename'    => Upload::DB_FILE_NAME,
                            'filesize'    => Upload::DB_FILE_SIZE,
                            'web_path'    => Upload::DB_WEB_PATH,
                            'system_path' => Upload::DB_SYSTEM_PATH,
                            'mime_type'     => Upload::DB_MIME_TYPE
                        ) )
                        ->validator( function ( $file ) {
                            return $file['size'] >= 5000000 ?
                                "Files must be smaller than 5 MB" :
                                null;
                        } )
                        ->allowedExtensions( array( 'png', 'jpg', 'jpeg', 'pdf' ), "Please upload an image or PDF" )
                )
            )
        
    )
    ->where("councilmatic.gid", "5259") // this is here to load only those images that apply to the zoning change record with the GID of 5259
    ->debug(true)
    ->process( $_POST )
    ->json();

Here's the JavaScript code:

function loadFilesEditor() {

    editor = new $.fn.dataTable.Editor( {
        ajax: '/php/editor.files.php',
        table: '#table_files',
        fields: [
            {
                label: "Author:",
                name: "files.author",
            },
            {
                label: "URL:",
                name: "files.url",
            },
            {
                label: "Images:",
                name: "files.id",
                type: "upload",
                display: function ( file_id ) {
                    return '<img src="'+table.file( 'files', file_id ).web_path+'"/>';
                },
                noFileText: 'No images'
            }
        ]
    } );

    var buttons = [
        { extend: 'create', editor: editor },
        { extend: 'edit',   editor: editor },
        { 
            text: "Reload",
            action: function ( e, dt, node, config ) {
                table.ajax.reload();
            }
        }
    ];

    table = $('#table_files').DataTable( {
        dom: 'Bf<"#tag_filter">rtipB',
        
        serverSide: true,
        processing: true,
        ajax: {
            url: '/php/editor.files.php',
            type: "POST"
        },
        //order: (admin === 1 ? [[3, 'asc'],[5, 'desc']] : [0, 'asc']),
        select: {
            style: 'os'
        },
        stateSave: false,
        columns: [
            {
                data: "files.url",
                title: "URL",
            },
            {
                data: "files.author",
                title: "Author",
                defaulContent: "",
            },
            {
                data: "files.web_path",
                render: function ( data ) {
                    return "<img src='" + data + "' class='img img-responsive'/>";
                },
                title: "Image",
                searchable: false
            },
            {
                data: "files.id",
                title: "id",
                visible: false,
                searchable: false
            }
        ],
        select: true,
        buttons: buttons
    } );

}

The latest error is:

An SQL error occurred: SQLSTATE[42703]: Undefined column: 7 ERROR:  column files.gid does not exist
LINE 1: ...s "id" FROM  files as files  JOIN councilmatic ON files.gid ...
                                                             ^
HINT:  Perhaps you meant to reference the column "files.id"

As you can see, files.gid is never requested by the PHP or the JavaScript.

Where is that column name coming from?

allan

I think Editor is getting confused by the fact that Editor is being initialised with the files table being the main table to edit (second parameter of the constructor) but it is also MJoin'ing to files.

Should the constructor actually be referencing councilmatic? What table is it that you want to show the records from in the DataTable (i.e. one record == one row)?

Thanks,
Allan

stevevance

I want the files table to be the primary table, where one record is one row is one file.

The DataTables should show all of the files that have been uploaded that are relevant to this zoning change record (which is what the councilmatic.gid=5259 is for).

If I change Editor to be initialized with the councilmatic table then the DataTables will load a single empty row, representing the record for "5259" in the councilmatic table.

If I change Editor to be initialized with the councilmatic_files_link table then the DataTables server-side script will have a similar error as the original (Undefined column: 7 ERROR: column councilmatic_files_link.gid does not exist LINE 1: ... as councilmatic_files_link JOIN councilmatic ON councilmat...).

tangerine

Do you have your query working in, say, PhpMyAdmin?

It might be useful to detach your SQL from the Editor syntax.

stevevance

@tangerine How do I see the SQL query?

tangerine

Actually I meant write and test your query without using Editor.
You could enable Editor's debug mode

 ->debug(true) 

But that's not likely to help as you already know your SQL is invalid.

allan

I'm not entirely clear on the use of an Mjoin in this case. If your main table is showing the files, trying to Mjoin to itself isn't really going to show the structure of the data (at least not that I can see).

You've got a left join to the councilmatic table - and pulling in the gid column from that table. I wonder if the best way here might be to use the RowGroup extension for DataTables. That way you get one row per file, but also get the grouping by the gid.

Or have I missed the point?

Allan

stevevance

@allan I forgot to link you to the example on my website.
https://www.chicagocityscape.com/ordinances2.php?ordinance=5259 (it only works with ordinance 5259 because that is hardcoded right now).

I want to use Editor to upload images that are associated with this ordinance. Normally, the "upload many" example that you have would work for what I want (because I implemented that successfully), but I want a file author and file URL fields to be associated with the file (so that I can give credit to the author).

@tangerine Because of the fatal error, the SQL won't get output.

tangerine

Can you write and test your query without using Editor?

allan

Thanks for the link - although I'm still not entirely sure I understand the need for the Mjoin. If you are showing all the files for a specific gid, its one file per row in the table, so you can just use a regular Field::inst( ) at the top level, just as you have done for Field::inst( 'files.url' ), for example.

If you wanted one row with multiple images, then yes, it would be correct to use Mjoin - i.e. a single gid which shows multiple images.

Allan

stevevance

I decided to go in a different direction, and I designed a new schema/structure. I have it working now. I ended up building a "master files" table and a "files info" table that are linked together. The "master files" table contains only the information about the file itself, while the "files info" table has the auxiliary information, like the file author, a source URL, a description of the file, and other information.

To link the zoning changes to the files, the "files info" table also has geometry information so the zoning changes and the files are compared to see if they are near each other. When I upload a file, I have to set the geometry, using some custom JavaScript functions that geocode addresses that I type in. The geocode results are then stored in the "files info" table.

If anyone wants the code I can post it on a gist.

@allan You can consider this post closed.