Problem WHERE clause

SWATswat

Hi,
I use EDITOR Data Table and would like to know how to use the WHERE clause to get the result of the application SQL below

SELECT `gestion`.`date_entree`,
       `gestion`.`date_sortie`,
       `gestion`.`nbre_palette`,
       `gestion`.`numero_bl`,
       `gestion`.`transporteur`,
       `gestion`.`achat_trs`,
       `gestion`.`vente_trs`,
       `regie`.`numero_bl_regie`,
       `regie`.`date_regie`,
       `regie`.`heure_regie`,
       `regie`.`commentaire`
FROM `gestion`,
     `regie`
WHERE `regie`.`numero_bl_regie`=`gestion`.`numero_bl`

Thanks a lot for your help.

allan

You'd need to use the "complex" where options in Editor. For example:

->where( function ($q) {
  $q->where( 'regie.numero_bl_regie', 'gestion.numero_bl', '=', false );
} )

The final parameter is the important one as it stops the value in the second parameter being bound.

Allan

SWATswat

Thanks for your response Allan,
But it's not work.

Erreur message is :
{"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'regie' in 'field list'","data":[]}

My code is correst ?

 // Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'gestion', 'regie' )
    ->fields(
        Field::inst( 'gestion.numero_bl' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.date_entree' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.date_sortie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.nbre_palette' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.transporteur' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.achat_trs' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.vente_trs' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.date_regie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.heure_regie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.commentaire' )->validator( 'Validate::notEmpty' )
    )
    ->where( function ( $q ) use( $MyDateRecherche, $MyBlRecherche ){
        $q->where( 'regie.numero_bl_regie', 'gestion.numero_bl', '=', false );
                $q->or_where( 'gestion.date_entree', $MyDateRecherche, '=');
               $q->or_where( 'gestion.numero_bl', $MyBlRecherche, '=');
    } )
    ->process( $_POST )
    ->json();

$(document).ready(function() {
    
    


    $('#example').DataTable( {
        dom: 'Bfrtip',
        ajax: "php/staff4.php",
           
        columns: [{
                "data": "gestion.numero_bl"
            },
            {
                "data": "gestion.date_entree"
            },
            {
                "data": "gestion.date_sortie"
            },
            {
                "data": "gestion.nbre_palette"
            },
            {
                "data": "gestion.transporteur"
            },
            {
                "data": "gestion.achat_trs"
            },
            {
                "data": "gestion.vente_trs"
            },
            {
                "data": "regie.date_regie"
            },
            {
                "data": "regie.heure_regie"
            },
            {
                "data": "regie.commentaire"
            }
        ],
        select: true,
        buttons: [
                                    
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }
        ]
    } );
} );

Thanks

allan

Is the primary key in your gestion table called regie? Could you show me the SQL schema?

Allan

SWATswat

Hi Allan,

Attached SQL plan.

This query works with the views in MySQL.

gestion (
  `id` int(200) NOT NULL,
  `date_entree` date NOT NULL,
  `date_sortie` date DEFAULT NULL,
  `nbre_palette` int(2) NOT NULL,
  `numero_bl` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `transporteur` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `achat_trs` decimal(8,2) NOT NULL,
  `vente_trs` decimal(8,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mouvement (
  `id` int(200) NOT NULL,
  `date_mouvement` date NOT NULL,
  `numero_bl_mouvement` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `date_reception` date NOT NULL,
  `stock_du_jour` int(1) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=265 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

regie` (
  `id` int(200) NOT NULL,
  `numero_bl_regie` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `date_regie` date NOT NULL,
  `heure_regie` decimal(4,2) NOT NULL,
  `commentaire` varchar(150) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `gestion`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `mouvement`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `regie`
  ADD PRIMARY KEY (`id`);

In summary:
gestion.numero_bl = mouvement.numero_bl_mouvement = regie.numero_bl_regie

Thanks

allan

Super - thanks! Let me take a more detailed look over it than my phone allows for (travelling at the moment). Normal service resumes on Tuesday!

Allan

allan

Could you try using:

Editor::inst( $db, 'gestion', 'gestion.regie' )

for your constructor? I think the issue is that Editor doesn't prefix the primary key with the table name automatically. If you could let me know if that addresses the issue I will commit a change to fix that for future releases.

Thanks,
Allan

SWATswat

Hi Allan,

I just do the test with your code, it did not work.

Error Message :
DataTables warning: table id=example - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'gestion.regie' in 'field list'

allan

Let's try:

Editor::inst( $db, 'gestion', 'gestion.id' )

Sorry - I copied and pasted your initialisation from above without full parsing the code in my head. There is no regie column in the gestion column.

Allan

SWATswat

Hi Allan,

Unfortunately, it does not find the table "regie"

Error Message :
DataTables warning: table id=example - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'regie.date_regie' in 'field list

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'gestion', 'gestion.id' )
    ->fields(
        Field::inst( 'gestion.numero_bl' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.date_entree' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.date_sortie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.nbre_palette' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.transporteur' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.achat_trs' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'gestion.vente_trs' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.date_regie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.heure_regie' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'regie.commentaire' )->validator( 'Validate::notEmpty' )
    )
    ->where( function ( $q ) use( $MyDateRecherche, $MyBlRecherche ){
        $q->where( 'regie.numero_bl_regie', 'gestion.numero_bl', '=', false );
        $q->or_where( 'gestion.date_entree', $MyDateRecherche, '=');
        $q->or_where( 'gestion.numero_bl', $MyBlRecherche, '=');
        
                
    } )
    ->process( $_POST )
    ->json();

allan

There is no leftJoin statement in the above. You would need to add one to be able to read from two different tables. Documentation about how to do joins is available here.

Allan