Where clause on an Mjoined field?

Where clause on an Mjoined field?

TronikTronik Posts: 122Questions: 28Answers: 1

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin

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

    Thanks,
    Allan

  • TronikTronik Posts: 122Questions: 28Answers: 1
    ->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

  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin

    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

  • TronikTronik Posts: 122Questions: 28Answers: 1
    Answer ✓

    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

Sign In or Register to comment.