Thanks to Dan Kubb on IRC for helping me out with this:
> use :unique => true
> not DISTINCT, but does a GROUP BY all the SELECT fields
> so same basic outcome.. and in alot of DB's, the same query plan
So using my example, this now does the trick:
def self.accessible_by(user)
all(
:links => [Book.relationships[:readers]],
:conditions => ["(readers.user_id = ? OR books.private = ?)",
user.id, false],
:unique => true
)
end
--
Richard Livsey
Minutebase - Online Meeting Minutes
http://minutebase.com
http://livsey.org
On Mon, Sep 14, 2009 at 12:24 AM, Richard Livsey<[email protected]> wrote:
> Here's a gist with a clearer example of the problem I discuss below.
> http://gist.github.com/186348
>
> I'm currently using dm 0.9.11 with mysql.
>
> Say I have users and books, and those books can be public or private.
> I then have a 'readers' table which maps what books people can read.
>
> I then want to return a list of books for a user which are *either*
> public or has the user as an allowed reader, eg:
>
> class User
> end
>
> class Reader
> belongs_to :user
> belongs_to :book
> end
>
> class Book
> property :private, Boolean, :default => true
> has n, :readers
>
> def self.accessible_by(user)
> all(
> :links => [Book.relationships[:readers]],
> :conditions => ["(readers.user_id = ? OR books.private = ?)",
> user.id, false]
> )
> end
> end
>
>>> Book.accessible_by(current_user)
>
> This works fine for private books, but for ones which are public it
> will return duplicate records due to joining on the readers table
> (assuming the public book has a number of readers)
>
> In mysql I could use distinct, eg something like:
>
> SELECT DISTINCT books.*
> FROM books
> INNER JOIN readers ON readers.book_id = books.id
> WHERE readers.user_id = 10 OR books.private = FALSE
>
> But I can't see a way of doing that easily with datamapper.
>
> Obviously I could turn this example into a manual query, but that
> isn't possible (that I'm aware of) in the actual use-case because I'm
> using this as part of larger queries/chains for example:
>
>>> current_account.books.accessible_by(current_user).all(:name.like =>
>>> "%foo%", :order => [:created_at.asc])
>
> Is there a way of achieving this with datamapper, or does anyone have
> any recommendations of how I could change the architecture to make
> this easier?
>
> Thanks in advance for any help.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
