MJoin Multiple Databases

Restful Web Services

I have succesfully been using multiple databases in my application by simply adding the database name before the table name like so:

Editor::inst( $db, 'ridedata1-web1-uk.ride', 'ride_id' )

However, when attempting to use the same method with MJoin it is not working and I recieve the following SQL error:

An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Here is my code:

    ->join(
        Mjoin::inst( 'ridedata1-web1-uk.ride_carbon_data' )
            ->set( false )
            ->link( 'ride.ride_id', 'ride_carbon_data.ride_id' ) 
            ->fields(                                                        
                Field::inst( 'carbon_benefit' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '').'<br> kg / miles';              
                    }),              
                Field::inst( 'distance_replace' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '').'<br> miles';              
                    })                               
            )                                                
    )   

I have used many Mjoins in other places but always when trying to link a table in the same database. I have tried enclosing the database name in ` and " and ' but nothing helps.

Can anyone help me out?

Thanks

Chris

allan

Hi Chris,

This is an area that isn't well tested in the Editor libraries I'm afraid.

Could you add ->debug( true ) before the process() call in the Editor chain and then show me the JSON that is being returned by the server when the page is loaded? That should contain the SQL query and let me see why it is failing.

Allan

rf1234

Had a similar issue even though it was in the same database. It was about linking two rows from my contract table. One being a "derivative" the other one being the "underlying". Aliasing the table name didn't work. Same problem that you have with this line

Mjoin::inst( 'ridedata1-web1-uk.ride_carbon_data' )

Obviously Editor doesn't really escape complex names properly in this statement and I needed a simple name that doesn't contain a period or an alias.
So I created a view called "underlying" and used that in the Mjoin:

Mjoin::inst( 'underlying' )

This post shows that such a view can even be created across databases. You could call it "carbon_data_view". That should work.
https://stackoverflow.com/questions/10694601/creating-view-across-different-databases
Good luck!

Restful Web Services

Hi Both,

Thanks for your responses. Here is the debug JSON requested by Allan.

debugSql
bindings
[{name: ":where_1", value: "1", type: null}, {name: ":where_2", value: "2", type: null},…]

query
"SELECT  `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id` as 'freecyc7_ridedata1-web1-uk.ride.ride_id', `ride`.`ride_id` as 'ride.ride_id', `ride`.`create_date` as 'ride.create_date', `ride`.`create_date` as 'ride.create_date', `ride`.`modified_date` as 'ride.modified_date', `ride`.`branch` as 'ride.branch', `ride`.`customer_id` as 'ride.customer_id', `ride`.`customer_id` as 'ride.customer_id', `cmfu`.`first_name` as 'cmfu.first_name', `cmfu`.`last_name` as 'cmfu.last_name', `ride`.`customer_contract_id` as 'ride.customer_contract_id', `cc`.`contract_code` as 'cc.contract_code', `ride`.`type_id` as 'ride.type_id', `ride_type`.`name` as 'ride_type.name', `ride`.`start_latitude` as 'ride.start_latitude', `ride`.`start_longitude` as 'ride.start_longitude', `ride`.`start_time` as 'ride.start_time', `ride`.`finish_latitude` as 'ride.finish_latitude', `ride`.`finish_longitude` as 'ride.finish_longitude', `ride`.`finish_time` as 'ride.finish_time', `ride`.`moving_time` as 'ride.moving_time', `ride`.`maximum_speed` as 'ride.maximum_speed', `ride`.`average_speed` as 'ride.average_speed', `ride`.`distance` as 'ride.distance', `ride`.`description` as 'ride.description' FROM  `freecyc7_ridedata1-web1-uk`.`ride` LEFT JOIN cms_module_feusers_users as cmfu ON `cmfu`.`id` = `ride`.`customer_id`  LEFT JOIN customer_contract as cc ON `cc`.`customer_contract_id` = `ride`.`customer_contract_id`  LEFT JOIN `freecyc7_ridedata1-web1-uk`.`ride_type` ON `ride_type`.`ride_type_id` = `ride`.`type_id` WHERE (`ride`.`branch` = :where_1 AND `ride`.`customer_contract_id` = :where_2 AND `ride`.`ride_id` != :where_3 AND `ride`.`ride_id` != :where_4 )"

query
"SELECT DISTINCT  `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id` as 'dteditor_pkey', `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`carbon_benefit` as 'carbon_benefit', `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`distance_replace` as 'distance_replace' FROM  freecyc7_ridedata1-web1-uk.ride as freecyc7_ridedata1-web1-uk.ride  JOIN `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` ON `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id "

error
"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-web1-uk.ride as freecyc7_ridedata1-web1-uk.ride  JOIN `freecyc7_ridedata1-web1-' at line 1"
fieldErrors

Let me know if you need anything else?

Thanks

Chris

Restful Web Services

Hi Allan,

It is clear that the error is on the second query, the SELECT DISTINCT, query. The name is not correctly being encapsulated by the ` escape. However, having had a brief look through the PHP for Editor I cant see the exact place where this encapsulation is being done. Can you help point me in the right direction?

JOIN `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` ON `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id

Thanks

Chris

allan

Thanks Chris,

This is the statement that is failing:

SELECT DISTINCT `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id`                      AS 'dteditor_pkey', 
                `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`carbon_benefit`   AS 'carbon_benefit', 
                `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`distance_replace` AS 'distance_replace' 
FROM            freecyc7_ridedata1-web1-uk.ride                                    AS freecyc7_ridedata1-web1-uk.ride 
JOIN            `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` 
ON              `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id

The FROM clause isn't being correctly escaped.

Could you show me your full PHP script for the Editor initialisation please?

Thanks,
Allan

Restful Web Services

Thanks for your reply. Can I email it to you?

Restful Web Services

Hi Allan. I have sent it as PM message, let me know if you haven't received it.

Restful Web Services

Hi Allan. Sorry, the first PM I sent you was the wrong one. I have just sent the correct code.

allan

Thanks - I've got it now and will review shortly.

Allan

rf1234

That looks pretty much like the same thing causing my issue.

I had something like

Mjoin::inst( 'contract AS underlying' )

Sinc this wasn't escaped properly I devised the work around with the view. Would be good to hear back from you guys to know whether I'll still need the work around in the future. Many thanks.

allan

Yeah - as most certainly won't work in MJoin at the moment I'm afraid. That is something that needs to be added into the libraries.

I don't believe that is the issue with @Restful Web Services's issue though. It looks like there might be an issue with the detection of the database fields. More soon.

Allan

Restful Web Services

Thanks for the update.