left join only for getting values for new entrys

MadMax76

I have tables for
- invoices
- users
- approval for invoices (holding data wihch users have to approve each invoice; might be multiple users).

when opening an invioce, I also call a list of all required approvers with their status, date and so on. I also add a button for additional approvals. In the pop-up I want that only people are listed, that are not already necessary as approvers for this invoice.
Therefore I also already have a view in my sql, that is giving the names of those potential additional users ('V_NeueFreigeber').

Everything works fine but one thing: In the pop-up for new users ALL potential users for all invoices are named.

Here the server-code:

<?php
include( "../lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

session_start();

$selectedStapelname = $_SESSION["selectedStapelname"];
$selectedINP_ID = $_SESSION["selectedINP_ID"];

Editor::inst( $db, 'V_Freigaben', 'ID_freigabe')
    ->fields(
        Field::inst( 'V_Freigaben.ID_freigabe' )->set(false),
        Field::inst( 'V_Freigaben.INP_ID' ),
        Field::inst( 'V_Freigaben.Stapelname' ),
        Field::inst( 'V_Freigaben.freig_person' )
//here i get the potential new approvers; this field always returns nothing/null
            ->options( Options::inst()
                ->table( 'V_NeueFreigeber' )
                ->value( 'rights_PID' )
                ->label( 'NamePers' ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'V_NeueFreigeber.NamePers' ),
        Field::inst( 'V_Freigaben.freig_person2' )->set(false)
//here i get the already established approvers for showing them in the list
//in the end freig_person and freig_person2 are the same field!!!
            ->options( Options::inst()
                ->table( 'V_users' )
                ->value( 'PID' )
                ->label( 'Namen' ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'V_users.Namen' ),
        Field::inst( 'V_Freigaben.freig_zahlsp_grund' )
    )
    ->where( 'V_Freigaben.Stapelname', $selectedStapelname )
    ->where( 'V_Freigaben.INP_ID', $selectedINP_ID )

    ->leftJoin( 'V_users', 'V_users.PID', '=', 'V_Freigaben.freig_person' )
    ->leftJoin( 'V_NeueFreigeber', 'V_NeueFreigeber.INP_ID = V_Freigaben.INP_ID AND V_NeueFreigeber.Stapelname = V_Freigaben.Stapelname COLLATE Latin1_General_100_CI_AS AND V_NeueFreigeber.rights_PID = V_Freigaben.freig_person', '', '' )
    ->process( $_POST )
    ->json();

Here the html (the relevant parts)

    var editor; // use a global for the submit and return data rendering in the examples
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            ajax: "../../../../../../../DataTables/Editor-PHP-1.9.6/controllersKontura/freigabe_PID_St_INP.php",
            table: "#liste_freigaben",
            fields: [
               { label: "Freigeber", name: "V_Freigaben.freig_person", type: "select", placeholder: "Bitte wählen"
            }, { label: "INP ID", name: "V_Freigaben.INP_ID", default: "__INP_ID__", type: "hidden"
            }, { label: "freig_angefordert_von", name: "V_Freigaben.freig_angefordert_von", default: "__thisPID__", type: "hidden"
            }, { label: "Stapelname", name: "V_Freigaben.Stapelname", default: "__Stapelname__", type: "hidden"
            }, { label: "Begründung", name: "V_Freigaben.freig_angefordert_wie"
            } ],
            formOptions: { inline: { onBlur: 'submit'}}
        } );

How can I achieve this?

Thanks
Max

allan

Hi Max,

In the pop-up I want that only people are listed, that are not already necessary as approvers for this invoice.

What you need to do for this is make use of dependent(). The values of the select field depend on the state of other fields in the form, so when the form is in that state (start edit or user edits values) the client-side will need to refresh the list of options. Typically that is done by making an Ajax request to the server to get the list of options (to another PHP script which will query the database based on the state of the form).

That approach is taken in this blog post. While it isn't exactly the same as your case, the principles there can be applied.

Let me know how you get on with that,
Allan

MadMax76

Hi Allan,

first of all: I LOVE this editor+datatable!!! Great stuff and amazing possibilities!

And I will dig into the dependencies on another occasion, I have a future problem this will be necessary for!

BUT: I think in this case this is not the best solution, I do not need an update of values depending from other fields. The possible filed-values can be selected when building the datatable/editor.

I think it must be something like:

fields: [
  { 
       label: "Freigeber", 
       name: "V_Freigaben.freig_person", 
       type: "select",
       placeholder: "Bitte wählen", 
       **options: [ 'call from ajax??? sql-view...' ] **
}

i tried with options: ['max', 'kurt', 'allan'], but that didnt change anything - I still got ALL possible values from V_Freigaben.freig_person; max, kurt and allan were neglected.

Thanks for your help!
max

allan

You mean you just have a static list of options that can be used for all rows?

            ->options( Options::inst()
                ->table( 'V_NeueFreigeber' )
                ->value( 'rights_PID' )
                ->label( 'NamePers' ) )

looks correct to me, assuming that the SQL names are correct.

Just before the ->process( $_POST ) can you add ->debug(true), then can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Regards,
Allan

MadMax76

Hi Allen, thanks again. Yes this works but calls ALL rows, I would need to limit them with something like
->where( 'V_Freigaben.Stapelname', $selectedStapelname )
->where( 'V_Freigaben.INP_ID', $selectedINP_ID )

Is that possible?

By the way some little critics, maybe the view of a beginner is helpful for optimizing some points. Nothing of high importance, but small anoying things...:
* in the explanations (manual etc) sometimes it is not clear (for beginners) where code should go to - e.g. into php, server-side, ...
* in some cases the blue box in the right top corner with "PHP" or "javascript" is on top of bit of the code, which can then not be read.
* in my "account" i find a list of all questions I ever have had. All of them are marked open, although apart from this one I accepted an answer in each of them.

Max

MadMax76

Sorry should be
->where( 'V_NeueFreigeber.Stapelname', $selectedStapelname )
->where( 'V_NeueFreigeber.INP_ID', $selectedINP_ID )

allan

Is this what you are looking for?:

->options( Options::inst()
    ->table( 'V_NeueFreigeber' )
    ->value( 'rights_PID' )
    ->label( 'NamePers' )
    ->where( 'Stapelname', $selectedStapelname )
    ->where( 'INP_ID', $selectedINP_ID )
 )

Thanks for the feedback!

1. Agreed - we need to work on clarifying what is client-side and what is server-side. Thanks for noting that.
2. Good point - it has annoyed me a few times as well. I haven't found a good way to handle it yet due to the container being overflow: scroll. I need to have another think about it.
3. It is meant to just be a link - as in "Open this thread", rather than a status. I'd never thought of it being a state before - perhaps "View" would be better.

Regards,
Allan

tangerine

• in some cases the blue box in the right top corner with "PHP" or "javascript" is on top of bit of the code, which can then not be read.

And also, why does it have a "hover" state, which I interpret as meaning it is somehow actionable on click?

MadMax76

Hi Allan, that would be what I need.
For some reasons with that solution, and also with

            ->options( Options::inst()
                ->table( 'V_NeueFreigeber' )
                ->value( 'rights_PID' )
                ->label( 'NamePers' )
                ->where( 'V_NeueFreigeber.Stapelname', $selectedStapelname )
                ->where( 'V_NeueFreigeber.INP_ID', $selectedINP_ID ))

there are no results displayed at all.

Just to make sure it is not a problem with the sql or similar I made up a controller like this

<?php
// DataTables PHP library
include( "../lib/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;

session_start();

//$freig_person = $_SESSION["PID"];
$selectedStapelname = $_SESSION["selectedStapelname"];
$selectedINP_ID = $_SESSION["selectedINP_ID"];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'V_NeueFreigeber', ['INP_ID', 'Stapelname', 'rights_PID'])
    ->fields(
        Field::inst( 'V_NeueFreigeber.INP_ID' ),
        Field::inst( 'V_NeueFreigeber.Stapelname' ),
        Field::inst( 'V_NeueFreigeber.rights_PID' ),
        Field::inst( 'V_NeueFreigeber.NamePers' )
    )
    ->where( 'V_NeueFreigeber.Stapelname', $selectedStapelname )
    ->where( 'V_NeueFreigeber.INP_ID', $selectedINP_ID )

    ->debug(true)
    ->process( $_POST )
    ->json();

and the datatable to fit, and this works like a charm...

MadMax76

ad feedback: Happy to hear I did not offend!

ad 2: Cant you implement a first (empty) line that is always there?

allan

And also, why does it have a "hover" state, which I interpret as meaning it is somehow actionable on click?

Legacy... It used to show a dialogue box with extra information. Needs to be removed.

there are no results displayed at all.

Can you show me the JSON response from the server when the data is loaded please? It should contain the SQL executed, which would be useful here.

ad 2: Cant you implement a first (empty) line that is always there?

That would be one option. It just feels a bit redundant. When I read tangerine’s comment I was wondering about hiding the tag on hover over the code block.

Allan

MadMax76

Hi Allan,

I diged into "getting the json response" (I am learning a lot here...) and found this:

{
    "data": [
        {
            "DT_RowId": "row_461",
            "V_Freigaben": {
                "ID_freigabe": "461",
                "INP_ID": "1",
                "Stapelname": "920_20210124_21.47.49.138_",
                "freig_person": "13",
                "freig_person2": "13",
                "freig_angefordert_wann": "2021-01-24 22:23:38.963",
                "freig_angefordert_wie": "NEU21",
                "freig_erledigt": "0",
                "freig_erledigt_wann": "1900-01-01 00:00:00.000",
                "freig_abl_grund": null,
                "freig_angefordert_von": null,
                "freig_zahlsperre": null,
                "freig_zahlsp_grund": null
            },
            "V_NeueFreigeber": {
                "NamePers": null
            },
            "V_users": {
                "Namen": "Oliver"
            },
            "FREIG_Status": {
                "fre_stat_text": "offen"
            },
            "anforderer": {
                "Namen": null
            }
        },
        {
            "DT_RowId": "row_482",
            "V_Freigaben": {
                "ID_freigabe": "482",
                "INP_ID": "1",
                "Stapelname": "920_20210124_21.47.49.138_",
                "freig_person": "4",
                "freig_person2": "4",
                "freig_angefordert_wann": "2021-01-25 00:00:00.000",
                "freig_angefordert_wie": null,
                "freig_erledigt": "0",
                "freig_erledigt_wann": null,
                "freig_abl_grund": null,
                "freig_angefordert_von": null,
                "freig_zahlsperre": null,
                "freig_zahlsp_grund": null
            },
            "V_NeueFreigeber": {
                "NamePers": null
            },
            "V_users": {
                "Namen": "Kontura Max"
            },
            "FREIG_Status": {
                "fre_stat_text": "offen"
            },
            "anforderer": {
                "Namen": null
            }
        }
    ],
    "options": {
        "V_Freigaben.freig_person": [],
        "V_Freigaben.freig_person2": [
            {
                "label": "Oliver",
                "value": "13"
            },
            {
                "label": "Kontura Max",
                "value": "4"
            },
            {
                "label": "Reinhold",
                "value": "12"
            },
            {
                "label": "Josef",
                "value": "10"
            },
            {
                "label": "Max",
                "value": "3"
            },
            {
                "label": "Roland",
                "value": "9"
            },
            {
                "label": "Stryria Max",
                "value": "6"
            }
        ],
        "V_Freigaben.freig_erledigt": [
            {
                "label": "abgelehnt",
                "value": "2"
            },
            {
                "label": "freigegeben",
                "value": "1"
            },
            {
                "label": "offen",
                "value": "0"
            }
        ],
        "V_Freigaben.freig_angefordert_von": [
            {
                "label": "Oliver",
                "value": "13"
            },
            {
                "label": "Kontura Max",
                "value": "4"
            },
            {
                "label": "Reinhold",
                "value": "12"
            },
            {
                "label": "Josef",
                "value": "10"
            },
            {
                "label": "Max",
                "value": "3"
            },
            {
                "label": "Roland",
                "value": "9"
            },
            {
                "label": "Stryria Max",
                "value": "6"
            }
        ]
    },
    "files": [],
    "searchPanes": {
        "options": []
    }
}

But this does not include the sql executed...what else can i do...I can send you the user+pw for the site as pm, if you would be ready to help me further?

regards
Max

MadMax76

sorry debug was not firing as I had it after "process"...sorry as I mentioned I am quite a beginner. Here now the query:

{
    "1": {
        "query": "SELECT DISTINCT  [rights_PID] as 'rights_PID', [NamePers] as 'NamePers' FROM  [V_NeueFreigeber] WHERE [INP_ID] IS NULL ",
        "bindings": []
    }
}

which seems strange too me as I have both conditions in there:

                ->where( 'Stapelname', $selectedStapelname )
                ->where( 'INP_ID', $selectedINP_ID ))

when changing the order, it always only uses the second/last where-statement. If I have only one where-statement, this one is used, but also then it says "IS NULL".

ALso when putting a fixed value like '123' instead of $selectedStapelname, the debugger always cmoes up with "IS NULL".

thanks
Max

allan

Hi Max,

There is indeed something odd going on there. Do you have it inside a closure function perhaps? It doesn’t appear to be from the code above, but I don’t have the complete listing, so I might be missing something. Could you show me the unabbreviated file please?

Also the unabbreviated JSON would be useful as well since you are using those variables elsewhere too.

Thanks,
Allan

MadMax76

I did send you all files invovled (i think so...) as a pm...

MadMax76

and here the full debug-part of the json. In the "Main query" the where-condition works:

    "debug": [
        {
            "query": "SELECT  [V_Freigaben].[ID_freigabe] as 'V_Freigaben.ID_freigabe', [V_Freigaben].[INP_ID] as 'V_Freigaben.INP_ID', [V_Freigaben].[Stapelname] as 'V_Freigaben.Stapelname', [V_Freigaben].[freig_person] as 'V_Freigaben.freig_person', [V_NeueFreigeber].[NamePers] as 'V_NeueFreigeber.NamePers', [V_Freigaben].[freig_person2] as 'V_Freigaben.freig_person2', [V_users].[Namen] as 'V_users.Namen', [V_Freigaben].[freig_angefordert_wann] as 'V_Freigaben.freig_angefordert_wann', [V_Freigaben].[freig_angefordert_wie] as 'V_Freigaben.freig_angefordert_wie', [V_Freigaben].[freig_erledigt] as 'V_Freigaben.freig_erledigt', [FREIG_Status].[fre_stat_text] as 'FREIG_Status.fre_stat_text', [V_Freigaben].[freig_erledigt_wann] as 'V_Freigaben.freig_erledigt_wann', [V_Freigaben].[freig_abl_grund] as 'V_Freigaben.freig_abl_grund', [V_Freigaben].[freig_angefordert_von] as 'V_Freigaben.freig_angefordert_von', [anforderer].[Namen] as 'anforderer.Namen', [V_Freigaben].[freig_zahlsperre] as 'V_Freigaben.freig_zahlsperre', [V_Freigaben].[freig_zahlsp_grund] as 'V_Freigaben.freig_zahlsp_grund' FROM  [V_Freigaben] LEFT JOIN [V_users] ON [V_users].[PID] = [V_Freigaben].[freig_person]  LEFT JOIN [V_NeueFreigeber] ON [V_NeueFreigeber].[INP_ID] = V_Freigaben.INP_ID AND V_NeueFreigeber.Stapelname = V_Freigaben.Stapelname COLLATE Latin1_General_100_CI_AS AND V_NeueFreigeber.rights_PID = V_Freigaben.freig_person  LEFT JOIN V_users as anforderer ON [anforderer].[PID] = [V_Freigaben].[freig_angefordert_von]  LEFT JOIN [FREIG_Status] ON [FREIG_Status].[fre_stat_id] = [V_Freigaben].[freig_erledigt] WHERE [V_Freigaben].[Stapelname] = :where_0 AND  [V_Freigaben].[INP_ID] = :where_1 ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": "920_20210124_21.51.40.343_",
                    "type": null
                },
                {
                    "name": ":where_1",
                    "value": "4",
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT DISTINCT  [rights_PID] as 'rights_PID', [NamePers] as 'NamePers' FROM  [V_NeueFreigeber] WHERE [V_NeueFreigeber].[INP_ID] IS NULL ",
            "bindings": []
        },
        {
            "query": "SELECT DISTINCT  [PID] as 'PID', [Namen] as 'Namen' FROM  [V_users] ",
            "bindings": []
        },

MadMax76

can I move this from "free community support" to the "paid" one??

allan

Hi Max,

That's it moved over. To be honest, the category of the discussion doesn't actually make any difference. Priority support threads such as yours get highlighted in red for me regardless of category .

Thanks for the files!

I think I might see the issue - specifically in the chaining of the where method. Could you try the following for me please?

            ->options( Options::inst()
                ->table( 'V_NeueFreigeber' )
                ->value( 'rights_PID' )
                ->label( 'NamePers' )
                ->where( [
                    'INP_ID' => $selectedINP_ID,
                    'Stapelname' => $selectedStapelname
                )
            )

That is a little different from the behaviour of Editor->where() method where they can be chained for additional conditions. I think that should be changed in the code...

Could you try that and let me know how you get on with it please?

Thanks,
Allan

MadMax76

Here the working solution for multiple conditions in the options:

            ->options( Options::inst()
                ->table( 'V_NeueFreigeber' )
                ->value( 'rights_PID' )
                ->label( 'NamePers' )
                ->where( function ( $q ) use ( $selectedStapelname, $selectedINP_ID  ) {
                        $q->where( 'Stapelname', $selectedStapelname )
                            ->and_where( function ( $r )  use ( $selectedINP_ID ) {
                                $r->where( 'INP_ID', $selectedINP_ID );});
                            } )
                     )