http://stackoverflow.com/questions/3451856/multiple-database-in-single-query-is-possible

When you have correct SQL use raw expressions in Laravel http://laravel.com/docs/queries#raw-expressions. I doubt you can use ORM for this task however some say they have some success http://laravel.io/forum/05-27-2014-use-eloquent-relationships-with-another-database

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.

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

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.