querying 2 tables on a single DM call

[Tiago Matos]

Hello, 

I have a performance related question. 

We have a call which runs multiple times per page load. After reading http://datamapper.org/docs/associations.html we currently have TranslationTarget.first(:translation_key => {:source => source}) but it first does a query to table TranslationKey then the query to TranslationTarget:

DEBUG -  (0.000468) SELECT `id` FROM `translation_keys` WHERE (`source` = 'Top Referrers' AND `domain` = 'admin')
  DEBUG -  (0.002409) SELECT `id`, `revision_status`, `revision_account_email`, `locale`, `updated_at`, `translation_key_id`, `account_id` FROM `translation_targets` WHERE (`translation_key_id` = 369 AND `locale` = 'pt' AND `revision_status` = 'approved') ORDER BY `id` LIMIT 1

(domain and revision_status are not very important fields, just filters)

Is there a way to use Datamapper here and avoid direct SQL?

Many thanks in advance.

Tiago Duarte Matos
http://tiagomatos.org

[Charley Kline]

I'm responding far after the original post, but...

I suspect it is a universal property of ORMs that they will not usually be able to construct an SQL query that is as efficient as what someone could write by hand.  I have seen similar cases to yours where DataMapper just didn't have enough information to connect the dots to build a single query that joined several tables, and instead made an initial query to pull records out of one table, and then generated a second query that included something like  "... WHERE other_table_id IN (3,6,10,17,24,31,35,38,40)."  It makes old RDBMS people cringe, to be sure, but using an ORM is not about generating optimal SQL.

We ignore these inefficiencies because of the way that an ORM allows us to manipulate related data objects in the natural way of the language we're writing in. Also, if the relevant objects have already been loaded into the application using DataMapper, it is often the case that the underlying relational database doesn't need to get called at all.

/cvk

[Abe Voelker]

The docs don't mention it, but you can force an INNER JOIN by using the string-style association syntax:

TranslationTarget.first('translation_key.source' => source)

http://stackoverflow.com/a/6716151/215168

--
You received this message because you are subscribed to the Google Groups "DataMapper" group.
To unsubscribe from this group and stop receiving emails from it, send an email to datamapper+...@googlegroups.com.
To post to this group, send email to datam...@googlegroups.com.

Visit this group at http://groups.google.com/group/datamapper.
For more options, visit https://groups.google.com/d/optout.