Mjoin and alias

chipaldance

Hi,

I want to do a relation one to multiple.

I have a table "centers" with this fields : "cen_id" and "cen_title".
And another table "centers_centers" with this fields : "cc_cen_id" and "cc_parent"

The content of cc_parent is the cen_id of the parent center. So one center can have several parents centers.

So I use a Mjoin for my one to multiple relation but I have to use 2 times the "centers" table. This is impossible in mysql and I receive an error. I want to use an alias like with leftJoin but it seems impossible, I receive an error too.

What is the solution ?

Thanks,
Gaétan

allan

The Mjoin class has a method called name() which can be used to modify the JSON name for the resulting data:

Mjoin::inst( 'access' )
   ->name( 'access2' )
   ...

That basically provides an alias so you can use the same target Mjoin table multiple times. This shouldn't create an SQL conflict as it is actually a separate SQL statement that Editor executes for each Mjoin.

Allan

chipaldance

Hi,
Thanks for your answer, it's seems to be the good issue :)

But, this is my code :

$editor = Editor::inst( $db, 'centres', 'cen_id' );
        $editor->fields(
            Field::inst( 'centres.cen_id'),
            Field::inst( 'centres.cen_titre' ),
            Field::inst( 'centres.cen_exclusivite' )
        );
        
        $editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centrealias' )
                ->link( 'centres.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centrealias.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'cen_titre' )
                )
        );

And I receive :
{"error":"SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table\/alias: 'centres'","data":[]}

allan

Ah I see - thanks for the code. You need to use the Mjoin->aliasParentTable() method in that case - it will do a proper alias:

Mjoin::inst( 'centres' )
  ->aliasParentTable( 'c2' )

Then refer to c2 as the table name for the parent table.

Allan

chipaldance

Hi Allan,

Sorry, I don't understand why my code doesn't work...
This is my code with name for the alias of my Join table and aliasParentTable for my master table.

$editor = Editor::inst( $db, 'centres', 'cen_id' );
        $editor->fields(
            Field::inst( 'centres.cen_id'),
            Field::inst( 'centres.cen_titre' ),
            Field::inst( 'centres.cen_exclusivite' ),
            Field::inst( 'centres.cen_date' )
        );
        
        
        $editor->join(
            Mjoin::inst( 'centres' )
                ->aliasParentTable( 'c2' )
                ->name( 'centrealias' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centrealias.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'cen_titre' )
                )
        );

I receive : {"error":"SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table\/alias: 'c2'","data":[]}

Where is wrong ??

Thanks again ;)

allan

Could you open the file in Database/Drivers/Mysql/Query.php and in the _prepare function you will see a commented out debug line (file_put_contents()) could you comment it back in and change the location that the file writes to (if you need to) then run the script again please.

It will dump the generated query to that file. Then copy and paste the queries shown into this discussion so I can take a look.

Allan

chipaldance

With this code, the sql is :

SELECT
  cen_id AS 'cen_id',
  centres.cen_id AS 'centres.cen_id',
  centres.cen_titre AS 'centres.cen_titre',
  centres.cen_exclusivite AS 'centres.cen_exclusivite',
  centres.cen_date AS 'centres.cen_date'
FROM centres
SELECT
  centres.cen_id AS dteditor_pkey,
  centres.cen_id AS cen_id,
  centres.cen_titre AS cen_titre
FROM centres AS c2
JOIN c2
  ON c2.cen_id = c2.cm_cen_id
JOIN centres
  ON centres.cen_id = c2.cm_mut

There isn't "centrealias" ? And two times "c2"

Edited by Allan - formatting of the SQL for readability

allan

Hi,

Sorry for the delay in replying! Two issues here:

1. The centrealias is a concept that is entirely in PHP / JSON - it is not used in the SQL at all so it shouldn't be referred to in the SQL field names.
2. I'm afraid that there is a bug in Editor when the parent alias is used with a link table as is the case here. The fix will be in Editor 1.5.4 which will be released shortly, but until then, in the Join.php file replace the _prep method with the following which has the fix in place:

    private function _prep ( $editor )
    {
        $links = $this->_links;

        // Were links used to configure this instance - if so, we need to
        // back them onto the join array
        if ( $this->_join['parent'] === null && count($links) ) {
            $editorTable = $editor->table();
            $editorTable = $editorTable[0];
            $joinTable = $this->table();

            if ( $this->_aliasParentTable ) {
                $editorTable = $this->_aliasParentTable;
            }

            if ( count( $links ) === 2 ) {
                // No link table
                $f1 = explode( '.', $links[0] );
                $f2 = explode( '.', $links[1] );

                $this->_join['parent'] = $f1[1];
                $this->_join['child'] = $f2[1];
            }
            else {
                // Link table
                $f1 = explode( '.', $links[0] );
                $f2 = explode( '.', $links[1] );
                $f3 = explode( '.', $links[2] );
                $f4 = explode( '.', $links[3] );

                // Discover the name of the link table
                if ( $f1[0] !== $editorTable && $f1[0] !== $joinTable ) {
                    $this->_join['table'] = $f1[0];
                }
                else if ( $f2[0] !== $editorTable && $f2[0] !== $joinTable ) {
                    $this->_join['table'] = $f2[0];
                }
                else if ( $f3[0] !== $editorTable && $f3[0] !== $joinTable ) {
                    $this->_join['table'] = $f3[0];
                }
                else {
                    $this->_join['table'] = $f2[0];
                }

                $this->_join['parent'] = array( $f1[1], $f2[1] );
                $this->_join['child'] = array( $f3[1], $f4[1] );
            }
        }
    }

Regards,
Allan

chipaldance

Hi Allan,

This doesn't work.

With this code :

$editor = Editor::inst( $db, 'centres', 'cen_id' );
        $editor->fields(
            Field::inst( 'centres.cen_id'),
            Field::inst( 'centres.cen_titre' ),
            Field::inst( 'centres.cen_exclusivite' ),
            Field::inst( 'centres.cen_date' )
        );
        $editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centre_copy' )
                ->aliasParentTable( 'c2' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centre_copy.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'centre_copy.cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'centre_copy.cen_titre' )
                )
        );

I receive :
{"sError":"Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing. --- name -> centre_copy.cen_id --- set -> both --- dbField -> centre_copy.cen_id --- strpos . dbField -> 11 --- "}

And with this code :

$editor = Editor::inst( $db, 'centres', 'cen_id' );
        $editor->fields(
            Field::inst( 'centres.cen_id'),
            Field::inst( 'centres.cen_titre' ),
            Field::inst( 'centres.cen_exclusivite' ),
            Field::inst( 'centres.cen_date' )
        );

        $editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centre_copy' )
                ->aliasParentTable( 'c2' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centre_copy.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'cen_titre' )
                )
        );

The query is :
SELECT centres.cen_id as dteditor_pkey, centres.cen_id as cen_id, centres.cen_titre as cen_titre FROM centres as c2 JOIN centres_mut ON c2.cen_id = centres_mut.cm_cen_id JOIN centres ON centres.cen_id = centres_mut.cm_mut

So, this query don't return the list of attached centres.

I have an error or it's a bug ?

Thanks again,
Gaétan

edited by Allan Code highlighting

allan

Hi Gaétan,

->link( 'centre_copy.cen_id', 'centres_mut.cm_mut' )

This line is wrong. As I say, the name() should not be referred to in the SQL field names. I would suggest trying:

        $editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centre_copy' )
                ->aliasParentTable( 'c2' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centres.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'cen_titre' )
                )
        );

p.s. Details on how to highlight code using markdown can be found in this guide.

chipaldance

Yes but this code return :
{"data":[{"DT_RowId":"row_1","centres":{"cen_id":"1","cen_titre":"centre 1","cen_exclusivite":"1","cen_date":"0000-00-00 00:00:00"},"centre_copy":[{"cen_id":"1","cen_titre":"centre 1"}]},{"DT_RowId":"row_2","centres":{"cen_id":"2","cen_titre":"Centre 2","cen_exclusivite":"0","cen_date":"0000-00-00 00:00:00"},"centre_copy":[{"cen_id":"2","cen_titre":"Centre 2"},{"cen_id":"2","cen_titre":"Centre 2"}]},{"DT_RowId":"row_3","centres":{"cen_id":"3","cen_titre":"centre 3","cen_exclusivite":"1","cen_date":"0000-00-00 00:00:00"},"centre_copy":[{"cen_id":"3","cen_titre":"centre 3"},{"cen_id":"3","cen_titre":"centre 3"}]},{"DT_RowId":"row_4","centres":{"cen_id":"4","cen_titre":"centre4","cen_exclusivite":"0","cen_date":"0000-00-00 00:00:00"},"centre_copy":[{"cen_id":"4","cen_titre":"centre4"}]}],"options":{"centres[].cen_id":[{"value":"1","label":"centre 1"},{"value":"2","label":"Centre 2"},{"value":"3","label":"centre 3"},{"value":"4","label":"centre4"}]},"files":[]}

For each "centre", I receive a list of attached centers. The number of attached centers is ok but data are data of the master center :
"centre_copy":[{"cen_id":"2","cen_titre":"Centre 2"},{"cen_id":"2","cen_titre":"Centre 2"}]

And if I use :

$editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centre_copy' )
                ->aliasParentTable( 'c2' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centres.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'centres.cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'centres.cen_titre' )
                )
        );

I receive :
{"sError":"Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing. --- name -> centres.cen_id --- set -> both --- dbField -> centres.cen_id --- strpos . dbField -> 7 --- "}

allan

Try this:

$editor->join(
            Mjoin::inst( 'centres' )
                ->name( 'centre_copy' )
                ->aliasParentTable( 'c2' )
                ->link( 'c2.cen_id', 'centres_mut.cm_cen_id' )
                ->link( 'centres.cen_id', 'centres_mut.cm_mut' )
                ->fields(
                    Field::inst( 'cen_id' )
                        ->options( 'centres', 'cen_id', 'cen_titre' ),
                    Field::inst( 'cen_titre' )
                )
        );

That will cause the join to read the fields from the joined table. You don't really want or need the table selector there.

Allan

chipaldance

Yes, I've tried this.
The query is :

SELECT cen_id as 'cen_id', centres.cen_id as 'centres.cen_id', centres.cen_titre as 'centres.cen_titre', centres.cen_exclusivite as 'centres.cen_exclusivite', centres.cen_date as 'centres.cen_date' FROM centres
SELECT centres.cen_id as dteditor_pkey, centres.cen_id as cen_id, centres.cen_titre as cen_titre FROM centres as c2 JOIN centres_mut ON c2.cen_id = centres_mut.cm_cen_id JOIN centres ON centres.cen_id = centres_mut.cm_mut
SELECT DISTINCT cen_id as value, cen_titre as label FROM centres ORDER BY label asc

So, I receive an array of attached centers for each center like this :
"centre_copy":[{"cen_id":"2","cen_titre":"Centre 2"},{"cen_id":"2","cen_titre":"Centre 2"}]

The number of attached centers is ok but the data are data of the master center.

allan

Are you able to e-mail me a dump of your centres and centres_mut tables please (allan@ this domain.net)? I'll try to recreate and debug the issue locally.

Allan

chipaldance

Thanks for your answer by email Allan :)

I always have a problem about this but with edition.
The request return this :

{
    "data": [{
        "DT_RowId": "row_1",
        "centres": {
            "cen_id": "1",
            "cen_titre": "centre 1",
            "cen_exclusivite": "1",
            "cen_date": "0000-00-00 00:00:00"
        },
        "centre_copy": [{
            "cen_id": "4",
            "cen_titre": "centre4"
        }, {
            "cen_id": "3",
            "cen_titre": "centre 3"
        }, {
            "cen_id": "2",
            "cen_titre": "Wiig"
        }, {
            "cen_id": "1",
            "cen_titre": "centre 1"
        }]
    }, {
        "DT_RowId": "row_2",
        "centres": {
            "cen_id": "2",
            "cen_titre": "Wiig",
            "cen_exclusivite": "0",
            "cen_date": "0000-00-00 00:00:00"
        },
        "centre_copy": []
    }, {
        "DT_RowId": "row_3",
        "centres": {
            "cen_id": "3",
            "cen_titre": "centre 3",
            "cen_exclusivite": "1",
            "cen_date": "0000-00-00 00:00:00"
        },
        "centre_copy": []
    }, {
        "DT_RowId": "row_4",
        "centres": {
            "cen_id": "4",
            "cen_titre": "centre4",
            "cen_exclusivite": "0",
            "cen_date": "0000-00-00 00:00:00"
        },
        "centre_copy": []
    }],
    "options": {
        "centres[].cen_id": [{
            "label": "Wiig",
            "value": "2"
        }, {
            "label": "centre 1",
            "value": "1"
        }, {
            "label": "centre 3",
            "value": "3"
        }, {
            "label": "centre4",
            "value": "4"
        }]
    },
    "files": []
}

Look at :

"options":{"centres[].cen_id":[{"label":"Wiig","value":"2"},{"label":"centre 1","value":"1"},{"label":"centre 3","value":"3"},{"label":"centre4","value":"4"}]},"files":[]}

It's "centres[].cen_id", I think that is a conflict with other data with alias “centres". So, when I edit a line, the request for save data contains centres array without title, ...
I think that the identifiant option must be centre_copy[].cen_id

This is my code for Editor in js :

var fields = new Array(
            {
                label: "Centre : ",
                name: "centres.cen_titre",
                data: "centres.cen_titre"
            },
            {
                label: "Programmes exclusifs : ",
                name: "centres.cen_exclusivite",
                data: "centres.cen_exclusivite",
                type: "radio",
                options: [
                    { label: "Oui", value: 1 },
                    { label: "Non", value: 0 }
                ]
            },
            {
                label: "Centres mutualisés : ",
                name: "centre_copy[].cen_id",
                data: "centre_copy[].cen_id",
                type: "checkbox"
            }
        );

allan

You are correct - thanks for pointing that out.

The workaround for the moment is to use initComplete:

initComplete: function ( e, settings, json ) {
  editor.field( 'centre_copy[].cen_id' ).update( json.options['centres[].cen_id'] );
}

Allan

chipaldance

Thanks, It's works !