Symfony Propel: Join Query Across different Database

I have two tables that should be joined together by a foreign key relationship, unfortunately they are separate into two different database.

My question is that is it possible for me to create a Criteria thingy that allows me to create cross database join query?

-------------Problems Reply------------

The answer is "no" without doing something exotic. If you look at the source of the Criteria object, it only supports one database name.

You've got two options:

1) Do two queries and do the join yourself. Unless you're very well versed in your particular database, this is the one to go with. Just make sure that each query includes your join field, then foreach over one while you lookup and add from the other.

2) Configure a view or a stored procedure in one database that calls the second database and joins the tables virtually. As far as PHP knows, it's only dealing with one database. I know Oracle can do this -- MySql might be able to, with some expert configuration. (More than my meager skill.)

-- update in response to your comment --

I haven't done this so I don't have sample code. Here's how I'd figure it out. (Off the top of my head, so don't take as gospel...)

Start here with your configuration: http://www.symfony-project.org/book/1_2/08-Inside-the-Model-Layer#chapter_08_database_connections.

Let's say your two connections were named "db1" and "db2".

Supply a database name when creating your criteria object:

$crit1 = new Criteria("db1");
$crit2 = new Criteria("db2");

Do your selects on your two database classes.

$dataOnes = DataOnePeer:doSelect($crit1);
$dataTwos = DataTwoPeer::doSelect($crit2);

Then put them together somehow...

foreach ($dataOnes as $d1) {
$joinKey = $d1->getMyJoinColumn();
$d2 = findByKey($dataTwos, $joinKey);
if (!empty($d2)) {
$d1->myD2 = $d2;
}
}

function findByKey($dataTwoArr, $key) {
foreach($dataTwoArr as $d2) {
if ($key == $d2->getMyJoinColumn()) {
return $d2;
}
}
return null;
}

This supposes a 1-1 relationship. You'll have to modify for 1-many.

Good luck!

Category:symfony1 Views:21 Time:2009-08-28

Related post

  • Symfony left join query 2010-11-16

    I've got the following query which is running correctly: $q = $this->createQuery('e') ->where('e.Persons_idUser =?', $request) ->leftJoin('e.JobTitles jt') ->leftJoin('e.EmploymentLevels el'); but when I'm iterate through the result and t

  • Connecting multiple database and join query across database in php 2012-01-17

    Using php i wants to make a join query across 2 database. This is my first connection. $conn = mysql_connect('localhost','root1','pass1'); @mysql_select_db('database1',$conn); This is my second connection. $conn1 = mysql_connect('localhost','root2','

  • Symfony Propel Project: How to upgrade schema without database resets? 2010-02-05

    I've deployed my symfony project to the server and now I wanna change the models inside schema.yml without reseting other unrelated database tables and keep the current data. Is there any diff / upgrade feature for symfony propel project ? ----------

  • Symfony Propel Multiple Database Error 2010-11-19

    I did it but It did not work. I created: propel.schema.xml propel.schema.yml # config/propel.schema.yml propel: article: id: ~ the other db configuration is orgdb.schema.xml <?xml version="1.0" encoding="UTF-8"?> <database name="orgdb" defau

  • In web applications, which is more realible, 1 join query for 4 tables or 4 individual database calls? 2011-11-29

    In PHP, what is more efficient? 1 join query to 4 tables OR 4 individual database calls? Limit will be maximum 10 in every case. The tables are: approvals id | content | user_id | time_stamp users id | name | email labels id | name label_relations id

  • Symfony Propel criteria 2009-01-19

    Is there any possible way to convert the MySQL object into criteria object? I tried this query: select p.disrepid, p.subject, p.body, c.disrepid as disrepid1, c.subject as subject1, c.body as body1 from discusreply as p, discusreply as c where p.dist

  • How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table? 2008-10-06

    I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, lik

  • How to create a dataview In Sharepoint with data from a join query? 2009-01-22

    I have 3 Lists in Sharepoint. I want to create a dataview that is a join of 3 tables. Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB Table1 has duplicate values in FieldA so I need to only return one value to join with T

  • Join query or subquery 2009-03-18

    Are there rules of thumb for developers when to use join instead of subquery or are they the same. --------------Solutions------------- Depends on RDBMS. You should compare execution plans for both queries. In my experience with Oracle 10 and 11, exe

  • Which MySQL JOIN query is more efficient? 2009-11-12

    Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more eff

  • C# Display Join Query in DataGridView at designTime using TableAdapter, etc 2009-12-04

    I have a DataGridView I also have some tableAdapters (groupTableAdapter, userTableAdapter) generated from sqlserver database. I have created a JOIN query in userTableAdapter that shows users with their correspoding groupname. Of course, I've got the

  • mysql join query using like? 2009-12-18

    I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found... SELECT * FROM Table1

  • Doctrine2 many-to-one association won't use JOIN query 2010-11-12

    I have a Car entity with a many-to-one relationship with an entity Owner. If I select all cars, Doctrine does one query on the Car table, and subsequently one query on the Owner table for each car. So fetching N cars becomes N+1 queries instead of a

  • symfony program hangs up when calling database 2010-12-02

    Hi I am using the Doctrine ORM with symphony, was able to build the tables with the database generator but when I try to run the http://localhost:8080//frontend_dev.php/cines it hangs up on the line with the query as I can see in the log symfony [inf

  • Using Lucene to query an RDBMS database 2011-01-17

    I've skimmed the docs for the Java version of Lucene, but I can't really see the top-level "this is how it works" info so far (I'm aware I need to RTFM, I just can't see the wood for the trees). I understand Lucene uses search indexes to return resul

  • How to query huge MySQL databases? 2011-01-22

    I have 2 tables, a purchases table and a users table. Records in the purchases table looks like this: purchase_id | product_ids | customer_id --------------------------------------- 1 | (99)(34)(2) | 3 2 | (45)(3)(74) | 75 Users table looks like this

  • MySQL join query performance issue 2011-03-11

    I am running the be query SELECT packages.id, packages.title, subcat.id, packages.weight FROM packages ,provider, packagestosubcat, packagestocity, subcat, usertosubcat, usertocity, usertoprovider WHERE packages.endDate >'2011-03-11 06:00:00' AND

  • LINQ performing multiple queries instead of a single "joined" query 2011-04-11

    I am not a LINQ power user by any means, but can fumble my way around on a basic level. I have a question regarding how LINQ formulates it's query "strategy". I will try to explain this as best as I can and write an extremely dumbed-down example by m

  • Get all contacts and their details (e.g. postal address) in one OUTER JOIN query 2011-05-01

    I know how to retrieve contact data for specific contacts. However, i can't find a way to get all contacts plus some of their details in a single query. The following code gets all contacts having a postal address: Uri uri = ContactsContract.CommonDa

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.147 (s). 11 q(s)