Support the ongoing development of Laravel.io →
posted 10 years ago
Database
Last updated 2 years ago.
0
Last updated 2 years ago.
0

I am familiar with constructing SQL statements that utilize tables in more than one database (as shown in your first link) using mysql command line or a tool like phpmyadmin, and have used raw expressions in Laravel for other purposes, and so neither of those are the problem for me.

The problem is that as far as I understand it, the only way to access a table in Laravel is through a connection created in the database.php file ('mysql1', 'mysql2', etc.) and I do not understand how to use two of these connections in one query. All the examples I can find assume both tables are in the same database, and so they use the same connection. I have played around with the syntax a lot, but with no success.

This seems like a trivial question but a lot of searching on the web and posts to other forums have resulted in nothing. I have to believe someone else is doing this.

Last updated 2 years ago.
0

I've done this, not sure if its the only way but i'm currently doing something like this - note i've just copied in some relevant not the full files

// config/database.php
'mysql' => array(
	'driver'    => 'mysql',
        ....
	'database'  => 'shop',
         ....
),

'mysql-search' => array(
	'driver'    => 'mysql',
        ....
	'database'  => 'shopsearch',
)

the mysql connection is my default one so on 90% of my models i do nothing else. on models that come from the shopsearch database I add the following to model file

class Search extends Eloquent {

	protected $table = 'search';
	protected $primaryKey = "subversion_id";

	protected $connection = 'mysql-search';

        public function version() {
		return $this->hasOne('Version', 'version_id', 'version_id');
	}

the version table is actually in the other database And this means I can do

$search = Search::with('Version')->get();

you can also specify the connection string on DB commands like

DB::connection('mysql-search')->select ... blah blah

Hope this helps you

K

Last updated 2 years ago.
0

Kpzani,

Thanks for your suggestion. After playing around and googling some more, I came to the conclusion / realization that Laravel has a number of commands that seem generally to fall under the heading of allowing 'raw' database queries, but not all seem to operate in quite the same way. What I found is that I could execute some MySQL commands directly without having to use the Laravel database connections that I had set up in the database.php file. So the following works just fine:

$success = DB::statement('DROP TABLE temptable');

For some SQL commands, DB::statement does not work, but using DB::select instead does. The following SQL command works as expected:

$success = DB:select('INSERT INTO temptable SELECT * FROM othertable WHERE othertable.column = temptable.column);

It's possible to construct and execute complex SQL commands using this approach.

Where I was going wrong was assuming that I had to use the Laravel connections (defined in database.php) in some form, even with a 'raw' query, whereas in reality I could just construct regular SQL commands and use them directly, completely ignoring the connections that I had configured (provided I figured out which of the Laravel DB:: commands would work in each case).

I like working with Laravel and now that I've got this figured out I can do quite a few things that I was struggling with in the context of Eloquent and the standard Laravel connections, although I can see that advantages of them in some situations.

I guess this is one of those things that is so obvious when you know it that it doesn't get documented in a way that is transparent to newbies. Anyway, having figured it out I thought I'd share this in case it's of any use to anyone else.

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

GSheffield gsheffield Joined 27 May 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.