Error in Upload Many from DB: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id'

giovanni.bracci

I got this error trying to implement Upload Many example.

In my DB i have 3 tables:

Table: files

+----+----------+--------------+-----------+----------------+-------------------+
|  id  |  name  |  filename  |  filesize  |  web_path  |  system_path  |
+----+----------+--------------+-----------+----------------+-------------------+

Table: users

+----+----------+---------+-----+-----+-----+
|  id  |  field1  |  field2  |  ...  |  ...  |  ...  | ...
+----+----------+---------+-----+-----+-----+

Table: instruments

+----+----------+---------+-----+-----+-----+
|  id  |  field1  |  field2  |  ...  |  ...  |  ...  | ...
+----+----------+---------+-----+-----+-----+

Here is my JS on EDITOR

            }, {
                label: "Images:",
                name: "files[].id",
                type: "uploadMany",
                display: function ( fileId, counter ) {
                    return '<img src="'+editor.file( 'files', fileId ).web_path+'"/>';
                },
                noFileText: 'No images'
            }

Here is my JS on TABLE COLUMNS

            {
                data: "files",
                render: function ( d ) {
                    return d.length ?
                        d.length+' image(s)' :
                        'No image';
                },
                title: "Image"
            }

Here is my SERVER SIDE SCRIPT

<?php

// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;


$xrm = $_POST['xrm'];

Editor::inst( $db, 'instruments' )
    ->field(
        Field::inst( 'instruments.serial' ),
        Field::inst( 'instruments.producer' )
            ->options( Options::inst()
                ->table( 'producers' )
                ->value( 'id' )
                ->label( 'producer' )
            )
            ->validator( Validate::dbValues(
                ValidateOptions::inst()
                    ->message( 'Produttore non a DB' ),
                'producer',
                'producers'
            ) ),
        Field::inst( 'producers.producer' ),
        Field::inst( 'instruments.name' ),
        Field::inst( 'instruments.country' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'countryName_it' )
            )
            ->validator( Validate::dbValues(
                ValidateOptions::inst()
                    ->message( 'Paese non a DB' ),
                'countryName_it',
                'countries'
            ) ),
        Field::inst( 'countries.countryName_it' ),
        Field::inst( 'instruments.yearOfProduction' )
            ->validator( Validate::numeric('.', ValidateOptions::inst() ->message('Inserire soltanto valori numerici')))
            ->validator( Validate::minLen( 4, ValidateOptions::inst() ->message('Input non valido') ) )
            ->validator( Validate::maxLen( 4, ValidateOptions::inst() ->message('Input non valido') ) )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( 'instruments.instcat_id' )
            ->options( Options::inst()
                ->table( 'instcat' )
                ->value( 'id' )
                ->label( 'category' )
            ),
        Field::inst( 'instcat.category' ),
        Field::inst( 'instruments.purchaseDate' )
            ->validator( Validate::dateFormat('d/m/Y') )
            ->getFormatter( Format::dateSqlToFormat('d/m/Y') )
            ->setFormatter( Format::dateFormatToSql('d/m/Y') ),
        Field::inst( 'instruments.color' ),
        Field::inst( 'instruments.owner_id' )
            ->options( Options::inst()
                ->table( 'users' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'users.name' ),
        Field::inst( 'users.surname' ),
        Field::inst( 'instruments.stolen' ),
        Field::inst( 'instruments.description' ),
        Field::inst( 'instruments.public_id' ),
        Field::inst( 'instruments.pending' ),
        Field::inst( 'instruments.pendingTo' )
    )
    ->where( function ( $q ) use ( $xrm ) {
        $q->where( function ( $r ) use ( $xrm ) {
            $r->where( 'owner_id', $xrm );
            $r->or_where( function ( $s ) use ( $xrm ) {    
                $s->where( 'pending', '1' );
                $s->and_where( 'pendingTo', $xrm );
                } );
            } );
        } )
    ->join(
        Mjoin::inst( 'files' )
            ->link( 'users.id', 'users_files.user_id' )
            ->link( 'files.id', 'users_files.file_id' )
            ->fields(
                Field::inst( 'id' )
                    ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/upload/__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
                        ) )
                        ->validator( Validate::fileSize( 500000, 'Files must be smaller that 500K' ) )
                        ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
                    )
            )
    )
    ->leftJoin( 'producers', 'producers.producer', '=', 'instruments.producer' )
    ->leftJoin( 'countries', 'countries.countryName_it', '=', 'instruments.country' )
    ->leftJoin( 'instcat', 'instcat.id', '=', 'instruments.instcat_id' )
    ->leftJoin( 'users', 'users.id', '=', 'instruments.owner_id' )
    ->process($_POST)
    ->json();

The error is: DataTables warning: table id=instrum - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause'

Obviously it doesn't exist an user_id field in my users table. But the example say: ->link( 'users.id', 'users_files.user_id' )

What am I doing wrong???

allan

I don't quite understand these lines:

            ->link( 'users.id', 'users_files.user_id' )
            ->link( 'files.id', 'users_files.file_id' )

If you don't have a users_files table? Could you confirm if you do or not? If you don't, you don't need an Mjoin - since it would then just be a 1:1 relationship.

Allan

giovanni.bracci

Hi Allan,

Actually, I don't have users_files table but I can create it if needed.

The strange thing is that even if I create the table I got the same error but the error refers to users.user_id (that doesn't exist in my db) and not users_files.user_id.
The error is still: DataTables warning: table id=instrum - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause'

What I want to do is to upload one or more image and I was trying to do the same as in the "Editor example File upload (many)".
URL: https://editor.datatables.net/examples/advanced/upload-many.html

I can't understand which table do I need to create on database and I cannot understand why the code of the example gives me the error I mentioned here and on my previous post.

What am I missing?
Could you please help me?

Thank you in advance.

Giovanni

giovanni.bracci

I'm a stupid. I was pointing to the wrong table. Everything's working.