Uploadmany without n:m table

Uploadmany without n:m table

rheinertprheinertp Posts: 23Questions: 4Answers: 0

Hi,
is it possible to use the uploadmany type without having a n_m table - but just with a field
that contains the ids of the images - separated by a separator.

I am thinking about a comparable way as to define a multi selection using "select2" (or "selectize").
i.e.

Image table as in your examples
i.e. "usertable" with fields: firstname - name - age - images
The field "usertable.images" being a varchar - containing the references to the image-ids - e.g. "1;3;5"
for the images with the ids 1,3,5.

Is this possible?
Thank you very much!

Replies

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Yes, that is possible. The value expected and returned by uploadMany is an array, so you could just give it [ 1, 2, 3, ... ].

    If you want to store that as a string in the database then you could use the [implode and explode formatters](https://editor.datatables.net/manual/php/formatters#Arrays0 (for set and get) to convert from the string form to array and back.

    One important point to consider is that you aren't going to get referential integrity doing this. If you delete an image the id referencing it would still exist in the linking string. For that reason alone I wouldn't recommend doing this.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0

    Thank you for your quick response. The problem is that only the first id seems to be taken from the table -resulting in an error:

    Uncaught Unknown file id 4 in table ...

    But if I have image 4 linked (in another line) then it works.

    Maybe it is better to use the "n:m"-table - like in your expample:

     ->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" ) )
                        )
                )
        )
    

    Question:
    How can I realize that I only have ONE n:m table - linking different tables to the (one) image table?

    Maybe by adding an element like
    ->link('n_m_files.tablename', 'users' ) for the user-table
    and
    ->link('n_m_files.tablename', 'buildings' ) for the buildings-table

    (Important: When writing the data into the "n_m_files"-table the tablename also must be written - to get the connection.

    Thanks so much!

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    How can I realize that I only have ONE n:m table - linking different tables to the (one) image table?

    The problem with having just a single table comes back to referential integrity - using a table such as users_files means you can have a foreign key reference to the users and files tables - delete from either and it will remove from the link table.

    If you just had a files_host table, you could foreign key link to the files table, but not to the users (and buildings) tables. You could do that - just make the host field an integer to store the id, but I would very much recommend against it.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0

    Hi Allan,
    tanks again for your help. I split up the text by a separator (;) and it works.
    But I only get those images from the table within the json-response that
    correspond to the first id in the dependant table.

    Here is the code:

    Field::inst( 'tblKunde.Logo' )->setFormatter( function ( $val, $data, $opts ) {
                            return $val ? (is_array($val) ? implode(";", $val) : $val) : null;
                        } )->getFormatter( function ( $val, $data ) {
                            return $val ? explode(";", $val) : null; // (strpos($val, ";")>0 ? explode(";", $val) : [$val]) : null;
                        } )
            ->upload( Upload::inst( $this->filepath.'__ID__.__EXTN__' )
                ->db('tblcentralform_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
                ) )/*
                ->dbClean( function ( $data ) {
                   for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
                     unlink( $data[$i]['system_path'] );
                   }                 
                 return true; 
                 } ) */
                ->validator( Validate::fileSize( 500000, 'Files must be smaller than 500000' ) )
                ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload only 'png', 'jpg', 'jpeg', 'gif'" ) )
               )
              )
    ->leftJoin( $dbname.'.tblcentralform_files AS tblcentralform_files', $dbname.'.tblcentralform_files.id', '=',  $datatable.'.Logo'  )
    

    In tblKunde.Logo I got a reference to 2 images: content is "2;3"
    In the response json I got:

    ...,"files":{"tblcentralform_files":{"2":{"id":"2","filename":"download.png",
    "filesize":"3081","web_path":"2.png",
    "system_path":"/files\/2.png"}}},"draw":1,"recordsTotal":"6",
    "recordsFiltered":"6"}
    

    What do I have to do to get the json-"files" of all of the elements in the "tblcentralform_files" table (and not only those with a id that is at first place in the tblKunde.Logo field).
    Ideally I would get all files that are within the Logo-string - something like
    ... where tblKunde.Logo LIKE Concat("%", 'tblcentralform_files.id, "%")

    Thanks so much for your support!
    Pascal

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Hi Pascal,

    The Upload class has a where method so you could use:

    ->where( function ($q) {
      $q->where(...);
    } );
    

    However, its important to that you wouldn't be able to perform the query with another database record as the value to check against like you have above. You'd need to use a known value such as $_POST['...'] (assuming you are posting the id you want.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0
    edited January 2020

    Hi Allan,
    ->where just seems to further reduce the results (which is logical..)
    Here is the part of the return json...

    {"query":"SELECT  `id` as 'id', `filename` as 'filename', `filesize` as 'filesize', `web_path` as 'web_path', `system_path` as 'system_path' 
    FROM  `tblcentralform_files` WHERE `id` IN 
    (:wherein1, :wherein2) AND (`id` > :where_2 )","bindings":
    [{"name":":wherein1","value":"2;3","type":null},
    {"name":":wherein2","value":"4;5","type":null},
    {"name":":where_2","value":"0","type":null}]
    

    As you see: id is 1, 2, 3, 4, 5
    Reference is 2;3 and 4;5
    Meaning 4 and 5 does not exist for him - and the "AND (id>0) does not help.

    There does not seem to be an "->orWhere" creating a SQL like

    WHERE `id` IN (:wherein1, :wherein2) OR (`id` > :where_2 ) ...
    

    This would do the trick ..

    Do you have any idea how I can get all the values from the table? (splitting up the field with explode (and implode when writing back) works without problems ...

    Thanks,
    Pascal

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    HI Pascal,

    Its or_where() in the Editor PHP DB libs.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0

    Hi,
    sorry to bother you again.
    If I put

            ->upload( Upload::inst( $this->filepath.'__ID__.__EXTN__' )
                ->db('tblcentralform_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
                ) )
                ->where( function ($q) {
                      $q->or_where('id','0','>');
                    } )
    

    it is still " AND " (maybe it is the first where that defines and / or - and if I try

            ->upload( Upload::inst( $this->filepath.'__ID__.__EXTN__' )
                ->db('tblcentralform_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
                ) )
                ->or_where( function ($q) {
                      $q->where('id','0','>');
                    } )
    

    there is an error as or_where does not exist for uploads...

    What do I wrong?
    Thanks,
    Pascal

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    You are correct - the outer where will be an AND - but since it is the first and only where condition I don't really understand why there would be a combination operator at all? It should just be WHERE id > 0 from the above code.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0

    I think this is due to the upload class - which internally does the first

    WHERE `id` IN (:wherein1, :wherein2)
    

    (linking the images-table to the table which contains the reference to the id field.
    In my case - I "misuse" the refenerce to the id by a string containing the ids (separated by ; ). I explode them when "getting" and do an "implode" when setting - which works fine.

    But - editor (upload) does only grab the files that are within one of these ids.
    i.e. it takes id=2 and id=3 - BUT not id=4 and id=5
    because the references are 2;4 and 3;5.

    The second "where" was a try to get the entries from the image-table (even though they are not connected - because of the string..

    Maybe there is another was to do it?
    Unfortunately the upload does not have the function ->or_where (but just ->where..

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Ah I see - yes, there is that built in WHERE IN which makes use of the referential integrity of the database to ensure that only the required records are obtained. Since you aren't using that in your database unfortunately the Upload class isn't going to work out of the box for you.

    It's this part of Upload.php that would need to be changed to match your requirements.

    For that reason, I'd come back to my original suggestion to use the referential integrity of the database, and have a link table rather than trying to address multiple files in a single text field.

    Allan

  • rheinertprheinertp Posts: 23Questions: 4Answers: 0

    Thanks for that hint. I just commented out the line ("$q->where_in( $this->_dbPKey, $ids );) and it works. But of course it is not really good as it grab all elements now.

    But there is no such
    $q->where_in_like that could help getting the elements that contain the ids?

    Maybe this is a flexible way to avoid n:m tables.
    Thanks, Pascal

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Honestly - you'll run into major performance issues doing that. There is no reason why you couldn't add that to the libraries, but with any significant size of database it is going to run really slowly.

    I don't understand the reticence to use a link / join / junction (call it what you like) table? It's the supported way, its performant and makes sense in SQL.

    Allan

This discussion has been closed.