Cascading list with leftjoin and Where clause

SWATswat · May 2021

Good morning,

how to make sure that the Where clause is taken into account, perhaps it is badly positioned?

Javascript :

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "Staff_Qualite.php",
        table: "#EditQualite",
        fields: [ {
                label: "Date :",
                name: "qualite_generale.date_qg",
                type: "datetime"
            }, {
                label: "Origine de la remontée :",
                name: "qualite_generale.origine_remontee_qg",
                type:  "select",
                placeholder: "Selectionner une origine",
                options: [
                    { label: "Interne", value: "Interne" },
                    { label: "Externe", value: "Externe" }
                ]
            }, {
                label: "Importance :",
                name: "qualite_generale.importance_qg",
                type:  "select",
                placeholder: "Selectionner une Importance",
                options: [
                    { label: "Basse", value: "Basse" },
                    { label: "Moyenne", value: "Moyenne" },
                    { label: "Haute", value: "Haute" }
                ]
            }, {
                label: "Nom du client :",
                name: "qualite_generale.id_qualite_clients_qg",
                type: "select",
                placeholder: "Selectionner un client"
            }, {
                label: "Cofor :",
                name: "qualite_generale.id_qualite_cofor_qg",
                type: "select",
                placeholder: "Selectionner un Cofor"
            }, {
                label: "Secteur :",
                name: "qualite_generale.nom_wms_erp_qg",
                type: "select",
                placeholder: "Selectionner un Secteur",
                options: [
                    { label: "GEOLOG", value: "GEOLOG" },
                    { label: "ITEM", value: "ITEM" },
                    { label: "SAP", value: "SAP" },
                    { label: "WMS", value: "WMS" }
                ]
            }, {
                label: "Pilote :",
                name: "qualite_generale.pilote_qg"
            }, {
                label: "N° réclamation client :",
                name: "qualite_generale.num_reclamation_client_qg"
            }, {
                label: "Description de l'anomalie :",
                name: "qualite_generale.descriptif_anomalie_qg"
            }, {
                label: "Analyse de l'anomalie :",
                name: "qualite_generale.analyse_anomalie_qg"
            }, {
                label: "Action à mettre en place :",
                name: "qualite_generale.action_mise_en_place_qg"
            }, {
                label: "Etat de l'action :",
                name: "qualite_generale.etat_action_qg",
                type: "select",
                placeholder: "Selectionner un Etat",
                options: [
                    { label: "En cours", value: "1" },
                    { label: "Retard", value: "2" },
                    { label: "Validée", value: "3" }
                ]
            }, {
                label: "NC Catégorie :",
                name: "qualite_generale.id_qualite_motif_qg",
                type: "select"
            }, {
                label: "NC motif :",
                name: "qualite_generale.id_qualite_detail_motif_qg",
                type: "select"
            }, {
                label: "N° de facture :",
                name: "qualite_generale.num_facture_qg"
            }, {
                label: "Montant facture :",
                name: "qualite_generale.montant_qg"
            }, {
                label: "Responsabilité :",
                name: "qualite_generale.responsabilite_qg",
                type: "select",
                placeholder: "Selectionner une Responsabilité",
                options: [
                    { label: "GEFCO", value: "GEFCO" },
                    { label: "CLIENT", value: "CLIENT" },
                    { label: "FOURNISSEUR", value: "FOURNISSEUR" },
                    { label: "SOUS-TRAITANT", value: "SOUS-TRAITANT" }
                ]
            }
        ]
    } );

    //cascading fild list
    editor.dependent( 'qualite_generale.id_qualite_motif_qg', 'dependent_motif_detailMotif.php' );
    editor.dependent( 'qualite_generale.id_qualite_clients_qg', 'dependent_client_cofor.php' );


     $('#EditQualite').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "Staff_Qualite.php",
            type: "POST"
        },
        serverSide: true,
        columns: [
            { data: "qualite_generale.date_qg" },
            { data: "qualite_generale.origine_remontee_qg" },
            { data: "qualite_generale.importance_qg" },
            { data: "qualite_clients.nom_qc" },
            { data: "qualite_cofor.nom_cofor_qco" },
            { data: "qualite_generale.pilote_qg" },
            { data: "qualite_generale.num_reclamation_client_qg" },
            { data: "qualite_generale.descriptif_anomalie_qg" },
            { data: "qualite_generale.analyse_anomalie_qg" },
            { data: "qualite_generale.action_mise_en_place_qg" },
            { data: "qualite_generale.etat_action_qg", 
                render: function(data, type, row) {
                    var maValeur = '';
                   if (data === "1"){maValeur = 'En cours';}
                   if (data === "2"){maValeur = 'Retard';}
                   if (data === "3"){maValeur = 'Validée';}
                  
                   return maValeur;
                }
            },
            { data: "qualite_motif.motif" },
            { data: "qualite_detail_motif.detail_qdm" },
            { data: "qualite_generale.num_facture_qg" },
            { data: "qualite_generale.montant_qg" },
            { data: "qualite_generale.responsabilite_qg" }
        ],
         select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor }
        ]
    } );
} );

PHP :

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'qualite_generale', 'id_qg' )
    ->fields(
        
        Field::inst( 'qualite_generale.date_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.origine_remontee_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.importance_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        //CLIENT
        Field::inst( 'qualite_generale.id_qualite_clients_qg' )
        ->options( Options::inst()
            ->table( 'qualite_clients' )
            ->value( 'id_qc' )
            ->label( 'nom_qc' )
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_clients.nom_qc' ),
            
            
        //COFOR
        Field::inst( 'qualite_generale.id_qualite_cofor_qg' )
        ->options( Options::inst()
            ->table( 'qualite_cofor' )
            ->value( 'id_qco' )
            ->label( 'nom_cofor_qco' )
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_cofor.nom_cofor_qco' ),
            
            
        Field::inst( 'qualite_generale.nom_wms_erp_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.pilote_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.num_reclamation_client_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.descriptif_anomalie_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.analyse_anomalie_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.action_mise_en_place_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.etat_action_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        //MOTIF
        Field::inst( 'qualite_generale.id_qualite_motif_qg' )
        ->options( Options::inst()
            ->table( 'qualite_motif' )
            ->value( 'id_qm' )
            ->label( 'motif' )
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_motif.motif' ),
            
            
        //DETAIL MOTIF
        Field::inst( 'qualite_generale.id_qualite_detail_motif_qg' )
        ->options( Options::inst()
            ->table( 'qualite_detail_motif' )
            ->value( 'id_qdm' )
            ->label( 'detail_qdm' )
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_detail_motif.detail_qdm' ),
        
        
        Field::inst( 'qualite_generale.num_facture_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.montant_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ),
        Field::inst( 'qualite_generale.responsabilite_qg' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) ) 
    )   
        
    ->leftJoin( 'qualite_motif', 'qualite_motif.id_qm', '=', 'qualite_generale.id_qualite_motif_qg' )
    ->leftJoin( 'qualite_detail_motif', 'qualite_detail_motif.id_qdm', '=', 'qualite_generale.id_qualite_detail_motif_qg' )
    ->leftJoin( 'qualite_clients', 'qualite_clients.id_qc', '=', 'qualite_generale.id_qualite_clients_qg' )
    ->leftJoin( 'qualite_cofor', 'qualite_cofor.id_qco', '=', 'qualite_generale.id_qualite_cofor_qg' )
    ->where('qualite_generale.id_site_whs_qg', $Id_WHS, '=')
    ->where('qualite_clients.id_whs_qc', $Id_WHS, '=')
    ->where('qualite_cofor.id_whs_qco', $Id_WHS, '=')
    ->process( $_POST )
    ->json();

SWATswat · May 2021

editor.dependent file : Depend_client_cofor

ini_set('display_errors', '1');
 
 
// DataTables PHP library and database connection
include_once( "Editor-PHP-2.0.0--kpi/lib/DataTables.php" );
 
 
    $My_var1 = $db
        ->select( 'qualite_cofor', ['id_qco as value', 'nom_cofor_qco as label'], ['id_clients_qco' => $_REQUEST['values']['qualite_generale.id_qualite_clients_qg']] )
        ->fetchAll();
        
    
    echo json_encode( [
        'options' => [
            'qualite_generale.id_qualite_cofor_qg' => $My_var1
        ]
    ] );

editor.dependent file : dependent_motif_detailMotif

ini_set('display_errors', '1');
 
 
// DataTables PHP library and database connection
include_once( "Editor-PHP-2.0.0--kpi/lib/DataTables.php" );
 
 
    $My_var = $db
        ->select( 'qualite_detail_motif', ['id_qdm as value', 'detail_qdm as label'], ['id_qualite_motif' => $_REQUEST['values']['qualite_generale.id_qualite_motif_qg']] )
        ->fetchAll();
        
    
    echo json_encode( [
        'options' => [
            'qualite_generale.id_qualite_detail_motif_qg' => $My_var
        ]
    ] );

The 2 cascading lists work well but the information retrieved is for all warehouses.
I would like the where clause to work to retrieve only the data from the connected warehouse ($id_whs)

Thanks for your help.

allan · May 2021

The condition being applied to the SELECT statements for the options is:

['id_qualite_motif' => $_REQUEST['values']['qualite_generale.id_qualite_motif_qg']]

Should it also be using $id_whs? Do you also need to post that parameter to the server, or is it somewhere in the form field? Sorry - there is a lot of code there, so I haven't been able to fully take it all in.

Allan

SWATswat · May 2021

Hello Allan,

this variable is on the PHP page, I can retrieve its content from anywhere. It contains the ID of the warehouse.
I need the 3 clause where to use this ID to retrieve only the information of the connected warehouse.

Thanks for your help.

SWATswat · May 2021

Can you tell me how to add the where clause in the editor.dependent file please?

Thanks
(I use Editor 2.0.0)

allan · May 2021

Thanks - so what you are going to have to do is send extra information to the server so it can make the correct query. For that, you can use the data option available as a function - e.g.:

editor.dependent(
  'qualite_generale.id_qualite_motif_qg',
  {
    url: 'dependent_motif_detailMotif.php'
    data: function (d) {
      d.id_whs = ...;
      ...
    }
  }
);

Any parameters you add to the d object there, will be available to you on the server-side:

$REQUEST['id_whs']`

which you can then use in your array for the WHERE condition.

So the part you need to fill in, is the ... in the code above to submit the values that you need for your query condition.

Regards,
Allan

SWATswat · May 2021

Thank you very much Allan for your answer.
only not being a developer, I'm a bit lost.
Where should I place this code and what type of action should I put instead of the ... ?
where to place this code: $REQUEST['id_whs'] ?

Do you have an example somewhere to help me

Thanks for your patience and help.

SWATswat · May 2021

Really nobody has a solution?

please, I really need your help.

colin · May 2021

Yep, it's on Allan's list to reply, it's been the weekend so please be patient.

Colin

allan · May 2021

I’m not sure of your exact data model or design specification, so I can’t say exactly what the code should be, however, for the WHERE statement you need to expand on the condition you already have for the Ajax options file - e.g.:

  $My_var1 = $db
    ->select(
      'qualite_cofor',
      ['id_qco as value', 'nom_cofor_qco as label'],
      [
        'id_clients_qco' => $_REQUEST['values']['qualite_generale.id_qualite_clients_qg'],
        'id_whs' => $REQUEST['id_whs']
      ]
    )
  ->fetchAll();

On the client-side, you need to get the id_whs value from somewhere - if you have it as a PHP variable then you might use <?php echo json_encode($id_whs); ?>.

Allan

SWATswat · May 2021

First of all, thank you for your feedback, your help and your patience.
I got so involved in this problem that I didn't see the weekend pass.
I tried the modifications but without success.
A little explanation would be easier.

I have 3 tables :

qualite_generale

id_qg Primaire int
id_site_whs_qg int
date_qg date
origine_remontee_qg varchar
importance_qg varchar
nom_client_qg varchar
cofor_qg varchar
num_reclamation_client_qg varchar
nom_wms_erp_qg varchar
pilote_qg varchar
descriptif_anomalie_qg text
analyse_anomalie_qg text
action_mise_en_place_qg text
etat_action_qg int
id_qualite_motif_qg int
id_qualite_detail_motif_qg int
num_facture_qg varchar
montant_qg decimal
responsabilite_qg varchar
id_qualite_clients_qg int
id_qualite_cofor_qg int

qualite_cofor

id_qco Primaire int
id_whs_qco int
nom_cofor_qco varchar
id_clients_qco int

qualite_clients

id_qc Primaire int
id_whs_qc int
nom_qc varchar

If I schematize the cascading lists:

qualite_generale.nom_client_qg = ## qualite_clients ##
and
qualite_generale.cofor_qg = ## qualite_cofor ##

But with the condition that if I choose a customer when inserting data in the table ## qualite_generale ##, I must have only the cofor (supplier code) that correspond to this customer for the connected warehouse.
Or :
qualite_generale.id_site_whs_qg = qualite_cofor.id_whs_qco = qualite_clients.id_whs_qc = $ID_WHS

Currently, when I select the client in Datatable Editor, I have all clients combined.

That's where I'm at.

Thank.

tangerine · May 2021

I tried the modifications but without success.

Please show the relevant code.

SWATswat · May 2021

Hello Tangerine,

Here are the changes:

Page Dependent_client_cofor.php :

// DataTables PHP library and database connection
include_once( "Editor-PHP-2.0.0--kpi/lib/DataTables.php" );
 
 
    $My_var1 = $db
        ->select( 
        'qualite_cofor', 
        ['id_qco as value', 'nom_cofor_qco as label'], 
        [
        'id_clients_qco' => $_REQUEST['values']['qualite_generale.id_qualite_clients_qg'],
        'id_whs_qco' => $_REQUEST['qualite_generale.id_site_whs_qg']
        ] )
        ->fetchAll();
        
    
    echo json_encode( [
        'options' => [
            'qualite_generale.id_qualite_cofor_qg' => $My_var1
        ]
    ] );

Page Staff_Qualite.php

...
->leftJoin( 'qualite_motif', 'qualite_motif.id_qm', '=', 'qualite_generale.id_qualite_motif_qg' )
    ->leftJoin( 'qualite_detail_motif', 'qualite_detail_motif.id_qdm', '=', 'qualite_generale.id_qualite_detail_motif_qg' )
    ->leftJoin( 'qualite_clients', 'qualite_clients.id_qc', '=', 'qualite_generale.id_qualite_clients_qg' )
    ->leftJoin( 'qualite_cofor', 'qualite_cofor.id_qco', '=', 'qualite_generale.id_qualite_cofor_qg' )
    ->where('qualite_generale.id_site_whs_qg', $Id_WHS, '=')
    ->process( $_POST )
    ->json( $Id_WHS );

Thanks for your help.

allan · May 2021

$_REQUEST['qualite_generale.id_site_whs_qg']

Can you show me how you are sending qualite_generale.id_site_whs_qg to the server please? Presumably through the data property for the dependent() call?

Have you also tried: print_r($_REQUEST); to make sure that it is being sent to the server?

Allan

SWATswat · May 2021

Hello Allan,

The print_r() doesn't give anything, I don't know where to put it, I put it in the page Qualite.php. the return is empty.

But I have better. Now when I select a 'client' I have well that the 'cofor' attached to this 'client' which are present in the list.

Here are the modifications:

In the Qualite.php file, I added this :

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "Staff_Qualite.php",
        table: "#EditQualite",
        fields: [ {
                label: "WHS :",
                name: "qualite_generale.id_site_whs_qg",
                def: "<?php echo $Id_WHS; ?>",
                type: "readonly"
            }, {...

In the file Staff_Qualite.php, I added this in the first line :

Field::inst( 'qualite_generale.id_site_whs_qg' ),

and here is my dependent file : I replaced this

'id_whs_qco' => $_REQUEST['qualite_generale.id_site_whs_qg']

By This :

'id_whs_qco' => $_REQUEST['values']['qualite_generale.id_site_whs_qg']

My last problem is that I have all the customers of all the warehouses displayed.
I need a where clause to complete my project, the equivalent of this:

qualite_clients.id_whs_qc = qualite_generale.id_site_whs_qg

but I don't know where to put it or in what form.
Would you have a lead ?

Thank you very much for your help

allan · May 2021

Yup - that looks like a nice workaround. I was going to suggest you try:

editor.dependent(
  'qualite_generale.id_qualite_motif_qg',
  {
    url: 'dependent_motif_detailMotif.php'
    data: function (d) {
      d.id_whs = "<?php echo $Id_WHS; ?>";
    }
  }
);

But your solution to use a readonly or hidden field looks fine.

My last problem is that I have all the customers of all the warehouses displayed.
I need a where clause to complete my project, the equivalent of this:

Apologies - is that for the host table or the options being displayed?

Thanks,
Allan

SWATswat · May 2021

Hello Allan,

The host table? I don't know.

But the table Qualite_generale calls the table 'qualite_clients' which calls the table 'qualite_cofor'.

In order for the list of customers to be displayed in relation to the warehouse ID, the where clause would have to work with the 'qualite_clients' table.

I admit that I'm making progress little by little but I don't understand everything I'm doing! It's strange .

Thanks for your help.

allan · May 2021

In order for the list of customers to be displayed in relation to the warehouse ID, the where clause would have to work with the 'qualite_clients' table.

Can you show me the Javascript initialisation for that table please?

If you are able to give me a link to your page, I'll hopefully be able to get a better picture of what is going on.

Thanks,
Allan

SWATswat · May 2021

Hello Allan,
It's here for page Qualite.php, the first part:

...
{
                label: "Nom du client :",
                name: "qualite_generale.id_qualite_clients_qg",
                type: "select",
                placeholder: "Selectionner un client"
            },
...

Page Qualite.php, the second part :

...
{ data: "qualite_clients.nom_qc" },
...

and the page Staff_Qualite.php :

...
//CLIENT
        Field::inst( 'qualite_generale.id_qualite_clients_qg' )
        ->options( Options::inst()
            ->table( 'qualite_clients' )
            ->value( 'id_qc' )
            ->label( 'nom_qc' )
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_clients.nom_qc' ),
...

I sent you the link by message because it is a production server.

The table is empty for now, click on NEW and the problem is with the fields :
- "Customer Name" (Nom du client) and "Cofor" (Cofor).

When I select a customer, I have the cofor attached to it.
But in the list of customers, there is the customer TEST and it is on the warehouse 2 so it should not appear.

This is what I am trying to do without success.

Thank you very much for your precious help.

allan · May 2021

Thanks so much for the link - that really helped!

In your Qualite.php file, add:

data: function (d) {
  d.id_whs = "<?php echo $Id_WHS; ?>";
}

to your ajax object for the $('#EditQualite').DataTable( { initialisation object.

Then in Staff_Qualite.php where you have the ->options() instance add:

->where('{id-column}', $_REQUEST['id_whs'])

I don't know what the name of the column id_whs should be matching against I'm afraid, so change {id-column} to the once that is required.

Allan

SWATswat · May 2021

Hello Allan,

It doesn't work, I don't have any selection in 'Client' and 'Cofor', maybe I didn't put the code in the right place.

Page Qualite.php :

...
$('#EditQualite').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "Staff_Qualite.php",
            type: "POST",
        data: function (d) {
              d.id_whs = "<?php echo $Id_WHS; ?>";
        }
        },
        serverSide: true,
        columns: [
...

Page Staff_Qualite.php :

...
//CLIENT
        Field::inst( 'qualite_generale.id_qualite_clients_qg' )
        ->options( Options::inst()
            ->table( 'qualite_clients' )
            ->value( 'id_qc' )
            ->label( 'nom_qc' )
        ->where('qualite_clients.id_whs_qc', $_REQUEST['id_whs'])
        )
        ->validator( Validate::dbValues() ),
    Field::inst( 'qualite_clients.nom_qc' ),
...

Thank you again for your help..

allan · May 2021

I can see that id_whs: "1" is being sent to Staff_Qualite.php now, and it is returning:

qualite_generale.id_qualite_clients_qg  []

That suggests to me, that there are no records in the qualite_clients table where the id_whs_qc column is equal to 1. Is that the case?

Allan

SWATswat · May 2021

Hello Allan,

No not at all, there are indeed data with id_whs_qc = 1, here is :

I added in page Staff_Qualite.php :

->debug(true)

SWATswat · May 2021

Allan,

The problem may come from there: "WHERE id_whs_qc IS NULL"

3 Object:
query: "SELECT DISTINCT `id_qc` as 'id_qc', `nom_qc` as 'nom_qc' FROM `qualite_clients` WHERE `id_whs_qc` IS NULL "

But I don't understand why?

allan · May 2021

Let's try:

->where(function ($q) {
  $q->where('id_whs_qc', $_POST['id_whs']);
})

Regards,
Allan

SWATswat · May 2021

Hello Allan,

Thanks a lot, it works perfectly.

Thank you for your patience and your very precious help.

Cascading list with leftjoin and Where clause

Cascading list with leftjoin and Where clause

Replies

qualite_generale

qualite_cofor

qualite_clients

Howdy, Stranger!

Categories

DataTables

Cascading list with leftjoin and Where clause

Cascading list with leftjoin and Where clause

Replies

qualite_generale

qualite_cofor

qualite_clients

Howdy, Stranger!

Quick Links

Categories

DataTables