Where clause on an Mjoined field?

Tronik

Im trying to use ->where on a field from a table which is mjoined / linked, but the field is not recognized by datatables:
SQLSTATE[42S22]: Column not found: 1054 Unknown column

When I use the ->debug, I find that the column is not even present in the SQL query, so it makes sense that the error is thrown. I guess the mjoined table is joined by a separate query

The column works otherwise (shown in table)

Is it possible to achieve this?

allan

Can you show me the PHP you are using and also the debug SQL result please?

Thanks,
Allan

Tronik

->join(            
            Mjoin::inst( 'files' )
                ->link( 'products.id', 'products_images.products_id' )
                ->link( 'files.id', 'products_images.files_id' )
                ->fields(
                    Field::inst( 'products_images.files_id' )
                    ->set(false)
                    ->name('id'),
                    Field::inst( 'filename_download' )
                    ->set(false),
                    Field::inst( 'filesize' )
                    ->set(false),
                    Field::inst( 'filename_disk' )
                    ->set(false)
                    
                )
        )
.........

->where( 'products_images.files_id', '=',null)
->process( $_POST )
->json();

The debug response would be very big to paste here, but the gist is that none of the above joined fields are present in the SELECT query that is made.
Mjoined fields must be joined to the table some other way?

I've tried changing the where column to different column name like 'files' and so on.
The field is used in js like:

{
  "data": "files"
}

it works in the table but I cannot use a where statement on it

allan

You need to put the where statement on the Mjoin chain - i.e.:

            Mjoin::inst( 'files' )
                ->link( 'products.id', 'products_images.products_id' )
                ->link( 'files.id', 'products_images.files_id' )
                ->where( 'products_images.files_id', '=',null)

It should be noted that it doesn't restrict the rows returned at the top level though - all rows from whatever your main table is would still be returned.

Allan

Tronik

Yes, which is not what I want, I need the parent table to return only the rows where the condition is met.

I ended up creating a VIEW which I leftjoined, then I can use the where statement