Do join in a query update

nico077

Hi,

$editor->db()
->field('nature_de_frais.se')
->query('update','budget')
->set('budget.id_vu_dgp',1)
->where('budget.id',$id, "=")
->where('budget.id_vu_dgp',2, "=")
->where('nature_de_frais.se',$row ["nature_de_frais"]["se"], "!=")
->exec();

I have an error because 'nature_de_frais.se' doesn't exist, i need to do a join, like that :

LEFT JOIN nature_de_frais ON nature_de_frais.id_budget = budget.id

I have try 2 methods:
->leftJoin( ... ) and ->join( ... )
And theyt don't work, i have an error

allan

The join method is the one to use. Can you show me the code you tried with that and also the error message please?

Allan

nico077

The code with the join method :

->on( 'postEdit', function($editor, $id, $values, $row) {
      $editor->db()
            ->query('update','budget')
            ->join('nature_de_frais','budget.id = nature_de_frais.id_budget', 'LEFT' )
            ->set('budget.id_vu_dgp',1)
            ->where('budget.id',$id, "=")
            ->where('budget.id_vu_dgp',2, "=")
            ->where('nature_de_frais.se',$row ["nature_de_frais"]["se"], "!=")
            ->exec();
})
->process($_POST)
 ->json();

And when i want edit a field, i have that :

allan

And can you show me the schema for those two tables please?

Also, add ->debug(true) immediately before the ->process($_POST) if you would be so kind. Then show me the JSON returned from the server (it will show the generated SQL).

Allan

nico077

The previous developper hasn't update Datatable, the current version for the project is 1.5.5, so ->debug(true) doesn't exist.

The schema :

The JSON returned from the server :

nico077

Finally I succeeded, the problem came from the update, i have do that :

->on( 'preEdit', function($editor, $id, $values) {
    $editor->db()
     ->query('update','budget')
     ->set('budget.id_vu_dgp',1)
     ->where('budget.id_vu_dgp',2, "=")
     ->where( function ( $q ) use ( $id, $values) {
          $q->where( 'budget.id', '(
          SELECT id_budget 
          FROM nature_de_frais 
          WHERE id_budget = '. $id .' 
          AND se != '. $values ["nature_de_frais"]["se"] .'
          )', 'IN', false );
     })
     ->exec();
})
->process($_POST)
->json();