Phalcon join multiple database in single query

Question

Phalcon join multiple database in single query

sravis Apr '14

Created Apr '14	Last Reply Jun '16	Replies 2	Views 1820	Votes 1

sravis 3.6k

Apr '14

Usually in SQL or Codeigniter's Active record we can join 2 different database tables in single query like:

join database_1.table_1 on ..
join database_2.table_2 on ..

How do we achieve this is "Phalcon"?

I tried to do it, but got an error:

SQLSTATE[42S02]: Base table or view not found:
1146 Table 'database_2.table_2' doesn't exist

1

Phalcon · Answer 1 · 2014-04-04T12:49:08-07:00

You can also join tables or models to your queries:

Plain PHQL: https://docs.phalcon.io/en/latest/reference/phql.html#joins
Using the query builder: https://docs.phalcon.io/en/latest/reference/phql.html#creating-queries-using-the-query-builder

sravis · Answer 2 · 2014-04-04T21:13:02-07:00

Hai; There is no problem at join tables in same database.

But my query is how do we JOIN tables in 2 different database? If i try to do it i get above error.

Please suggest some idea..

Todd Empcke · Answer 3 · 2016-06-07T13:09:40-07:00

We have a client who has 2 databases. One is readonly and controlled by seperate software. The 2nd was created so we could add modules to the application with our own tables. Our tables need to join with the readonly tables from the first database. This is easy to do with raw sql:

SELECT ...
FROM db1.employee
LEFT JOIN db2.user ON(db1.employee.empid = db2.user.empid)

But how can this be acomplishd with phalcon modle relationships ?