Do join in a query update

Do join in a query update

nico077nico077 Posts: 55Questions: 14Answers: 2

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

Answers

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • nico077nico077 Posts: 55Questions: 14Answers: 2

    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 :

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • nico077nico077 Posts: 55Questions: 14Answers: 2

    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 :

  • nico077nico077 Posts: 55Questions: 14Answers: 2

    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();
    
This discussion has been closed.