Where Condition

veryjackson · September 2021

Im using searchpanes server-side and i have where condition

but search panes show count all fields how can i filter search panes value like main where condition ?

Im using like that but not working

<?php
// DataTables PHP library
include("php/DataTables.php");

// Alias Editor classes so they are easy to use
use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions,
  DataTables\Editor\SearchPaneOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst($db, 'odemeler')
  ->fields(
    Field::inst('odemeler.side')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.tarih')
      ->getFormatter(Format::dateSqlToFormat('Y-m-d H:i:s'))
      ->setFormatter(Format::dateFormatToSql('Y-m-d H:i:s'))
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.durum')
      ->searchPaneOptions(
        SearchPaneOptions::inst()
          ->table('odemeler')
          ->value('durum')
          ->where(function ($q) {
            $q->where('deleted', 0);
          })
          ->render(function ($str) {
            switch ($str) {
              case '1':
                return 'Kart Ödemesi Bekleniyor';
                break;
              case '2':
                return 'Havale Ödemesi Bekleniyor';
                break;
              case '3':
                return 'Kart İle Ödendi';
                break;
              case '4':
                return 'Havale İle Ödendi';
                break;
              case '5':
                return 'Hatalı Kart Ödemesi';
                break;
            }
          })
      )
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.yontem')
      ->searchPaneOptions(
        SearchPaneOptions::inst()
          ->table('odemeler')
          ->value('yontem')
          ->where(function ($q) {
            $q->where('deleted', 0);
          })
          ->render(function ($str) {
            switch ($str) {
              case 'kart':
                return 'Kart';
                break;
              case 'havale':
                return 'Havale';
                break;
            }
          })
      )
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.okulId')
      ->options(
        Options::inst()
          ->table('odeme_okul_listesi')
          ->value('id')
          ->label('baslik')
      ),
    Field::inst('odeme_okul_listesi.baslik')
      ->searchPaneOptions(
        SearchPaneOptions::inst()
          ->value('odeme_okul_listesi.baslik')
          ->label('odeme_okul_listesi.baslik')
          ->where(function ($q) {
            $q->where('deleted', 0);
          })
          ->leftJoin('odeme_okul_listesi', 'odeme_okul_listesi.id', '=', 'odemeler.okulId')
      ),
    Field::inst('odemeler.fiyat')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.taksit')
      ->searchPaneOptions(
        SearchPaneOptions::inst()
          ->table('odemeler')
          ->value('taksit')
          ->where(function ($q) {
            $q->where('deleted', 0);
          })
      )
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.ogrenciadi')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.sinifi'),
    Field::inst('odemeler.okulnumarasi'),
    Field::inst('odemeler.ogrencitc')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.hes')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.adres')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.veliannebaba')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.veliadi')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.velisoyadi')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.velitc')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.velitelefon')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.velimail')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.velimeslek')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
    Field::inst('odemeler.deleted')
      ->validator(Validate::notEmpty(
        ValidateOptions::inst()
          ->message('Bu alan zorunludur')
      )),
  )
  ->where('odemeler.deleted', 0)
  ->leftJoin('odeme_okul_listesi', 'odeme_okul_listesi.id', '=', 'odemeler.okulId')
  ->on('preRemove', function () {
    return false;
  })
  ->debug(true)
  ->process($_POST)
  ->json();

sandy · September 2021

Hi @veryjackson ,

Making the following to the SearchPanes Editor example locally I get the following.

Field::inst( 'sites.name' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'sites.name')
                ->label( 'sites.name' )
                ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
                ->where( function ($q) {
                    $q->where('sites.name', 'London', '=');
                })
            )

but search panes show count all fields

I'm not sure what you mean by this. Could you expand further on what you are seeing that you think is incorrect please?

Thanks,
Sandy

veryjackson · September 2021

@sandy So i need searchpanes data if deleted = 0 my example i do it but not working ?

sandy · September 2021

Hi @veryjackson ,

Have you tried adding the table into the where call? Something like

$q->where('odemeler.deleted', 0, '=');

Are there any errors returned?

Thanks,
Sandy

veryjackson · September 2021

@sandy Yes i tried like that but still searchpane data ignored deleted parameter

sandy · September 2021

Hi @veryjackson ,

In which case I will need to see a test case please in order to understand what is going on. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Thanks,
Sandy

veryjackson · September 2021

Hi @sandy

As far as I understand, we cannot access the deleted value here.What I want to know is how can we access it? because we can only use where command about whatever value is

Field::inst('odemeler.taksit')
      ->searchPaneOptions(
        SearchPaneOptions::inst()
          ->table('odemeler')
          ->value('taksit')
          ->where(function ($q) {
            $q->where('odemeler.deleted', 0, '=');
          })
      )

allan · September 2021

we cannot access the deleted value here

Is there a reason for that? Are you getting an error message or is there something else going wrong?

If you would post a link to a test case, as Sandy asked for, that would be really useful to let us debug this.

Regards,
Allan

veryjackson · September 2021

Hi @allan

Because searchpanes ignore main where condation, no error message, only the values for serchpanes do not match the main where condation. Searchpanes ignore this condation

->where('odemeler.deleted', 0)
  ->leftJoin('odeme_okul_listesi', 'odeme_okul_listesi.id', '=', 'odemeler.okulId')
  ->on('preRemove', function () {
    return false;
  })
  ->debug(true)
  ->process($_POST)
  ->json();

allan · September 2021

That is correct - the SearchPanesOptions class provides its own where method which you would need to use if you want to limit its options.

Allan

veryjackson · September 2021

@allan So how do I get it to filter by the main where method?

allan · September 2021

You don't - as I mentioned, SearchPanes has its own where() method. You'd need to apply the same condition to your SearchPanes options instance as you do to your main Editor instance.

        SearchPaneOptions::inst()
          ->table('odemeler')
          ->value('taksit')
          ->where(function ($q) {
            $q->where('odemeler.deleted', 0, '=');
          })

will be showing all the options where deleted is not 0.

I think to be honest I'm starting to get a little confused here! It would be helpful if you could link to your page showing the behaviour as it is, and with details on the behaviour that you would like to see.

Allan

veryjackson · September 2021

@allan I'm trying this, but it doesn't make this condition. The number on the side is not the same as the number it's filtering.

tangerine · September 2021

You have been asked three times to provide a link to your page or a test case.
https://datatables.net/manual/tech-notes/10

Where Condition

Where Condition

Answers

Howdy, Stranger!

Categories

DataTables

Where Condition

Where Condition

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables