headers
Fabian von Derschatta (united-domains AG | 3 Sep 2008 10:45
Picon
Favicon

Querying over multiple databases

Hello,

we use Propel for a big project and our database is growing and growing.
For a better overview we really would like to seperate tables into
different databases. But we would like to continue using and quering
these tables in one project.

With a custom SQL query I would easily do this by something like:
"SELECT * FROM database1.table1 JOIN database2.table2 ON table1.id =
table2.foreign_key"

This is how we have done it before we started to use Propel.

How can I do this in Propel without using SQL but using the generated
classes? Is this possible?

Kind Regards,
Fabian
Permalink | Reply | 9 comments |
headers
Hans Lellelid | 8 Sep 2008 00:53

Re: Querying over multiple databases

Hi Fabian,

What you want to do is not directly supported by Propel -- it's also a 
very MySQL-specific "feature".  The generic analogy would be to support 
querying over multiple schemas [in a single database].  MySQL doesn't 
support schemas, but seems to think that databases are equivalent to 
schemas (at least the "information_schema" is a separate "database" in 
MySQL).  Currently Propel doesn't support (using Criteria) queries 
across schemas either; however, that may be something that we could look 
into adding in the future.  Admittedly, I don't know the limitations of 
the support for this, as I tend to work within a single db schema.

Of course, you can always use SQL directly and hydrate propel objects 
using the results of the SQL query.

Hans

Fabian von Derschatta (united-domains AG) wrote:
> Hello,
>
> we use Propel for a big project and our database is growing and growing.
> For a better overview we really would like to seperate tables into
> different databases. But we would like to continue using and quering
> these tables in one project.
>
> With a custom SQL query I would easily do this by something like:
> "SELECT * FROM database1.table1 JOIN database2.table2 ON table1.id =
> table2.foreign_key"
>
> This is how we have done it before we started to use Propel.
(Continue reading)

Permalink | Reply | 5 comments |
headers
Thomas Ritter | 8 Sep 2008 09:19
Picon

Re: Querying over multiple databases

Additionally,

I'd like to see foreign keys into different schemas, if possible. If queries 
are possible over different schemas, this is surely possible as well.
Of course, not on all databases, but on PHP level, it should.

That way, one can define own schemas for additional data for 
third-party-schemata.

Common examples are CRMs created and maintained by third-party-software, 
whereupon a self-written software gives additional information / usage, maybe 
ebay "non paying bidder" disputes for customers. As some CRMs tend to rebuild 
their schemas on software updates, additional tables or columns just get 
lost, so information needs to be written to another schema.

Thomas Ritter

--

-- 
Do not meddle in the affairs of dragons, for you are crunchy and good with 
ketchup.
Permalink | Reply | 0 comments |
headers
Felix Gilcher | 8 Sep 2008 09:56

Re: Querying over multiple databases

Hi Fabian,

maybe the Federated Storage Engine [1] might be of some help. It's  
MySQL-specific and does have some limitations but it can proxy table  
access to remote databases. So the proxy would be handled mysql- 
internal and Propel would never get to know about it. I have never  
tried that though.

good luck

felix

[1] http://dev.mysql.com/doc/refman/5.1/en/federated-usagenotes.html

On Sep 8, 2008, at 9:04 AM, Fabian von Derschatta (united-domains AG)  
wrote:

> Hi,
>
> ok I see. Would be great if this feature finds its way into Propel  
> some
> day. Meanwhile we will try querying directly and hydrating the objects
> as you proposed.
>
> If I set up more than one datasource how can I access a specific
> datasource? Is it just getting the connection with $conn =
> Propel::getConnection('datasourcename')" and then continue with this
> connection object or do I have to initiate Propel with the specific
> datasource to use it?
>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Hans Lellelid | 8 Sep 2008 12:41

Re: Querying over multiple databases

Hi Fabian,

You're correct -- the connection is just determined from 
Propel::getConnection() call.  you can pass your connection method to 
the peer methods -- e.g. MyPeer::doSelect($crit, $con)  -- so that 
Propel will use that connection.  No need for further initialization.  
(Of course, this assumes that the connection you reference is defined in 
your runtime conf XML.)

Hans

Fabian von Derschatta (united-domains AG) wrote:
> Hi,
>
> ok I see. Would be great if this feature finds its way into Propel some
> day. Meanwhile we will try querying directly and hydrating the objects
> as you proposed.
>
> If I set up more than one datasource how can I access a specific
> datasource? Is it just getting the connection with $conn =
> Propel::getConnection('datasourcename')" and then continue with this
> connection object or do I have to initiate Propel with the specific
> datasource to use it?
>
> Regards,
> Fabian
>
> Am Sonntag, den 07.09.2008, 18:53 -0400 schrieb Hans Lellelid:
>   
>> Hi Fabian,
(Continue reading)

Permalink | Reply | 1 comment |
headers
Alan Pinstein | 16 Sep 2008 17:18
Picon
Gravatar

Criteria bug with AsColumns?

I posted previously about my "solution" for integrating GIS data with  
Propel. I have run into a problem with it, and am wondering what's  
going on.

I am now trying to set up a custom query for a left join, but the  
Criteria object is losing track of the ordering of the select columns.

I'd like to know if this should be considered a Propel bug, and I can  
maybe come up with a fix, or if I am trying something that shouldn't  
work.

Here's the query I'm setting up:

         $c = new Criteria();
         SyndicationTargetPeer::addSelectColumns($c);
         SyndicationEntryPeer::addSelectColumns($c);
         $c->addJoin(SyndicationTargetPeer::SYNDICATION_TARGET_ID,  
SyndicationEntryPeer::SYNDICATION_TARGET_ID, Criteria::LEFT_JOIN);
         $result = BasePeer::doSelect($c);

I have customized SyndicationTargetPeer::addSelectColumns like so:

     public static function addSelectColumns(Criteria $criteria)
     {
         parent::addSelectColumns($criteria);
         $criteria->addAsColumn("coverageAsText", "AsText(" .  
SyndicationTargetPeer::COVERAGE . ")");
     }

The problem is that the query that is issued to the database has the
(Continue reading)

Permalink | Reply | 502 comments |
headers
Hans Lellelid | 16 Sep 2008 19:22

Re: Criteria bug with AsColumns?

Hi Alan,

My suspicion is that this is a side effect of the fact that "as columns"
are stored in a separate structure (array) from select columns.  I'm not
sure off the top of my head why they couldn't be combined, but I believe
that's what's going on here.  They are added to the query after [all] the
select columns.

There may not be any further reason to enforce that they are kept separate.
 We have made some other changes in 1.3.0 to make them behave more
similarly (e.g. to treat addition of "AsColumns" as meaning the same thing
as adding regular select columns, as far as determining whether doselect*()
methods should add default columns.  Ultimately, we probably want to store
a single list of "SelectColumn" objects that could optionally have "AS"
information as part of them.  (I think that Propel2 needs to move in a
direction that uses heavier objects for columns; however, that is
admittedly outside the scope of this discussion.)

Hans

On Tue, 16 Sep 2008 11:18:29 -0400, Alan Pinstein <apinstein <at> mac.com>
wrote:
> I posted previously about my "solution" for integrating GIS data with
> Propel. I have run into a problem with it, and am wondering what's
> going on.
> 
> I am now trying to set up a custom query for a left join, but the
> Criteria object is losing track of the ordering of the select columns.
> 
> I'd like to know if this should be considered a Propel bug, and I can
(Continue reading)

Permalink | Reply | 509 comments |

Gmane