Mjoin error with Postgres

Mjoin error with Postgres

axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

Hello,
maybe someone can help me. It looks for me like there is a failure in the table name escape if you use Mjoin.

I have a table with 2 left join and one multiple join.
The left join's are working as expected.

Now I have added the mjoin:

        ->leftJoin( 'PortgroupsStatus', 'PortgroupsStatus.id', '=', 'Portgroups.id_Status' )
        ->leftJoin( 'PortgroupConnectiontype', 'PortgroupConnectiontype.id', '=', 'Portgroups.id_Contype' )
        ->join(
        Mjoin::inst( 'AdminSegmentTypes' )
            ->link( 'Portgroups.id', 'n2n_portgroup_destination2nettype.id_portgroup_dest' )
            ->link( 'AdminSegmentTypes.id', 'n2n_portgroup_destination2nettype.id_nettype' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'AdminSegmentTypes' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name_destination' )
            )
    )

But I got an error:
an SQL error occurred: SQLSTATE[42P01]: Undefined table: 7 FEHLER: Relation »portgroups« existiert nicht\nLINE 1: ...s\".\"name_destination\" as \"name_destination\" FROM Po...\n

Debug output:

query: "SELECT DISTINCT  \"Portgroups\".\"id\" as \"dteditor_pkey\", \"AdminSegmentTypes\".\"id\" as \"id\", \"AdminSegmentTypes\".\"name_destination\" as \"name_destination\" FROM  Portgroups as Portgroups  JOIN \"n2n_portgroup_destination2nettype\" ON \"Portgroups\".\"id\" = \"n2n_portgroup_destination2nettype\".\"id_portgroup_dest\"   JOIN \"AdminSegmentTypes\" ON \"AdminSegmentTypes\".\"id\" = \"n2n_portgroup_destination2nettype\".\"id_nettype\" WHERE \"Portgroups\".\"id\" IN (:wherein1, :wherein2,)  ORDER BY \"name\"  asc 

The name of the table is not correct escaped anymore. Postgres now use lower case (portgroups instead of Portgroups).
For the other joins it is correct.

I have installed the last Editor version 2.07 and also 2.06.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    FROM  Portgroups as Portgroups
    

    That is certainly the problem in the SQL. Postgres will fold unescaped identifiers down to lowercase.

    You note you are using Editor 2.0.7 - are you using the 2.0.7 PHP libraries on the server-side as well (search in Editor.php for version)? The reason I ask is that with some of the newer updates the as shouldn't be there any more, which makes me think you might have older PHP libraries for Editor.

    Regards,
    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Hello Allan,
    thanks for support.

    The version number I see in the Editor.php is

    public $version = '2.0.7';
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Could you check that this line is in the corresponding copy that you have? That is what should be stripping the as which is appearing in your debug.

    Aside from that, honestly I'm not sure! I would probably need a clone of your project or access to the system to find the difference.

    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Sorry for the late reply.
    I found a failure in my autoloader so that the version 2.0.5 was used. - Fixed now.
    But also in the new version it will not escape the table names correct. But this time not during the join
    Still

    {"query":"SELECT DISTINCT  \"Portgroups\".\"id\" as \"dteditor_pkey\", \"AdminSegmentTypes\".\"id\" as \"id\", \"AdminSegmentTypes\".\"name_destination\" as \"name_destination\" FROM  \"Portgroups\" Portgroups  JOIN \"n2n_portgroup_destination2nettype\" ON \"Portgroups\".\"id\" = \"n2n_portgroup_destination2nettype\".\"id_portgroup_dest\"   JOIN \"AdminSegmentTypes\" ON \"AdminSegmentTypes\".\"id\" = \"n2n_portgroup_destination2nettype\".\"id_nettype\" WHERE \"Portgroups\".\"id\" IN 
    
    FROM Portgroups Portgroups 
    

    instead of

    FROM "Portgroups" "Portgroups" 
    

    I cannot clone the application :neutral:

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Here it the code of the application:

    <?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\Validate,
            DataTables\Editor\ValidateOptions;
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'Portgroups','id' )
            ->field(
                    Field::inst( 'Portgroups.id_Status' )
                            ->options( Options::inst()
                            ->table( 'PortgroupsStatus' )
                            ->value( 'id' )
                            ->label( 'status' )
                            )
                            ->validator( Validate::dbValues() )
                            ->validator ('Validate::notEmpty')
                    ,
                    Field::inst( 'PortgroupsStatus.status'),
    
                    Field::inst( 'Portgroups.id_Contype' )
                            ->options( Options::inst()
                            ->table( 'PortgroupConnectiontype' )
                            ->value( 'id' )
                            ->label( 'pgr_contype' )
                            )
                            ->validator( Validate::dbValues() )
                            ->validator ('Validate::notEmpty')
                    ,
                    Field::inst( 'PortgroupConnectiontype.pgr_contype')
    
            )
            ->leftJoin( 'PortgroupsStatus', 'PortgroupsStatus.id', '=', 'Portgroups.id_Status' )
            ->leftJoin( 'PortgroupConnectiontype', 'PortgroupConnectiontype.id', '=', 'Portgroups.id_Contype' )
    
            ->join(
            Mjoin::inst( 'SegmentTypes' )
                ->link( 'Portgroups.id', 'n2n_portgroup_destination2nettype.id_portgroup_dest' )
                ->link( 'SegmentTypes.id', 'n2n_portgroup_destination2nettype.id_nettype' )
                ->order( 'name asc' )
                ->fields(
                    Field::inst( 'id' )
                        ->validator( Validate::required() )
                        ->options( Options::inst()
                            ->table( 'SegmentTypes' )
                            ->value( 'id' )
                            ->label( 'name' )
                        ),
                    Field::inst( 'name' )->set( false )
                )
        )
    
    
            ->debug(true)
            ->process( $_POST )
            ->json();
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Many thanks. I've just tried running it locally and this is the SQL for the four queries it generates:

    SELECT 
        "Portgroups"."id" as "Portgroups.id",
        "Portgroups"."id_Status" as "Portgroups.id_Status",
        "PortgroupsStatus"."status" as "PortgroupsStatus.status",
        "Portgroups"."id_Contype" as "Portgroups.id_Contype",
        "PortgroupConnectiontype"."pgr_contype" as "PortgroupConnectiontype.pgr_contype"
    FROM  "Portgroups"
    LEFT JOIN "PortgroupsStatus" ON "PortgroupsStatus"."id" = "Portgroups"."id_Status" 
    LEFT JOIN "PortgroupConnectiontype" ON "PortgroupConnectiontype"."id" = "Portgroups"."id_Contype"
    
    SELECT DISTINCT
        "id" as "id",
        "status" as "status"
    FROM "PortgroupsStatus" 
    
    SELECT DISTINCT
        "id" as "id",
        "pgr_contype" as "pgr_contype"
    FROM "PortgroupConnectiontype" 
    
    SELECT DISTINCT
        "id" as "id",
        "name" as "name"
    FROM "SegmentTypes" 
    

    I don't have your database schema here, but looking at the queries, that looks correct I think. If you run them against your db, do they complete successfully?

    If so, it suggests to me that the PHP libraries we are using are slightly different. Perhaps you could download the very latest from github and try them?

    Thanks,
    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Hi Alan,

    your SQL commands are working as you have expected.
    I have to find the difference in my environment.

    Thanks for support.

Sign In or Register to comment.