Mjoin error with Postgres

axel_tsys

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.

allan

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_tsys

Hello Allan,
thanks for support.

The version number I see in the Editor.php is

public $version = '2.0.7';

allan

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_tsys

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

axel_tsys

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();

allan

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_tsys

Hi Alan,

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

Thanks for support.