Home / Questions / 451242
Accepted
Miguel
Asked: 2022-05-12 16:04:41 +0800 CST 2022-05-12 16:04:41 +0800 CST

Query of two different connections in Laravel

  • 772

Good evening, I am trying to make a query of different connection tables, I have a connection that is the usual one called mysqland I have created another called apolo, I need to do a join with both.

$usuarios = DB::connection('apolo')->table('users')
                    ->join('clientes AS client', 'users.intranet_id', '=', 'client.id')
                    ->select('users.*', 'client.name')
                    ->get();

As you can see I connect to the connection of apoloand I want to do a join with my table of clients of my usual connection mysqlthat I am doing wrong, I have done it like this many times without problem, I don't know what I am doing wrong.

Cheers

It throws me this error:

Base table or view not found: 1146 Table 'apolo2021.clientes' doesn't exist

You are looking for the customers table in the database which is not.

Edition

I'm trying like this but either, I don't know if it's because there is a table in both databases users.

$prueba = DB::Connection('apolo')
             ->table('users')
             ->join(DB::Connection('mysql'))                       
             ->table('clientes', 'users.intranet_id', '=', 'clientes.id')
             ->get();
             dd($prueba);

But it returns me this error:

Too few arguments to function Illuminate\Database\Query\Builder::join(), 1 passed in
laravel

1 Answers

  1. Best Answer

    The join function does not work for you since, according to the documentation, that function does not receive the connection as a parameter:

    Join Eloquent

    You can create a model of the Users table indicating that it should use the apollo connection.

    namespace App;

use Illuminate\Database\Eloquent\Model;

class Users extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'apolo';
}

    Then you make the relationship with the clients table one by one (I understand that each user is associated with a single client):

    class Users extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'apolo';

    /**
     * Get the client associated with the user.
     */
    public function client()
    {
        return $this->hasOne(Clientes::class, 'id','intranet_id');
    }
}

    One to one relationship in eloquent

    You can also map the relationship in Clients, it would be:

    class Clientes extends Model
{
    /**
     * Get the User associated with the client.
     */
    public function user()
    {
        return $this->hasOne(Users::class, 'intranet_id','id');
    }
}

    When getting the model you should be able to use the client() or user() function, depending on which model you searched for:

    $user = Users::find(1)->client;
$cliente = Clientes::find(1)->user;
    • 3