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

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

UMR5558UMR5558 Posts: 41Questions: 13Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • UMR5558UMR5558 Posts: 41Questions: 13Answers: 0
    edited January 2023

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • UMR5558UMR5558 Posts: 41Questions: 13Answers: 0

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • UMR5558UMR5558 Posts: 41Questions: 13Answers: 0

    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

Sign In or Register to comment.