Level 102
Let me get this straight. You are trying to do a join between two servers? Can you show us how you would do that in your database manager?
Jan 28, 2022
7
Level 1
defining 2nd database connection inside a query
so i have this query where i have to take joins between two tables in different databases Works fine for me on one server but not on another where it gives me a connection error so i'm trying to explicitly define connection inside the query is there any way i can do that?
$document_changes = DocumentChange::select([
"document_changes.id", "document_changes.doc_id", "changes", "document_changes.document_title", "document_changes.url", "document_changes.created_at", "document_changes.downloaded_doc_link"
])->where("document_changes.id", '>', 0);
if ($state_array[0] != "" || $vertical_array[0] != "" || $document_array[0] != "") {
// HEREEEEE
i wanna define 2nd connection name here like this connection2.database2.table1'
$document_changes = $document_changes->join('database2.table1', 'document_changes.doc_id', '=', 'database2.table1.id');
}
```
Level 1
@Sinnbeck no what i'm saying is my query works fine on one server with two databases but if i deploy it on another gcp instance i get connection error Ofc i know basic stuff like that I just wanna explicitly declare my 2nd connection name explicitly inside the query at the start of 2nd table name at which prod server doesnt throws connection error but not on my staging server I know u can do DB:connection("mysql2") etc etc
Level 102
@Haseeb69 it is still converted to raw sql. So can you show how it should look in mysql syntax? Your above code looks like it is trying to do a join between two servers
Level 73
@Haseeb69 Have you set both databases as federated?
https://stackoverflow.com/questions/810349/mysql-cross-server-select-query/1546339#1546339
1 like
Level 1
@Sinnbeck @tray2 I think u guys didn't get my point what I'm trying to do here is to take a join between two databases that are present on the same server but I'm writing a raw query so I want to explicitly define my connection string prefix at the start of 2nd database table name. The reason why I didn't define connection string with the DocumentChanges is that I'm using the model on that and laravel knows it is based on connection 1. i just want a connection prefix with 2nd database table on raw query.
Level 73
Level 102
@Haseeb69 then your original code looks correct.. It should just be databasename.table. What error are you getting? Are you sure that the user for database 1 has acces to database 2?
Please or to participate in this conversation.