Laravel 4, retrieve data from multiple postgresql-schemas

Question

I'm working on a project with Laravel 4 where I use a postgres-db. The public-schema is the main schema for the admin. All the other custom schemas are for clients. All schemas are having the same tables setup.

But for some clients, the must retrieve data multiple schemas. Is this possible with L4? Or do I have to write custom queries?

These are my settings for the db-connection

'pgsql' => array(
        'driver'   => 'pgsql',
        'host'     => 'localhost',
        'database' => 'postgres',
        'username' => 'postgres',
        'password' => 'root',
        'charset'  => 'utf8',
        'prefix'   => '',
        'schema'   => 'public, client1, client2',
    ),

But when I'm preforming the query:

$users = Users::all()
**OR**
$users = DB::select(DB::raw('SELECT * FROM client1.users, client2.users'));

I'm only retrieving the users from public. Did I miss something or isn't this possible with Laravel?

Thanks in advance

Adam Rodriguez · Accepted Answer · 2021-06-03 17:02:21Z

2

Its fairly easy to do with Laravel.

In your config:

...

'pgsql2' => array(
    'driver'   => 'pgsql',
    'host'     => 'localhost',
    'database' => 'postgres',
    'username' => 'postgres',
    'password' => 'root',
    'charset'  => 'utf8',
    'prefix'   => '',
    'schema'   => 'client1',
),

...

In your model:

// Define your second db connection
protected $connection = 'pgsql2';
// Define your table
protected $table = 'users';

If have not tested this, so if that doesn't work:

// Define your table
protected $table = 'client1.users';

in your model.

edited Jun 3, 2021 at 17:02

answered Jan 13, 2014 at 19:05

Adam Rodriguez

1,8561 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Gorjan Over a year ago

Thanks for your answer, but I was hoping if I could get records from multiple schema's in one query. But after some research, I figured it out that it isn't possible. Or I am wrong?

Adam Rodriguez Over a year ago

You should be able to define relationships on your models the same way as a single schema(I have done this and it works) and use Eloquent to query your data. The fluent query builder should work the same way, just make sure your table is defined as I have shown above.

Taai · Accepted Answer · 2014-03-14 11:40:06Z

You don't need to specify other schema in your config, leave it as public.

Your query was wrong - that's all. This is how it should be:

SELECT * FROM client1.users UNION ALL SELECT * FROM client2.users

This query will select everything from both tables. You can UNION ALL selects from tables as many you need.

You may want to determine which user is in which schema, so just add an identifier (variable) in your query:

SELECT 1 AS s, * FROM client1.users
UNION ALL
SELECT 2 AS s, * FROM client2.users
UNION ALL
SELECT 3 AS s, * FROM client3.users

The output would look like this:

| s | id | name   |
|---|----|--------|
| 1 | 1  | John   |
| 1 | 2  | Lucy   |
| 2 | 1  | Robert |
| 3 | 1  | Kelly  |
| 3 | 2  | Sam    |

user2681858 · Accepted Answer · 2020-09-16 13:35:40Z

0

You need to use

'schemas' => 'public, client1, client2'

if you want to use multiple schemas.

For example when you are using procedures from different schemas.

If you will use

'schema' => 'public, client1, client2'

it will choose the first in the list.

answered Sep 16, 2020 at 13:35

user2681858

1

Collectives™ on Stack Overflow

Laravel 4, retrieve data from multiple postgresql-schemas

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related