I've created a single file in a gist that makes it easier to try what you are trying to do:
http://gist.github.com/458124 When I do this, I actually do get 12 queries: 1 to get the attachments: SELECT "id", "filename" FROM "attachments" ORDER BY "id" 1 to get the attachment tags for the attachments: SELECT "id", "attachment_id", "tag_id" FROM "attachment_tags" WHERE "attachment_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ORDER BY "id" and 10, 1 to get the tags for each attachment tag: SELECT "id", "tagtext" FROM "tags" WHERE "id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ORDER BY "id" You can force the initial query to generate a query close to what you might want by doing this: Attachment.all("attachment_tags.tag.tagtext.like" => "%").each which generates: SELECT "attachments"."id", "attachments"."filename" FROM "attachments" INNER JOIN "attachment_tags" ON "attachments"."id" = "attachment_tags"."attachment_id" INNER JOIN "tags" ON "attachment_tags"."tag_id" = "tags"."id" WHERE "tags"."tagtext" LIKE '%' GROUP BY "attachments"."id", "attachments"."filename" ORDER BY "attachments"."id" but that's doesn't work because it doesn't return the tags and populate the object graph. Ultimately, I think what you want is a way to generate a query like this: SELECT "attachment_tags"."attachment_id", "attachment_tags"."tag_id", "attachments"."filename", "tags","tagtext" FROM "attachments" INNER JOIN "attachment_tags" ON "attachments"."id" = "attachment_tags"."attachment_id" INNER JOIN "tags" ON "attachment_tags"."tag_id" = "tags"."id" ORDER BY "attachments"."id" and have datamapper take care of populating each attachments array or attachment_tags, with each attachment_tags array of tags populated as well. I don't think that's possible in datamapper. But depending on the data, 12 queries might actually be faster than 1 big query with joins, since the database doesn't have to return repeated values for each attachment column. On Mon, Jun 28, 2010 at 5:03 PM, deco <[email protected]> wrote: > How can I stop so many queries from running. With this configuration > it generates a few clean queries at the start then a query with 2 > inner joins for each tag a line has (so 10 lines means at least 10 > queries as each line has at least one tag). > > It works, its just very inefficient. > > I'd appreciate any advice anybody can offer or any resource you could > point me to :) > > Thanks. > > VIEW > > %table > - @results.each do |line| > %tr > %td= line.filename > -# if I remove the line.tags part then it does NOT generate > excess queries, otherwise it generates a query for each tag in each > result > %td > - line.tags.each do |tag| > = tag.tagtext > > > CONTROLLER > > def fetch_results > @results = Attachment.all(:attachment_tags => {:tag => > Tag.all(:tagtext.like => '%myfile%')} ) > > end > > > > > MODELS > > class AttachmentTag > include DataMapper::Resource > > property :id, Serial > > belongs_to :attachment > belongs_to :tag > end > > > class Attachment > include DataMapper::Resource > after :save, :move_file > > property :id, Serial > property :filename, String > has n, :attachment_tags > has n, :tags, :through => :attachment_tags > ... > ... > end > > class Tag > include DataMapper::Resource > > property :id, Serial > > property :tagtext, String > has n, :attachment_tags > has n, :attachments, :through => :attachment_tags > > end > > -- > You received this message because you are subscribed to the Google Groups > "DataMapper" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<datamapper%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/datamapper?hl=en. > > -- You received this message because you are subscribed to the Google Groups "DataMapper" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
