error when trying to use sql function combined with where statement

javismiles

Im trying to avoid creating a view and instead do this

    $editor->fields(
            Field::inst( 'SUM(imp.amount)', 'work.spend' )
//      ->set( false )
        ->where( function ($q) {
            $q->where( 'imp.type', '1', '=' );
        }   
        
        );

    $editor->leftJoin( 'imp', 'imp.workid', '=', 'work.id' );

but it just wont work, instead of sending all the rows it sends just one and it also gives error when trying to use the where statement

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

javismiles

this actually almost works except that it returns just 1 row instead of the many rows it was returning before

$editor->fields(
Field::inst( 'SUM(workimp.amount)', 'worksol.spend' ))
// ->set( false )
->where( function ($q) {
$q->where( 'workimp.type', '1', '=' );
});

that happens because in this specific case the left join necessary
$editor->leftJoin( 'workimp', 'workimp.solid', '=', 'worksol.solutionid' );

has many cases where there is nothing to match in that table,
how can I make sure that a left join doesnt constrain the rest of my joins,
that if a specific row doesnt have a match with that other table, the row is still returned?

allan

and it also gives error when trying to use the where statement

Yes, because there is no where() method on the Field object. It is only on the Editor object.

This is very similar to your other thread on this topic. Let's continue the discussion there.

Allan

javismiles

agreed, closing this one