Editor with a database schema which is not public and which has a specific name

UMR5558

Hello everybody.
I use editor with Join tables - one-to-many join and a database in a specific schema. I can't manage to find the syntax for the Mjoin in the php file.
I try with 'as' syntax, with ->name but the problem is with the link table in '->link'.
In sql command you must specfy the schema for example 'schema.table' or 'schema.table.column'.
Can you help me to write the php file please?
Thanks for your help.
Lionel

allan

Hi Lionel,

You should be able to just add the schema name at the front of the links:

        Mjoin::inst( 'schema1.permission' )
            ->link( 'schema2.users.id', 'schema1.user_permission.user_id' )
            ->link( 'schema1.permission.id', 'schema1.user_permission.permission_id' )

If that isn't working for you, what error are you getting?

Allan

UMR5558

Hello Allan, thanks for your answer.
I don't understand why schema2 in the first link.
All my tables are in schema1 named labo in my case.
I ever tried your syntax but it send me this eror: An SQL error occurred:

SQLSTATE[42P01]: Undefined table: 7 ERREUR: la relation « labo » n'existe pas\nLINE 1: ....\"name\" as \"name\" FROM labo.users as users JOIN \"labo\" ON ...\n

My code:

Mjoin::inst( 'labo.permission')
            ->link( 'labo.users.id', 'labo.user_permission.user_id' )
            ->link( 'labo.permission.id', 'labo.user_permission.permission_id' )

That's why i test lot of things before my question in the forum

This code almost working and only if the table user.permission is in the public schema

Editor::inst( $db, 'labo.users as users')
    ->field( 
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'labo.sites' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'sites.name' ),

    )
    ->leftJoin( 'labo.sites', 'sites.id', '=', 'users.site' )
    ->join(
        Mjoin::inst( 'labo.permission')
            ->name( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.permission_id' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'labo.permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
    )
    ->debug(true)
    ->process($_POST)
    ->json();

Thanks for your help

allan

Ah! The default schema for the user is the public schema, but you want to use labo.

You want to use the USE statement I think:

$db->sql('USE labo');

Then remove the labo. from your configuration for Editor, and it should just work.

Allan

UMR5558

Is this solution working with a posgresql database ? Your link point on mysql dev site.
Thanks for all?

allan

No sorry - I'd assumed MySQL. I didn't realise you were using Postgres. For PG try:

$db->sql('SET search_path TO labo');

See the postgres docs for the search_path.

Allan

UMR5558

Hello Allan.
Tanks a lot for your help. it's now working very well. Thanks to you I learned a lot of things that will be very useful to me.
have a nice day.
Lionel