October: Model returning null values?

[tyamz]

NOTE: Please DO NOT delete this again, I have had this question posted on both October's forum and Stack Overflow for at least a month, and there is not going to ever be an answer on either of them because clearly it is an issue with OctoberCMS. My goal in posting this here is showing that there is an issue. If you plan to delete this, at least find someone who can help me with this, this is a real issue and nobody on October's forum or Stack Overflow knows how to fix it (obviously because it hasn't been answered in a month already).

The problem that I'm having is, I cannot access certain information from the File Model such as File Path, File Name, and other information stored in the system_files table. I believe I have pinpointed where the issue is coming from, but I'm not sure how to fix it. Below I'll show some of the code.

1) File.php ~ File Model

    <?php namespace Compuflex\Downloads\Models;
    use Model;
    use October\Rain\Database\Attach\File as FileBase;
    /**
     * Model
     */
    class File extends FileBase
    {
        use \October\Rain\Database\Traits\Validation;
        /*
         * Validation
         */
        public $rules = [
        ];
        /*
         * Disable timestamps by default.
         * Remove this line if timestamps are defined in the database table.
         */
        public $timestamps = false;
        /**
         * @var string The database table used by the model.
         */
        public $table = 'compuflex_downloads_files';
        public $attachOne = [
                'file' => ['System\Models\File', 
                    'key' => 'id']
        ];

        public $belongsToMany = [
                'user' => ['RainLab\User\Models\User']
        ];
    }

In this file, you can see there are two relationships identified: $attachOne which attaches the file upload to the File Model as well as $belongsToMany which identifies the many-to-many relationship between users and files. (Many files can belong to Many users, or one user can have many files, but one file can also belong to many users). This is the file that sets up the file and user relation which you will see in the columns.yaml file. Just a side note, the only reason I set the 'key' =>'id' was to ensure that it was actually identifying the 'key' as 'id'.

2) Columns and Fields

I don't think the fields.yaml file is necessary, but just in case, you can find it here:
fields.yaml

columns.yaml

columns:
    file_name:
        label: 'compuflex.downloads::lang.file.name'
        type: text
        searchable: true
        sortable: true
    full_name:
        label: 'compuflex.downloads::lang.file.username'
        type: text
        searchable: true
        sortable: true
        select: name
        relation: user
    email:
        label: 'compuflex.downloads::lang.file.email'
        type: text
        searchable: true
        sortable: true
        select: email
        relation: user
    file:
        label: 'compuflex.downloads::lang.file.path'
        type: text
        searchable: true
        sortable: true
        select: file_name
        relation: file

I'm more concerned with the columns.yaml file because that is what I am trying to fix is the back-end controller that displays the list of file information as well as information about the users they're "attached" to.

Here is a screenshot of what the view looks like:

As you can see, the "File" Tab is not displaying any information about the file, and it is supposed to be displaying the file_name column from the system_files table or at least from the File Model, but it displays nothing.

Now, what I find interesting, if I change the last entry in the columns.yaml file from the original to:

file:
    label: 'compuflex.downloads::lang.file.path'
    type: text
    searchable: true
    sortable: true

Then the following is output in the back-end controller:

Therefore, as you can see, the information IS there (allegedly), but I'm just not sure how to access it properly.

So, I have one last test that I did that I will show you, I knew that if the select: attribute in the columns.yaml file is set to a column name that's not in the system_files table it should produce an SQL Error of some sort (such as column name not found). And I was right... So, I set it to 'ile_name' instead of file_name, just for testing purposes.

Here is what the error message was:

SQLSTATE[HY000]: General error: 1 no such column: ile_name (SQL: select "compuflex_downloads_files".*, (select group_concat(name, ', ') 
from "users" inner join "file_user" on "users"."id" = "file_user"."user_id" where "file_user"."file_id" = 
"compuflex_downloads_files"."id") as "full_name", (select group_concat(email, ', ') from "users" inner join "file_user" on "users"."id" = 
"file_user"."user_id" where "file_user"."file_id" = "compuflex_downloads_files"."id") as "email", (select ile_name from "system_files" 
where "system_files"."attachment_id" = "compuflex_downloads_files"."id" and "system_files"."attachment_type" = ? and "field" = ?) as 
"file" from "compuflex_downloads_files" order by "file_name" desc)

Now, to pinpoint the issue more, I believe the issue might be coming from:

(select ile_name from "system_files" where "system_files"."attachment_id" = "compuflex_downloads_files"."id" and "system_files"."attachment_type" = ? and "field" = ?)

I think that those ? are causing this query to return NULL results from the table in turn causing there to be no output. I'm not 100% on this, it's just one possibility. The problem is, I don't know where OctoberCMS builds these queries or if there is any simple way to fix that without touching the actual code for October (for obvious reasons).

Just a note: I apologize if I've included TOO much information here, but I have tried to cut it down. I just wanted to show you that I DID try to troubleshoot and solve this myself, but to no avail.

October build

(419)

[LukeTowers]

@tyamz Thank you for the detailed report.
? in MySQL refers to parametrized queries, I.e queries that are built without the values present, and then the values are bound to the query at time of execution. This is the foundation of how SQL injection attacks are prevented.

Use $query->getBindings() to get the values that will be bound to the query at time of execution for further debugging.

[tyamz]

@LukeTowers Where would I put $query->getBindings()? Sorry if that is a dumb question, I'm just unsure.

[LukeTowers]

Wherever you are currently debugging the query. You could probably put it in your controller's listExtendQuery method like so dd($query->getBindings()) to see what it's looking for

[LukeTowers]

and also pair that with dd($query->toSql()) I believe is how you get the SQL from a query object

[tyamz]

@LukeTowers
Okay, if I put dd($query->toSql()) I get this back:
"select "compuflex_downloads_files".*, (select group_concat(name, ', ') from "compuflex_company_" inner join "company_file" on "compuflex_company_"."id" = "company_file"."company_id" where "company_file"."file_id" = "compuflex_downloads_files"."id") as "name", (select file_name from "system_files" where "system_files"."attachment_id" = "compuflex_downloads_files"."id" and "system_files"."attachment_type" = ? and "field" = ?) as "file" from "compuflex_downloads_files" order by "file_name" desc"

And if I put dd($query->getBindings()) I get this:

Which I'm guessing means that ? is not getting a value for some reason.

[LukeTowers]

I can't provide step by step instructions for going through this debug process, but I can provide an explanation of some of the tools I pointed you to:
dd() is the dump and die function. It will dump the contents of the variable that it is passed to the screen and then immediately halt execution at that point.
$query is the query object. The listExtendQuery($query) function is called by the ListController behavior when it is building its query to be run to generate the list. It's possible that this method is too early in the request cycle to get the information that you require.

As a side note, although in theory the select: file_name should work, are you able to achieve what you need by using a column of type partial and then in that partial using the following:

<?= $record->file->getPath(); ?>

[tyamz]

@LukeTowers using that last part as a Partial worked. I will continue using that for now.

[tyamz]

@LukeTowers
Okay, so I've looked into this a bit more. I tried running the following query on my SQLite GUI Browser:
select "compuflex_downloads_files".*, (select group_concat(name, ', ') from "compuflex_company_" inner join "company_file" on "compuflex_company_"."id" = "company_file"."company_id" where "company_file"."file_id" = "compuflex_downloads_files"."id") as "name", (select file_name from "system_files" where "system_files"."attachment_id" = "compuflex_downloads_files"."id" and "system_files"."attachment_type" = ? and "field" = ?) as "file_path" from "compuflex_downloads_files" order by "file_id" desc;

And in return I got this:

And I also tried this query:
select "compuflex_downloads_files".*, (select group_concat(name, ', ') from "compuflex_company_" inner join "company_file" on "compuflex_company_"."id" = "company_file"."company_id" where "company_file"."file_id" = "compuflex_downloads_files"."id") as "name", (select file_name from "system_files" where "system_files"."attachment_id" = "compuflex_downloads_files"."id" and "system_files"."attachment_type" = "Compuflex\Downloads\Models\File" and "field" = "file") as "file_path" from "compuflex_downloads_files" order by "file_id" desc;

And in return I got this:

Now, I realize that October SHOULD be passing values back to the query rather than just ?, but what if those values are actually null. So, instead of using dd($query->getBindings()); to debug, I decided to try dd($query->getRawBindings()); which returned:

[jan-vince]

Hi @tyamz

I am using selects from system_files in many of my plugins but mostly to show a preview image.

I have tried your config and it didn't worked for me - but I don't know why. It returns zero lines.

This is my config:

preview_image: label: 'Image file name' relation: preview_image select: file_name
My relation name is preview_image where you have file.

But THIS WILL work fine:

columns.yaml
preview_image: label: 'Image file name' relation: preview_image type: file_name

Plugin.php
php public function registerListColumnTypes() { return [ 'file_name' => function($value) { if( !empty($value->file_name) ) return $value->file_name; else return null; }, ]; }

The only difference here is that I am manually doing a select so I can format it as I want (like put a path into an <img> tag.

I have never noticed this relation doesn't work when used with select in columns.yaml.

I will try to find where a problem is when I have a time!

[jan-vince]

So the answer was hidden in the documentation.

This works for me:

preview_image: label: 'Image file name' relation: preview_image valueFrom: file_name

Still don't know why select: file_name not work. Maybe @LukeTowers knows?

[LukeTowers]

@jan-vince @tyamz I think the answer is because you're overwriting the value of preview_image with your column, so if you did

preview_image_file_name:
    label: 'Image File Name'
    relation: preview_image
    select: file_name

it should work.